This discussion is archived
3 Replies Latest reply: Feb 20, 2013 1:46 PM by 575197 RSS

ORA-21560

575197 Newbie
Currently Being Moderated
Hi,

Oracle DB Version: 11.2.0.2.0
OS: Solaris

I am trying to execute to code sample below and I am getting ORA-21560: argument 2 is null, invalid, or out of range.

I noticed that the error occurs when length(v_buffer) = 32767.

Has anyone seen this and found a way to workaround this issue when record length exceeds 32767?
declare
  
       v_file    clob;
       v_buffer  varchar2(32767);
       v_name    varchar2(128) := 'clob2file_buffered.txt';
       v_lines   pls_integer := 0;
       v_eol     varchar2(2);
       v_eollen  pls_integer;
       c_maxline constant pls_integer := 32767;
 
   begin
 
      v_eol := case
                  when dbms_utility.port_string like 'ibmpc%'
                  then chr(13)||chr(10)
                  else chr(10)
               end;
      v_eollen := length(v_eol);
 
      dbms_lob.createtemporary(v_file, true);
 
      for r in (select x || ',' || y || ',' || z as csv
                from   source_data)
      loop
 
         if length(v_buffer) + v_eollen + length(r.csv) <= c_maxline then
            v_buffer := v_buffer || v_eol || r.csv;
         else
            if v_buffer is not null then
               dbms_lob.writeappend(
                  v_file, length(v_buffer) + v_eollen, v_buffer || v_eol
                  );
            end if;
            v_buffer := r.csv;
         end if;
 
         v_lines := v_lines + 1;
 
      end loop;
 
      if length(v_buffer) > 0 then
         dbms_lob.writeappend(
            v_file, length(v_buffer) + v_eollen, v_buffer || v_eol
            );
      end if;
 
      dbms_xslprocessor.clob2file(v_file, 'dump_dir', v_name);
      dbms_lob.freetemporary(v_file);
 
      dbms_output.put_line('file='||v_name||'; lines='||v_lines);
 
   end;
   /
  • 1. Re: ORA-21560
    thomaso Journeyer
    Currently Being Moderated
    ...
    dbms_lob.writeappend(v_file, length(v_buffer) + v_eollen, v_buffer || v_eol );
    ...
    v_buffer || v_eol - concatenated string will exceed max length of pl/sql varchar2 variable (32767).

    HTH
    Thomas
  • 2. Re: ORA-21560
    575197 Newbie
    Currently Being Moderated
    Right Thomas.

    I changed
    if length(v_buffer) + v_eollen + length(r.csv) <= c_maxline then
    to
    if length(v_buffer) + v_eollen + length(r.csv) <= c_maxline - 1 then
    and it worked.

    Now doing some checks to see if this change messed up anything. Though I doubt it would have.
  • 3. Re: ORA-21560
    thomaso Journeyer
    Currently Being Moderated
    Just make variable v_buffer smaller:
       v_buffer  varchar2(16384);
           v_name    varchar2(128) := 'clob2file_buffered.txt';
           v_lines   pls_integer := 0;
           v_eol     varchar2(2);
           v_eollen  pls_integer;
           c_maxline constant pls_integer := 16384;
    HTH
    Thomas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points