I have a table in Oracle Database as follows,
create table test_clob(
id1 number,
clob_col clob);
If i try to insert a varchar2 variable with size more than 4000 into the CLOB column, it inserts without any problem.
insert into test_clob values (1,rpad('a',32760,'a'));
commit;
If i try to update the CLOB column as follows, it works perfectly fine.
update test_clob set clob_col = rpad('b',32760,'b') where id1 = 1;
commit;
However, if i try to run the update statement as follows, it is failing due to "ORA-01461: can bind a LONG value only for insert into a LONG column" error.
declare
large_string varchar2(32767) := rpad('c',32760,'c');
begin
update test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
commit;
end;
I suspect it is the NVL function that is causing the problem. Any help on this is highly appreciated.
Note: I have used a simple table in the example, but actually the table has several columns and update statement has to update many colums at a time.
actually, rpad('a',32760,'a') when called from SQL would only return a 4k string which is why it works.
A Varchar type in SQL is limited to 4k, so when you try to bind a 32k varchar2 variable from pl/sql it will fail (as rpad when called from pl/sql will return the 32k).
eg:
SQL> select length(rpad('a',32760,'a')) from dual;
LENGTH(RPAD('A',32760,'A'))
---------------------------
4000
it silently limits the return to 4k for you. but pl/sql will not limit to 4k:
SQL> declare
2 large_string varchar2(32767) := rpad('c',32760,'c');
3 begin
4 dbms_output.put_line(length(large_string));
5 end;
6 /
32760
You should define your pl/sql variable as clob and NOT varchar2(32760):
SQL> create table test_clob(
2 id1 number,
3 clob_col clob);
Table created.
SQL> insert into test_clob values (1,rpad('a',32760,'a'));
1 row created.
SQL> select length(clob_col) from test_clob;
LENGTH(CLOB_COL)
----------------
4000
SQL> commit;
Commit complete.
SQL> declare
2 large_string clob := rpad('c',32760,'c');
3 begin
4 update test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select length(clob_col) from test_clob;
LENGTH(CLOB_COL)
----------------
32760
SQL>