Returning clob to varchar from a stored procedure
February 20, 2004 Jesse -- Thanks for the question regarding "Returning clob to varchar from a stored procedure", version Oracle8.0.5/Sun OS
You Asked
and we said...
Rating: 5
Is there a 4k limit when returning a portion of a clob in a function? August 28, 2003
Reviewer: Anthony from ny
Rating: 4
excellent February 20, 2004
Reviewer: vj from in
February 20, 2004 Ramasubbu -- Thanks for the question regarding "Cuncurrent execution of procedures", version Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
You Asked
Hi Tom,
The issue is to return the text details stored in a table as
clob(after
loaded into the table from a text file). Is there a limitation
like
4000 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 error
Text 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 would like to call this stored procedure using thin
client JDBC calls, 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 advance
Regards,
Hwee
and we said...
There is a 32k 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 16k 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
languages).
Below shows that a plsql routine can in fact return 16k 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 2048
ops$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( l_clob, 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 16384
PL/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[])
{
Statement statement = null;
try
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection 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.getDriverVersion());
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 test_proc( 1, ? ); end;" );
cstmt.registerOutParameter( 1, java.sql.Types.CHAR );
System.out.println( "prepared & registered" );
cstmt.executeUpdate();
String newVal = cstmt.getString( 1 );
System.out.println
( newVal.length() + " bytes retrieved..." );
}
catch( Exception e )
{
e.printStackTrace();
}
}
}
and it returns:
$ java test
JDBC Driver Name : Oracle JDBC driver
JDBC Driver Version : 8.1.5.0.0
Database Product Name : Oracle
Database Product Version: Oracle8i Enterprise Edition
Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Max Statements : 0
prepared & registered
16384 bytes retrieved...
So, you should be able to use this method to retrieve UPTO 32k
of text. Beyond that you would have to use streams on the clobs.Review & FollowupRating: 5
Is there a 4k limit when returning a portion of a clob in a function? August 28, 2003
Reviewer: Anthony from ny
Tom,
I am trying to create a function to return a portion of a clob. The size is
greater than 4k 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 varchar2
is
begin
return dbms_lob.substr( p_ptr, dbms_lob.getlength(p_ptr), 1 );
end;
/
/* test it out */
SQL> col text form a50
SQL> select a,b,get_clob(c) text from t;
A B TEXT
---------- --------- --------------------------------------------------
1 28-AUG-03 **************************************************
**************************************************
**************************************************
**************************************************
...
so far so good. The return val is less than or equal to the SQL limit of 4k.
/* 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 */
SQL> select a,b,get_clob(c) from t where a=2;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-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 string buffer too small
ORA-06512: at "SCOTT.GET_CLOB", line 5
ORA-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;
32767
PL/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 4k 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 32k unless you return the clob/blob itself |
Rating: 4
excellent February 20, 2004
Reviewer: vj from in
Hi, The below is the version of oracle we are using Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production We 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 not working. 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 would interface to them as "streams" -- perhaps you are using "strings"? small concise yet complete is 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, 2004 Ramasubbu -- Thanks for the question regarding "Cuncurrent execution of procedures", version Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
浙公网安备 33010602011771号