Re: reading a CLOB with a PL/SQL Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 5 Oct 2001 16:30:00 -0700
Message-ID: <9plfpo0ssc@drn.newsguy.com>

In article <3BBD50BA.9090400_at_schaefer-shop.de>, Markus says...
>
>Hi folks,
>
>I insert a dataset into a table using the following procedure:
>
><CODE>
>CREATE OR REPLACE PROCEDURE WRITE_KATALOGTEXT(IN_DOKUID CHAR,
>IN_ARTNR
>NUMBER, IN_TEXT VARCHAR2, IN_DATUM DATE) IS
>LOB_LOC CLOB;
>BEGIN
>INSERT INTO TEXTCASTOR (DOKUID, ARTNR, TEXT, DATUM) VALUES
>(IN_DOKUID,
>IN_ARTNR, EMPTY_CLOB(), IN_DATUM);
>COMMIT;
>SELECT TEXT INTO LOB_LOC FROM TEXTCASTOR
>WHERE DOKUID=IN_DOKUID AND ARTNR=IN_ARTNR AND DATUM=IN_DATUM FOR
>UPDATE;
>DBMS_LOB.WRITE (LOB_LOC, length(IN_TEXT), 1, IN_TEXT);
>COMMIT;
>END;
>/
></CODE>
>
>After this insert I try to read from the table using the
>following Procedure
>
><CODE>
>CREATE OR REPLACE FUNCTION GET_KATALOGTEXT(IN_ARTNR NUMBER) RETURN
>VARCHAR2 IS
>BUFFER CLOB;
>MY_RETURNVALUE VARCHAR2(32767);
>MY_ARTNR NUMBER(6):=IN_ARTNR;
>BEGIN
>SELECT TEXT INTO BUFFER FROM WORKFLOWOWNER.TEXTCASTOR
>WHERE ARTNR=MY_ARTNR;
>MY_RETURNVALUE:=DBMS_LOB.SUBSTR(BUFFER, 32767, 1);
>RETURN MY_RETURNVALUE;
>END;
>/
></CODE>
>
>My problem is, that I get the Oracle Error ORA-06502.
>The Version of the DB is 8.1.5 on Solaris.
>It says, that the String buffer is too small if the length of the
>CLOB exceeds 4k.
>Writing works with values having a length of over fourthousand
>characters.
>Reading only works with values having a length of less than
>fourthousand characters.
>Has anyone a clue how to handle this?
>
>Greetings
>Markus
>
>--
>Markus Böhmer
>Datenbankentwickler
>SSI Schäfer Shop GmbH
>mailto:markus.boehmer_at_schaefer-shop.de
>

You don't say but you must be calling the GET function from sql. SQL has a limit of 4000 bytes/varchar2 (2000 in 7.x) regardless. The GET routine is totally redundant as dbms_lob.substr provides the exact functionality with lots less code.

You cannot fetch a STRING larger then 4000 characters using SQL. You can fetch a string upto 32k in plsql. You can fetch a string as large as you like in a 3gl.

Here is an example showing this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int, y clob ); Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace procedure write_lob( p_x in number, p_text in varchar2 )
  2 as
  3 l_clob clob;
  4 begin
5 insert into t (x,y) values ( p_x, empty_clob() ) returning y into l_clob;
  6 dbms_lob.writeAppend( l_clob, length(p_text), p_text );   7 end;
  8 /

Procedure created.

(use returning DO NOT commit, thats a really truly bad transactional process. you insert a row, commit it and THEN finish it -- what if after you commit and before you finished it -- the database crashed??? commit ONLY when the transaction is totally, completely, utterly finished). besides, this is lots less code = better for us all.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec write_lob( 1, rpad( '*', 32000, '*' ) ); PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> column y format a20 ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select x, dbms_lob.substr( y, 20, 1 ) y, dbms_lob.getlength(y) from t;

         X Y                    DBMS_LOB.GETLENGTH(Y)
---------- -------------------- ---------------------
         1 ********************                 32000

see - dbms_lob.substr provides all of the functionality of your routine, without the extra code. when we try to get more then 4000 bytes:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select x, dbms_lob.substr( y, 20000, 1 ) from t;
select x, dbms_lob.substr( y, 20000, 1 ) from t

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

we cannot SQL just won't do it for us.

PLSQL can get upto 32k:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare 2 l_data long;
3 begin

  4          for l_rec in ( select * from t )
  5          loop
  6                  l_data := dbms_lob.substr( l_rec.y, 32000, 1 );
7                  dbms_output.put_line( 'got ' || length(l_data) || ' bytes...'
);
  8          end loop;

9 end;
10 /
got 32000 bytes...

PL/SQL procedure successfully completed.

and java/vb/c/etc etc etc can get as much as you like from a lob locator.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri Oct 05 2001 - 18:30:00 CDT