How to insert big blob data in Oracle?

Question

I'm trying the following:

INSERT INTO (blob_column) VALUES (HEXTORAW('very big hex here'))

But I'm getting the following error:

ORA-01704: string literal too long

How can I workaround that? Is there any other syntax to insert a binary without converting from string?

Answer 1

If you are doing this from an application (VB.Net or C++ or php or whatever), then you must load the data in chunks, if the data is larger than 4000 bytes (thanks, mustaccio) OR if the data contains values that can't be sent as a string (Binary data).

The process is to take your data, split it into an array of byte "chunks" that are each somewhere less than 4000 bytes per chunk. The examples below provide ways to do this in a few languages.

Here is a PHP example from Oracle for using byte chunks.

Here is a C++ example from Stack Overflow.

Here is a C#.Net example from Code Project.

If you're doing this from scripts, check out SQL*Loader.

LOAD DATA 
INFILE example13.dat 
INTO TABLE EXAMPLE13 
FIELDS TERMINATED BY ',' 
( EMPNO INTEGER EXTERNAL, 
ENAME CHAR, 
JOB CHAR, 
MGR INTEGER EXTERNAL, 
SAL DECIMAL EXTERNAL, 
COMM DECIMAL EXTERNAL, 
DEPTNO INTEGER EXTERNAL, 
RES_FILE FILLER CHAR(60), 
"IMAGE" BFILE(CONSTANT "ORDIMGDIR", RES_FILE) 
)

If one of these examples isn't the right programming language or doesn't help you, let me know and I'll try to find you one for that language.

Answer 2

When you use string literals, Oracle performs an implicit conversion. So actually you just set the value of varchar which is converted to BLOB upon insertion. That results in unnecessary restrictions (like maximum length) and clumsy workarounds (like loading in chunks).

First thing you should understand is you have to use bind variables. That can't be stressed enough. With bind variables your query becomes something like this.

INSERT INTO (blob_column) VALUES (:blob)

And instead of passing a string containing the whole query you pass both the query and the set of bind variables to db driver (whichever one you choose).

It's the driver's job is to map Go type to Oracle type. I don't use Go, but this seems to be able to do the trick.