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
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;
Other approaches are good. But, if you have the long string stored somewhere in a file system, you could make use of these things.
SQL* loader : This is easy and straightforward and
can be automated to read from file system.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
UTL_FILE and inserts to the table.
UPDATE "<table>" 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 longDECLARE
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;
/
