How do I insert data that might be binary into a Varchar2 with OTL? (OCI/OCCI would be OK of course)
Background: We have a lot of Varchar2 columns which are generally not binary, but it might happen somewhere someday (I am especially concerned about \0 and UTF-8)
Tuesday: I posted this related question: How can I store bytes in Oracle Varchar2, and have ASCII treated as text
If you must use VARCHAR2, you'll need to convert the binary data first, for example using BASE-64 encoding.
So if you're calling an insert statement from C++, you first in C encode the bytes you wish to insert, then call the statement to insert the resulting string.
If you wish to insert binary values from another table, it gets trickier, but you can encode them in a PL/SQL function.
But if you can alter the data type, it is probably better to use the RAW datatype instead.