Thanks for the question, Jesse.

Asked: August 02, 2000 - 9:51 am UTC

Last updated: September 22, 2006 - 2:22 am UTC

Version: Oracle8.0.5/Sun OS

Viewed 10K+ times! This question is

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

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is there a 4k limit when returning a portion of a clob in a function?

Anthony, August 28, 2003 - 2:33 am UTC

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

August 28, 2003 - 7:48 am UTC

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

excellent

vj, February 20, 2004 - 12:09 am UTC

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

February 20, 2004 - 7:34 am UTC

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.

Clob issues with Java stored procedures

Don, June 21, 2004 - 6:38 am UTC

I'm getting similar issues trying to pass a CLOB to a java stored procedure, however the procedure doesn't throw an error, it just truncates my passed input data at 12,255 bytes !

I have a stored procedure that takes a Clob as input - the java method looks like the following:

public static void parseXML(java.sql.Clob inFile) {
Reader rd = inFile.getCharacterStream();
...
}

The stored procedure definition is fairly similar defining the input parameter as a Clob.

From my database-external code, I'm setting the clob using a character stream..

File ff = new File("filename.xml");
long len = ff.length(); // my test file is 77K
Reader rd = new FileReader("filename.xml");
...
CallableStatement cstmt = connection.prepareCall(
"{MyClass.parseXML(?)}");
cstmt.setCharacterStream(1, rd, (int)len);
cstmt.execute();
...

The external code executes and completes correctly, however the stored procedure just truncates the input data at 12K. In reading the data out of the reader inside the database I've tried using char[] buffers of various sizes but nothing seems to work.

NOTE: The above happens on Oracle EE v9.2.0.1 - I've just upgraded to 9.2.0.5 and it now throws an error in the external routine stating that the setCharacterStream() is an invalid data type, however this datatype is the only data type compliant as per the java.sql.* packages !!! Is this method now unsupported ?? We use this method extensively on standard PreparedStatements to INSERT Clobs and have had no problems with it..

NOTE NOTE: It's very important to us to stick to the java.sql.* interfaces as our application is using the same code across multiple DB platforms. The Oracle site seems to have no information on doing this using non-oracle proprietary APIs... very frustrating...

June 21, 2004 - 8:37 am UTC

give me an entire example (concise, yet 100% complete - get it down to just the offending code) and I'll be glad to take a look.

the small test case to reproduce the error message

A reader, November 23, 2004 - 2:17 pm UTC

create table t ( x clob );

declare
l_data long := rpad('*',32000,'*');
begin
insert into t values ( l_data );
end;

create table s( seq_num number,
x varchar2(500) );

create or replace procedure p_load_clob
as
v_clob clob

create or replace procedure p_load_clob
as
v_clob clob;
v_length number;
v_string varchar2(32000);
v_pos number; --position
v_loop_counter number;
v_note varchar2(500);
begin
v_pos := 1; --initial the position of substr

select
dbms_lob.getlength(x),
x
into
v_length,
v_clob
from t;

v_string := dbms_lob.substr(v_clob, 32000,1);

FOR i in 1..64 loop
select substr(v_string, v_pos,500)
into v_note
from dual;

insert into s values(i, v_note);
v_pos := v_pos + 500;
end loop;
end;

execute p_load_clob;
------------------------------------------

1. try to break the data in clob into 500 per record.
2. after I read your response, I realize the problem is not caused by dbms_lob.substr, but
-------------------
select substr(v_string, v_pos,500)
into v_note
from dual;
--------------------
where v_string is over 4000, right?

Thanks again. Your two books are the best.

November 23, 2004 - 2:58 pm UTC

well, that is totally different no?

select....
into l_clob
from....

mydoc := dbms_lob.substr( l_clob, 4001, 1 );
...

end;

is about as far away from that piece of code as you can get.

the error is here:

select substr(v_string, v_pos,500)
into v_note
from dual;

for two reasons

a) v_string is 32000 characters, in SQL 4000 is the MAX
b) why why why would you use "dual" to substr?

just code

v_note := substr( v_string, v_pos, 500 );

and be done with it. use dual when you have to, not as a matter of fact. this is plsql -- not transact sql, you don't have to select into variables! just call functions like you would in any real language.

DBMS_LOB.SUBSTR give different results on 9iR2 and 10gR1

Jack Wells, January 29, 2006 - 3:23 pm UTC

Tom,

I am using DBMS_LOB.SUBSTR on a CLOB variable and am getting different results on a 9iR2 database vs a 10gR1 database.  In the output, you see the differing results are:

lo_clob substr length..: 16383  (9i database)
lo_clob substr length..: 10922  (10g database)

Why are my resulting substr *lengths* different in the two tests?  Is it because of the way DBMS_LOB.SUBSTR is implemented in 9i vs 10g?  Or maybe that the NLS_NCHAR_CHARACTERSET parameters are different?  How can I structure the code to get consistent, cross platform results?

Thanks,
Jack
*************************************************************************
* 9iR2 Test
*************************************************************************
SQL*Plus: Release 10.1.0.3.0 - Production on Sun Jan 29 14:53:56 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production


GLOBAL_NAME     USER
--------------- ---------------
NCGD.WORLD      NGS

1 row selected.

SQL> COLUMN parameter FORMAT A30
SQL> COLUMN value FORMAT A30
SQL> SELECT * FROM v$nls_parameters
  2  /

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               UTF8
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_NCHAR_CHARACTERSET         UTF8
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.

SQL> CREATE OR REPLACE PROCEDURE clobtest
  2  IS
  3    lo_clob      CLOB;
  4    ln_bufsize   PLS_INTEGER := 16383;
  5  BEGIN
  6    --
  7    -- Create a CLOB in memory that is greater than 32K in size.
  8    --
  9    DBMS_LOB.createtemporary (lob_loc => lo_clob, CACHE => TRUE, dur => DBMS_LOB.CALL);
 10    FOR i IN 1 .. 10 LOOP
 11      DBMS_LOB.writeappend (lo_clob, 10000, RPAD ('*', 10000, '*'));
 12    END LOOP;
 13    --
 14    -- Now test dbms_lob.substr
 15    --
 16    DBMS_OUTPUT.put_line ('lo_clob length.........: ' || DBMS_LOB.getlength (lo_clob));
 17    DBMS_OUTPUT.put_line ('ln_bufsize.............: ' || ln_bufsize);
 18    DBMS_OUTPUT.put_line ('lo_clob substr length..: ' || LENGTH (DBMS_LOB.SUBSTR (lo_clob, ln_bufsize, 1)));
 19    DBMS_LOB.freetemporary (lo_clob);
 20  END clobtest;
 21  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE clobtest
lo_clob length.........: 100000
ln_bufsize.............: 16383
lo_clob substr length..: 16383

PL/SQL procedure successfully completed.



**************************************************************************
* 10gR1 Test
**************************************************************************
SQL*Plus: Release 10.1.0.3.0 - Production on Sun Jan 29 14:59:08 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options


GLOBAL_NAME     USER
--------------- ---------------
NIPS.WORLD      NGS

1 row selected.

SQL> COLUMN parameter FORMAT A30
SQL> COLUMN value FORMAT A30
SQL> SELECT * FROM v$nls_parameters
  2  /

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               UTF8
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.

SQL> CREATE OR REPLACE PROCEDURE clobtest
  2  IS
  3    lo_clob      CLOB;
  4    ln_bufsize   PLS_INTEGER := 16383;
  5  BEGIN
  6    --
  7    -- Create a CLOB in memory that is greater than 32K in size.
  8    --
  9    DBMS_LOB.createtemporary (lob_loc => lo_clob, CACHE => TRUE, dur => DBMS_LOB.CALL);
 10    FOR i IN 1 .. 10 LOOP
 11      DBMS_LOB.writeappend (lo_clob, 10000, RPAD ('*', 10000, '*'));
 12    END LOOP;
 13    --
 14    -- Now test dbms_lob.substr
 15    --
 16    DBMS_OUTPUT.put_line ('lo_clob length.........: ' || DBMS_LOB.getlength (lo_clob));
 17    DBMS_OUTPUT.put_line ('ln_bufsize.............: ' || ln_bufsize);
 18    DBMS_OUTPUT.put_line ('lo_clob substr length..: ' || LENGTH (DBMS_LOB.SUBSTR (lo_clob, ln_bufsize, 1)));
 19    DBMS_LOB.freetemporary (lo_clob);
 20  END clobtest;
 21  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE clobtest
lo_clob length.........: 100000
ln_bufsize.............: 16383
lo_clob substr length..: 10922

PL/SQL procedure successfully completed.

ORA-01460

Learner, September 21, 2006 - 2:01 pm UTC

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi
PL/SQL Release 10.1.0.5.0 - Production
CORE    10.1.0.5.0    Production
TNS for IBM/AIX RISC System/6000: Version 10.1.0.5.0 - Productio
NLSRTL Version 10.1.0.5.0 - Production

the error:

DBD::Oracle::st execute failed: ORA-01460: unimplemented or unreasonable conversion requested (DBD ERROR: OCIStmtExecute) [for Statement "

and the table:

SQL> desc nitf
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 NITF_KEY                                  NOT NULL NUMBER(38)
 NITF_AUTHOR                               NOT NULL VARCHAR2(50)
 NITF_MODIFIER                                      VARCHAR2(50)
 NITF_TITLE                                NOT NULL VARCHAR2(50)
 NITF_SUMMARY                              NOT NULL CLOB
 BODY_TEXT                                          CLOB

SQL> 

and the function:

CREATE OR REPLACE  FUNCTION IW_INSERT_NITF   (

in_key in NUMERIC,

in_nitf_author in VARCHAR2,

in_nitf_modifier in VARCHAR2,

in_nitf_title in VARCHAR2,

in_nitf_summary in CLOB,

in_body_text in CLOB

)

return numeric

as

    keyval numeric;

begin

    if in_key > 0 then

        update nitf set

            nitf_author = in_nitf_author,

            nitf_modifier = in_nitf_modifier,

            nitf_title = in_nitf_title,

            nitf_summary = in_nitf_summary,

            body_text = in_body_text

        where nitf_key = in_key;

        commit;

        return in_key;

    else

        begin

            keyval := iw_next_item_key();

            insert into nitf

            (

            nitf_key,

            nitf_author,

            nitf_modifier,

            nitf_title,

            nitf_summary,

            body_text

            )

            values

            (

            keyval,

            in_nitf_author,

            in_nitf_modifier,

            in_nitf_title,

            in_nitf_summary,

            in_body_text

            );

            commit;

            return keyval;

        end;

    end if;

end iw_insert_nitf;

can you please assist here what is the problem.

Thanks a lot

September 22, 2006 - 2:22 am UTC

nope, I do not ever work with dbd, I don't know what dbd might be doing here.

A reader, December 19, 2012 - 9:41 am UTC

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is there a 4k limit when returning a portion of a clob in a function?

excellent

Clob issues with Java stored procedures

more questions

the small test case to reproduce the error message

DBMS_LOB.SUBSTR give different results on 9iR2 and 10gR1

ORA-01460

More to Explore

VLDB

VLDB

Returning clob to varchar from a stored procedure

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is there a 4k limit when returning a portion of a clob in a function?

excellent

Clob issues with Java stored procedures

more questions

the small test case to reproduce the error message

DBMS_LOB.SUBSTR give different results on 9iR2 and 10gR1

ORA-01460

More to Explore

VLDB

VLDB