Insert into clob using plsql bind variable
create table tab1 ( id number primary key , big_text clob);
create or replace procedure insert_into_tab1(a number,b varchar2)
as
begin
insert into tab1 values(a,b);
commit;
end;
/
exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'))
SQL> exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'));
PL/SQL procedure successfully completed.
SQL> select count(*) from tab1;
COUNT(*)
----------
1
SQL> select big_text from tab1;
BIG_TEXT
--------------------------------------------------------------------------------
Start string from here .........................................................
SQL> select dbms_lob.getlength(big_text) from tab1;
DBMS_LOB.GETLENGTH(BIG_TEXT)
----------------------------
32000
SQL>
PLSQL limits my bind variable to 32000.
SQL, the limit is 4,000 characters.
Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters.
refence : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:388196216305
