Home » SQL & PL/SQL » SQL & PL/SQL » ORA-21560: while size is less than 1 GB for a CLOB
icon9.gif  ORA-21560: while size is less than 1 GB for a CLOB [message #377564] Tue, 23 December 2008 09:06 Go to next message
sourajit_seth
Messages: 38
Registered: November 2005
Location: India
Member
Hi ,
I use "DBMS_XSLPROCESSOR.CLOB2FILE' to write data from a CLOB variable (Oracle 10g) to TXT file in unix. I assumed that
the clob can collect data of around 4 gb , but it is throwing the "Out of Range" error if the size exceeds
22502381 bytes, which is not at all sufficient to my requirement. I want to collect around 3GB in the variable.
Is it possible ? Please help

/************************************************/
2008-12-22 11:08 Error Occurred
ORA-21560: argument 2 is null, invalid, or out of range
/**********************************************/

           
Declare

Xfile            CLOB ;
v_buffer            VARCHAR2(32767);
v_eol                 VARCHAR2(2);
v_eollen              PLS_INTEGER;
c_maxline             CONSTANT PLS_INTEGER := 32767;
v_lines   PLS_INTEGER := 0;

v_dir		VARCHAR2(1000) := 'XXX/YYY' ;
v_data_string       VARCHAR2(32767)  ;
x   integer ;




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(Xfile, TRUE);


FOR r in  ( Select desc1||'~'||desc2||.....desc30 AS csv from desc_t )

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(Xfile, v_dir, 'V_DESC,TXT');
     DBMS_LOB.FREETEMPORARY(v_file);
     
     
     Exception
       When Others Then
         dbms_output.put_line('Error '||v_lines||' --'||SQLERRM) ;
         dbms_output.put_line('Error '||dbms_lob.getlength(Xfile)) ;
End ;




[mod-edit: added code tags; next time please add them yourself]



[Updated on: Tue, 23 December 2008 15:03] by Moderator

Report message to a moderator

Re: ORA-21560: while size is less than 1 GB for a CLOB [message #377566 is a reply to message #377564] Tue, 23 December 2008 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.
And REMOVE WHEN OTHERS.

Regards
Michel
Re: ORA-21560: while size is less than 1 GB for a CLOB [message #377654 is a reply to message #377564] Wed, 24 December 2008 01:50 Go to previous messageGo to next message
sourajit_seth
Messages: 38
Registered: November 2005
Location: India
Member
Hi Michel,
Thanks for your response. The oracle version is 10.2.0.3.0. I have used the code in a procedure and got the same error while executing the procedure in SQL* Plus, Toad and Pl/SQL Developer.

Thanks
Re: ORA-21560: while size is less than 1 GB for a CLOB [message #377656 is a reply to message #377654] Wed, 24 December 2008 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The reasons we want you to remove the When Others and run it from SQL*Plus are these:
1) With no When Others block in place we see the whole error stack, including the line numbers where the problem happened.

2) The errors that you hasve posted did nor come from the code that you have posted. Your code would produce an error like:
Error 5 --ORA-21560: argument 2 is null, invalid, or out of range
Error 22502381

which is nothing like the errors you're posting.

So we'd like to see what actually happens when you run the code.
Re: ORA-21560: while size is less than 1 GB for a CLOB [message #377736 is a reply to message #377564] Wed, 24 December 2008 12:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You need to use an Oracle directory object, not a directory path. Also, you switched from xfile to v_file. Those variables need to match. Either replace all xfile with v_file or all v_file with xfile or replace them both with some more appropriate name like v_clob, since they represent a clob, not a file. I have provided a brief demo of the important parts below.

SCOTT@orcl_11g> -- create an Oracle directory object:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY my_dir AS
  2  'c:\oracle11g' -- substituted a path on my system for your xxx/yyy
  3  /

Directory created.

SCOTT@orcl_11g> DECLARE
  2    v_clob  CLOB; -- replace all xfile and v_file with v_clob
  3    v_dir   VARCHAR2 (1000) := 'MY_DIR'; -- directory object, not path, in upper case
  4  BEGIN
  5    DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
  6    v_clob := 'test';
  7    DBMS_XSLPROCESSOR.CLOB2FILE (v_clob, v_dir, 'your_file.txt');
  8    DBMS_LOB.FREETEMPORARY (v_clob);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 




icon2.gif  Re: ORA-21560: while size is less than 1 GB for a CLOB [message #385739 is a reply to message #377736] Tue, 10 February 2009 22:34 Go to previous message
Eddblack
Messages: 2
Registered: February 2009
Location: San Francisco
Junior Member
The argument is expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range. Examples include when the LOB/FILE positional or size argument has a value outside the range 1 through (4GB - 1), or when an invalid open mode is used to open a file, and so on.

Please review:

http://www.errorpro.com/oracleerrors/oraerror.php?level1=Oracle&send=Send&ecode=ORA-21560

wwww.errorpro.com
Previous Topic: Update Table with Primary Key Constraint
Next Topic: Getting the first row (merged 5)
Goto Forum:
  


Current Time: Sun Dec 04 02:45:38 CST 2016

Total time taken to generate the page: 0.06115 seconds