how to insert long string oracle clob or blob

Question

How I can insert very long text 100000 <length(string) < 300000 oracle clob or blob?

DECLARE
    v_long_text CLOB;
BEGIN
    v_long_text := 'my long string text';

    INSERT INTO MYTABLE_NAME 
    VALUES      (v_long_text);
END;

its metod is not worked, returned error PLS-00172: string literal too long

Answer 1

You literal is implicitly a varchar, so you are not able to assign to v_long_text value larger than maximum literal of varchar (maximum varchar length in plsql is 32767).

You can use concatenation:

DBMS_LOB.APPEND(v_long_text, 'very long string');
DBMS_LOB.APPEND(v_long_text, 'yet another long string');

Of course, I am assuming that MYTABLE_NAME column type is a CLOB

UPDATE: Sample code:

DECLARE
    v_long_text CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(v_long_text,true);
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));
   DBMS_LOB.APPEND(v_long_text, dbms_random.string('U', 20000));

    INSERT INTO my_table VALUES (v_long_text);
END;