PLSQL CLOB maximum length error

Question

My code/PLSQL function is as below:

function build_update(p_table_name varchar2) --function to build update statement
return varchar2
    as
     t_string CLOB;                 
begin
for i in (select column_name from ALL_TAB_COLS where table_name = p_table_name)
 loop
  t_string := t_string || i.column_name||'='||' b' ||'.'||i.column_name||',';   

 end loop;
   t_string := to_clob(to_char(substr(t_string, 1, instr(t_string, ',', -1)-1)));
                return t_string;

end;

While creating dynamic sql update to be used in merge, it is showing error as can bind a LONG value only for insert into a LONG column.

Even if an using CLOB, it cant take more characters. I have only 152 columns in my table, and for them I am making column_nameA=B.column_nameA.

Please help

Answer 1

Since result might be to big to handle in a varchar2 (size > 32512 on my system), you should type your return value as CLOB to avoid the error. But then you must handle CLOB in you code, which is fun.

declare   
    function build_update(p_table_name varchar2) --function to build update statement
    return clob as
       t_string CLOB;                 
    begin
        for i in (select column_name from ALL_TAB_COLS where table_name = p_table_name)
       loop
           t_string := t_string || i.column_name||'='||' b' ||'.'||i.column_name||',';   
       end loop;

       t_string := substr(t_string, 1, instr(t_string, ',', -1)-1);
       return t_string;

    exception
          when others then 
               dbms_output.put_line(sqlerrm);
               dbms_output.put_line(length(t_string));
               return 'kaput';
    end;

begin
 dbms_output.put_line(substr(build_update('MY_TABLE'), 1, 32512));

exception
      when others then 
           dbms_output.put_line('-in-main:'||sqlerrm);
end;
/