Oracle DB SQL insert base64 string (> 4000 chars) into BLOB column of a table in SQL

Question

I'm trying to insert a long base64 string (picture) in a BLOB column of a table and I get the error "string literal too long" cause The string literal is longer than 4000 characters and it's true but I don't know another way to do it, I'm using SQL developer.

My syntax is this one :

Insert into TABLE_NAME ( BLOB_COLUMN ) VALUES ('/9j/4AAQSkZJRgABAgA...2Q==');

I tried the functions CAST('Expression' AS BLOB), TO_BLOB but i get each time the same result.

How can I do it? I'm lost. Someone for help?

Thanks a lot

You have to split the string up into multiple smaller literals. But where is it coming from; and why BLOB rather than CLOB? — Alex Poole, May 10 '18 at 19:54
possible duplicate of https://stackoverflow.com/questions/3890567/how-to-write-oracle-insert-script-with-one-field-as-clob — Turo, May 10 '18 at 19:55
A BASE64 string is a **STRING** (it is the main purpose of base64 to convert binary data into ASCII strings), so appropriate data type would be `CLOB` rather than `BLOB` — Wernfried Domscheit, May 11 '18 at 05:21
Another example of general low quality answers when it comes to oracle and always the counter question "why would you do such thing?" The question clearly states that there is base64 encoded binary data that should end up in a blob column. So: no duplicate, no CLOB, no "Why?", is it so hard? — Marc Wittke, Nov 07 '22 at 17:49

score -1 · Answer 1 · answered Oct 14 '19 at 15:15

I have another suggestion because I tried all of these stored procedure methods that I found around Stackoverflow and just kept running into issues.

Use Oracle SQL Developer software. In that software you can navigate the table by double clicking it in the left pane:

Now the Table information will show in the main panel with different tabs. One of these tabs is called 'Data'.

Now you can click the 'Insert Row (Ctrl + I)' button. This will allow you to manually upload your picture and let Oracle SQL Developer do the insert for you.

score -2 · Answer 2 · answered May 10 '18 at 20:40

If the requirement is just to store a string, I would recommend CLOB instead of BLOB, as to store text in BLOB column, it has to be cast to RAW. The retrieval is equally complicated.

It can't be done in SQL, as 4000 characters is the maximum you can assign to a string field. However, here is my workaround using a small Pl/Sql Block (In Pl/Sql, maximum of 32767 characters.

CREATE TABLE clob_ins_test (
    clob_column CLOB
);
/

Table CLOB_INS_TEST created.

SET SERVEROUTPUT ON;
DECLARE
    v_long_text VARCHAR2( 5000 );
BEGIN
    v_long_text := LPAD( '1', 3500, '1' ) || LPAD( '1', 600, '1' ); /* Assign your string value here as v_long_text := '/9j/4AAQSkZJRgABAgA...2Q=='*/
    dbms_output.put_line( 'Length of String is ' || length( v_long_text ) ); -- Printing the length of the String assigned to v_long_text.
    INSERT INTO clob_ins_test VALUES ( v_long_text );
END;
/

Length of String is 4100

PL/SQL procedure successfully completed.

SELECT dbms_lob.getlength( clob_column ) Length, clob_column FROM CLOB_INS_TEST;

    LENGTH CLOB_COLUMN                                                                     
---------- --------------------------------------------------------------------------------
    4100 11111111111111111111111111111111111111111111111111111111111111111111111111111111

Hope this helps!

Oracle DB SQL insert base64 string (> 4000 chars) into BLOB column of a table in SQL

2 Answers2