Returning clob to varchar from a stored procedure

February 20,200 4 Jesse -- thanks for the question regarding "returning clob to varchar from a stored procedure", version oracle8.0.5/Sun OS

You asked

Hi Tom, the issue is to return the text details stored in a table as clob (afterloaded into the table from a text file ). is there a limitation like4000 for the size of an out parameter to passed into a stored procedure? I have created the following stored procedure create or replace procedure test_proc (myid number, mydoc out varchar2) is c_lob clob; myclob_text varchar2 (4000); Len number: = 0; amount Number: = 100; offset number; begin select theclob into c_lob from demo where id = myid; Len: = dbms_lob.getlength (c_lob); offset: = 1; while (LEN> 0) loop myclob_text: = dbms_lob.substr (c_lob, amount, offset); -- dbms_lob. Read (c_lob, amt2, offset, myclob_text); -- dbms_output.put_line (myclob_text); Len: = len-amount; offset: = offset + amount; mydoc: = mydoc | myclob_text; end loop; end;/testing using: SQL> variable TXT varchar2 (4000); SQL> exec test_proc (1,: txt); error: ORA-06502 PL/SQL: numeric or value errortext in ID 1 is loaded from a file size of 16k. if I passed in the id = 2 which contains 2 lines of text, it Is OK. If I wowould like to call this stored procedure using thin client JDBC CILS, will it works for big clob? What is the solution if I were to return the clob-> varchar2 from a stored procedure? Thank you in advanceregards, Hwee

And we said...

There is a 32 K limit on the size of an out parameter (or in parameter or in fact all PLSQL variables). The reason you are getting SQL> variable TXT varchar2 (4000); SQL> exec test_proc (1,: txt); error: ORA-06502 PL/SQL: numeric or value error Is due to the fact that you are trying to stuff 16 K of data into 4000 bytes of allocated space. sqlplus will not let you define a variable of greater them 4000 bytes (but you can in other ages ). below shows that a PLSQL routine can in fact return 16 K of data: ops $ tkyte@DEV8I.WORLD> Create Table T (x int, y clob); table created. ops $ tkyte@DEV8I.WORLD> ops $ tkyte@DEV8I.WORLD> declare 2 TMP long; 3 C clob; 4 Begin 5 TMP: = rpad ('*', 16384, '*'); 6 insert into T values (1, empty_clob () Returning y into C; 7 dbms_lob.writeappend (C, length (TMP), TMP); 8 9 TMP: = rpad ('*', 2048, '*'); 10 insert into T values (2, empty_clob () Returning y into C; 11 dbms_lob.writeappend (C, length (TMP), TMP); 12 end; 13/PL/SQL procedure successfully completed. ops $ tkyte@DEV8I.WORLD> select X, dbms_lob. Getlength (y) from T 2/X dbms_lob.getlength (y) ---------- --------------------- 1 16384 2 2048ops $ tkyte@DEV8I.WORLD> Create or replace procedure test_proc (myid number, mydoc out varchar2) 2 As 3 l_clob clob; 4 Begin 5 select y into l_clob 6 from T 7 where x = myid; 8 9 mydoc: = dbms_lob.substr (rochelb, 32765, 1); 10 end; 11/procedure created. ops $ tkyte@DEV8I.WORLD> declare 2 test_bind_variable Long; 3 begin 4 test_proc (1, test_bind_variable); 5 dbms_output.put_line ('returned length is '| length (test_bind_variable); 6 end; 7/returned length is 16384pl/SQL procedure successfully completed. ops $ tkyte@DEV8I.WORLD> in Java, using the 8.1.5 thin driver, I coded the following: Import Java. io. *; import Java. SQL. *; import Java. util. *; Class test {public static void main (string ARGs []) {stateme NT Statement = NULL; try {drivermanager. registerdriver (New Oracle. JDBC. driver. oracledriver (); connection = drivermanager. getconnection ("JDBC: oracle: thin: @ aria-Dev: 1521: ora8idev", "Scott", "Tiger"); databasemetadata conmd = connection. getmetadata (); system. out. println ("JDBC driver name: \ t" + conmd. getdrivername (); system. out. println ("JDBC driver version: \ t" + conmd. getdriverversio N (); system. out. println ("database product name: \ t" + conmd. getdatabaseproductname (); system. out. println ("database product version: \ t" + conmd. getdatabaseproductversion (); system. out. println ("Max statements: \ t" + conmd. getmaxstatements (); system. out. println (); connection. setautocommit (false); Statement = connection. createstatement (); callablestatement cstmt = connection. preparecall ("begin te St_proc (1 ,? ); End; "); cstmt. registeroutparameter (1, Java. SQL. types. char); system. out. println ("prepared & Registered"); cstmt.exe cuteupdate (); string newval = cstmt. getstring (1); system. out. println (newval. length () + "bytes retrieved... ");} catch (exception e) {e. printstacktrace () ;}}and it returns: $ Java testjdbc driver name: Oracle JDBC driverjdbc driver version: 8.1.5.0.0database Product Name: oracledatabase product version: oracle8i Enterprise Edition Release 8.1.5.0.0-productionwith the partitioning and Java optionspl/SQL release 8.1.5.0.0-productionmax statements: 0 prepared & registered16384 bytes retrieved... so, you should be able to use this method to retrieve upto 32 K of text. beyond that you wowould have to use streams on the clobs.

Review & followup

Rating: 5
Is there a 4 K limit when returning a portion of a clob in a function? August 28,200 3
Reviewer: Anthony From ny

Tom, I am trying to create a function to return a portion of a clob. the size is greater than 4 k but less than 10k. i'm having some trouble understanding what's going on here: Create Table T (A int, B Date, C clob); insert into T values (1, sysdate, rpad ('*', 400, '*'); Commit; create or replace function get_clob (p_ptr clob) return varchar2isbegin return dbms_lob.substr (p_ptr, dbms_lob.getlength (p_pt R), 1); end; // * test it out */SQL> Col text form a50sql> select a, B, get_clob (c) Text from T; a B text ---------- --------- hour 1 28-aug-03 ******************************** **************************************** **************************************** **************************************** **************************************** ********... so far So good. the return Val is less than or equal to the SQL limit of 4 K. /* putting in a bigger clob */declare l_ptr clob; begin insert into T values (2, sysdate, empty_clob () Returning C into l_ptr; dbms_lob.write (l_ptr, 32767, 1, rpad ('*, 32767,' * '); Commit; end;/SQL> select dbms_lob.getlength (c) from t; dbms_lob.getlength (c) ------------------- 4000 32767/* the following error is expected */SQ L> select a, B, get_clob (c) from t where a = 2; error: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at "Scott. get_clob ", line 5/* But, this doesn't work .... */SQL> declare X varchar2 (32767); begin select get_clob (c) into X from t where a = 2; dbms_output.put_line (length (x); end; /declare * error at line 1: ORA-06502: PL/SQL: numeric or value error: character strin SmallORA-06512 for G buffer too: At "Scott. get_clob ", line 5ora-06512: At Line 4 /*... while this does */SQL> declare X varchar2 (32767); y clob; begin select C into y from t where a = 2; X: = get_clob (y ); dbms_output.put_line (x); end; 32767pl/SQL procedure successfully completed. why does the last anonymous block succeed? Is this first block doing it in SQL (even though it's in a PL/SQL block), therefore restricted to 4 K as well? Thanks in advance,-ant

Followup:

SQL is limited to 4000 bytes unless you just return the clob/BLOB itself. PLSQL is limited to 32 K unless you return the clob/BLOB itself

Rating: 4
Excellent February 20,200 4
Reviewer: VJ From in

Hi, the below is the version of Oracle we are usingoracle9i Enterprise Edition Release 9.2.0.1.0-productionpl/SQL release 9.2.0.1.0-productioncore 9.2.0.1.0 productiontns for 32-bit windows: version 9.2.0.1.0-productionnlsrtl version 9.2.0.1.0-productionwe have an SP that return (Out PARAM) The clob datatype to the calling program. the calling program is an JSP and we use ORACLE thin driver. my size of clob is around 32kb and sometimes little more than 32kb. if we send below 30kb it is working, but if it is more than 30 kb it is notworking. any other alternate as we built the programs around the same logic. RGDS
 

Followup:

You 'd really have to provide a teeny tiny code snippet for you can use clobs of unlimited size in Java/jdbc (well, upto the current 4gig limit in 9i ). you wocould interface to them as "streams" -- perhaps you are using "strings "? Smallconciseyet completeis the best test case. It won't need a JSP to reproduce your issue -- a tiny "Main" with a tiny bit of code.

February 20,200 4Ramasubbu -- thanks for the question regarding "cuncurrent execution of Procedures", version Oracle8i Enterprise Edition Release 8.1.6.3.0-Production