How to insert big blob data in Oracle

bloboracle

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?

Best Answer

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.

Related Question