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.
more questions
jen, November 22, 2004 - 4:11 pm UTC
declare
mydoc varchar2(10000)
l_clob clob;
begin
select....
into l_clob
from....
mydoc := dbms_lob.substr( l_clob, 4001, 1 );
...
end;
Error executing statement: ORA-01460: unimplemented or unreasonable conversion
I won't get error if I put <=4000 inside the dbms_lob.substr
Is there a parameter DBA can set up to increase the size limitation in pl/sql for this package?
Thanks.
November 22, 2004 - 5:30 pm UTC
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x clob );
Table created.
ops$tkyte@ORA9IR2> declare
2 l_data long := rpad('*',32000,'*');
3 begin
4 insert into t values ( l_data );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select dbms_lob.getlength(x) from t;
DBMS_LOB.GETLENGTH(X)
---------------------
32000
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 mydoc varchar2(10000);
3 l_clob clob;
4 begin
5 select x into l_clob from t;
6 mydoc := dbms_lob.substr( l_clob, 4001, 1 );
7 dbms_output.put_line( 'length = ' || length(mydoc) );
8 end;
9 /
length = 4001
PL/SQL procedure successfully completed.
<b>help me reproduce.... full test case please</b>
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