3 Replies Latest reply: Feb 20, 2013 3:46 PM by 575197 RSS

    ORA-21560

    575197
      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
          ...
          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
            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
              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