Unable to insert oracle BLOB data greater than 3200 using Insert Statement

Question

I am working on existing project and need to prepare DML for oracle database. But i am unable to prepare for Insert statement for Blob data due to huge size which is grater than 4000 bytes. Can any one help me?

N.B: I got this error, ORA-06550: String literal too lang.

Only INSERT Statement, no java programe. I just need it to prepare DML like insert statement.

My INSERT STATEMENT:

INSERT INTO APP_PROF('ID', 'IMAGE') VALUES('2', TO_BLOB('4654655665....'))

This Image blob data is greater than 45000bytes

Thanks in advance.

You can't do that without some program code. Oracle doesn't allow literals to be longer then 4000 bytes (or 32k since Oracle 12) — , Mar 23 '16 at 07:09
@a_horse_with_no_name, Tom [says](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1202548400346932817) "you cannot have a string literal over 4000 bytes in SQL, or 32k in PLSQL". I can confirm that Oracle 12 still has 4k limit for SQL. — Vadzim, Jun 02 '20 at 15:28
@Vadzim: you can enabled "extended strings" in Oracle 12 to allow for 32k string literals — , Jun 02 '20 at 15:30
Thanks, some details on `ALTER SYSTEM SET max_string_size='extended'`: https://stackoverflow.com/questions/30878036/oracle-12c-extended-to-support-varchar2-4000-bytes-doesnt-work-for-user-who-i — Vadzim, Jun 02 '20 at 15:40
See also alternative long binary literals workarounds in https://stackoverflow.com/questions/18116634/oracle-10-using-hextoraw-to-fill-in-blob-data. — Vadzim, Jun 02 '20 at 22:06

score 1 · Accepted Answer · answered Mar 23 '16 at 06:59

1

Yes. Use stream, not String/byte array to insert BLOB. Something like this:

PreparedStatement ps = conn.prepareStatement("insert into blobs (blob_value) values (?)");
InputStream in = new StringBufferInputStream(aLagrgeStringValue);
ps.setBinaryStream(1,in,(int)in.length());
ps.execute();

answered Mar 23 '16 at 06:59

Vasyl Moskalov

4,242
3
20
28

I am using SQL developer, I only need in SQL insert statement no java language is allowed. – Md. Naushad Alam Mar 23 '16 at 07:00
Look at DBMS_LOB package. https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm – Vasyl Moskalov Mar 23 '16 at 07:01

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

The problem I find here is that you are using string value to insert in a Blob column and error that you are reciving is the limitation error for the input string and not the blob column of the table.

please refer to the below link for further clarification.

How to convert VARCHAR2 to BLOB inside Oracle 11g PL/SQL after ORA-06502

edited May 23 '17 at 12:23

Community

1
1

answered Mar 23 '16 at 07:14

Hari

1
3

I have a hex string inside TO_BLOB function, so it doesn't create error. Its working i have inserted many row less than 4000bytes. – Md. Naushad Alam Mar 23 '16 at 07:19
@Md. Naushad Alam The same will not not work for string length greater than it max size ie 4000 bytes and that is the error that you are receving. Just to check the issue, try inserting a blob from any other table that is greater than 4000 bytes – Hari Mar 23 '16 at 07:43
@Hari, both RAW and VARCHAR types have 32k limit in PL/SQL. So there is no way for larger LOB literals. – Vadzim Jun 02 '20 at 15:31

Unable to insert oracle BLOB data greater than 3200 using Insert Statement

2 Answers2