1

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

Community
  • 1
  • 1
Olav
  • 1,758
  • 4
  • 27
  • 49

1 Answers1

1

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.

Community
  • 1
  • 1
flup
  • 26,937
  • 7
  • 52
  • 74
  • How would this look with BASE-64: + res "INSERT INTO HEADER_TBNAME VALUES(:BF_FILE_CREATION_DATE? – Olav Jan 20 '13 at 16:10
  • Here's someone who does it: http://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle – flup Jan 20 '13 at 16:16
  • Yes, but that is in a stored procedure? Or at least PL/SQL in the C++? – Olav Jan 20 '13 at 16:38
  • If the binary object you wish to insert sits on the C++ side, you can encode it using a C library: http://stackoverflow.com/questions/342409/how-do-i-base64-encode-decode-in-c – flup Jan 20 '13 at 16:45
  • Am I right that this involves having the data encoded as BASE-64 IN THE DATABASE? That is not what I am looking for (Unless it is not technically possible to have some binary data in a varchar2) – Olav Jan 21 '13 at 22:36
  • No, binary data cannot be written into a VARCHAR2 column. – flup Jan 21 '13 at 22:47