4

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

Meqenaneri Vacharq
  • 99
  • 1
  • 1
  • 12

3 Answers3

8

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;
mkuligowski
  • 1,544
  • 1
  • 17
  • 27
0

Other approaches are good. But, if you have the long string stored somewhere in a file system, you could make use of these things.

  • Load it using SQL* loader : This is easy and straightforward and can be automated to read from file system.
  • Use unix split utility with -b option to split the long text in the file into smaller chunks ( say 32000 or 4000 ) and in a shell script construct multiple insert statements.

something like :

split -b 4000 yourbigfile split_files_
ct=1
for chunk in $(cat split_files_??)
 do
   # first time
   echo "INSERT INTO table_name(col_name) VALUES ('${chunk}');"
   # 2nd and subsequent
  echo " update table_name set col_name = col_name || '$chunk';"
  ct=$((ct + 1))
done >yourinsert_script.sql
  • Create a PL/SQL procedure that reads the text from a file using UTL_FILE and inserts to the table.
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Have you checked the solution? In a similar case I've got an ORA-01489: result of string concatenation is too long. – Kunis Jan 21 '19 at 08:55
  • @kunis : I'm not sure how you're doing it. As far as I remember it did work for me. It's possible that somewhere in your code you are concatenating literals who's total size exceeds 4000 chars – Kaushik Nayak Jan 21 '19 at 09:01
  • @nayak `UPDATE "" SET BK_DATASET_SQL = 'SELECT SK_ACCNT_PUR_HIST_ID||''' || chr(38) || '3''|| ..... ' WHERE ...` If the text that I'm going to put into BK_DATASET_SQL is too long (more then 4000 symbols), I'm getting ORA-01489: result of string concatenation is too long
     – Kunis Jan 22 '19 at 14:51
0
DECLARE
    definition_clob CLOB;

BEGIN
dbms_lob.createtemporary(definition_clob, true);
dbms_lob.append(definition_clob,'String 1.. ">
dbms_lob.append(definition_clob,'String 2.. ">
.
.
.
INSERT INTO TABLE
(CLOB_COLUMN)
VALUES
(definition_clob)

COMMIT
dbms_lob.freetemporary(definition_clob);

END;
/
wyse12
  • 7
  • 4