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
– Alex Poole, Commented May 10, 2018 at 19:54
possible duplicate of stackoverflow.com/questions/3890567/… — Turo
– Turo, Commented May 10, 2018 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
– Wernfried Domscheit, Commented May 11, 2018 at 5: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
– Marc Wittke, Commented Nov 7, 2022 at 17:49

Ajith Kumar Y · Accepted Answer · 2018-05-10 20:40:09Z

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!

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related