Hi Tom,

Thank you for your answer and your help! Great site!

We use your way to send out emails (dbms_job and utl_smtp); so I would like to know the max length/size of email body that Oracle can handle (our application will not fail). In your book it is said the limitation is 32k. However from my test the body length can be 60000. Are size in byte and length the same?

Thanks again!

June

Hi tom,
can you provide me a link which shows how to use clob for more than 32k.
Thanks,

tom, for a test case I want to get some GIVEN XML content inserted into a table column of type CLOB through an anonymous block

declare
myclob clob;

begin
myclob := to_clob('<my_real_large_xml_content');

dbms_output.put_line(length(myclob)); -- returns 25071!
dbms_output.put_line(substr(clo, length(clo) -30)); -- does NOT return the LAST tag of the given XML content
end;

can you pls help?

no idea what your goal is.  but if you just want to insert, insert it then.  

works for me though

ops$tkyte@ORA920LAP> declare
  2     myclob clob;
  3  begin
  4          myclob := 'blah blah blah blah blah blah
  5                     blah blah blah blah blah blah
  6                     blah blah blah blah blah blah
  7                     blah blah blah blah blah blah
  8                     blah blah blah blah blah blah
  9                     blah blah blah blah blah blah
 10                     blah blah blah blah blah blah
 11                     blah blah blah blah blah blah
 12                     blah blah blah blah blah blah
 13                     blah blah blah blah blah blah
 14                     blah blah blah blah blah blah
 15                     blah blah blah blah blah blah
 16                     blah blah blah blah blah blah
 17                             <some tag>';
 18          dbms_output.put_line( length(myclob) );
 19          dbms_output.put_line( substr( myclob, length(myclob)-30) );
 20  end;
 21  /
550
blah blah blah
                           <some tag>

PL/SQL procedure successfully completed.

 

no idea why some of my "blahs" get lost?

it makes ME very unhappy ;o) ... because I see '<some tag>' AND the number of bytes is what I expected it to be.

Hi Tom,
I think I read somewhere (don't recall where) that when writing to a CLOB, its advantageous to do a explicit DBMS_LOB.OPEN and then perform your writes and then explicitly do a DBMS_LOB.CLOSE. It will give better performance compared to not doing an explicit OPEN and CLOSE (it is good to do this if you write to a CLOB in a loop which runs multiple iterations).
Do you agree? will it really make a difference in performance?
Thanks,

Hi Tom,
Here is the section from the Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2):

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lob2.htm#1009075 <code>

<quote>

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.


When calling DBMS_LOB.WRITE from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.


It is not mandatory that you wrap the LOB operation inside the Open/Close APIs. If you did not open the LOB before performing the operation, the functional and domain indexes on the LOB column are updated during the call. However, if you opened the LOB before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB is closed, it updates the functional and domain indexes on the LOB column.


If you do not wrap the LOB operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN or CLOSE statement.

</quote>

Here the documentation says that not performing a LOB operation wrapped within a OPEN/CLOSE call will affect the performance and it recommends doing OPEN before the lob operation and then CLOSE at the end of the LOB operation.

ahh, if you call dbms_lob.write* operations many times in succession -- yes.    Here is my benchmark -- i use runstats (asktom.oracle.com/~tkyte) -- it measures more things.  There is a definite and obvious difference on my test once you start doing THOUSANDS of write calls (abnormal, most people do a handful - usually one -- rarely thousands)

Here is my test case:

ops$tkyte@ORA920> create table t ( x clob );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure p( p_open_close in boolean default false,
  2                                 p_iters in number default 100 )
  3  as
  4      l_clob clob;
  5  begin
  6      insert into t (x) values ( empty_clob() )
  7      returning x into l_clob;
  8
  9      if ( p_open_close )
 10      then
 11          dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
 12      end if;
 13
 14      for i in 1 .. p_iters
 15      loop
 16          dbms_lob.WriteAppend( l_clob, 5, 'abcde' );
 17      end loop;
 18
 19      if ( p_open_close )
 20      then
 21          dbms_lob.close( l_clob );
 22      end if;
 23      commit;
 24  end;
 25  /

Procedure created.

We can control whether the lob is opened/closed and how many writeAppends we do to it.  The results for 100 writeAppends was marginal:

ops$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(FALSE,100);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(TRUE,100);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_stop(10000);
Run1 ran in 11 hsecs
Run2 ran in 9 hsecs
run 1 ran in 122.22% of the time

Name                                  Run1        Run2        Diff

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
9,323       9,024        -299    103.31%

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>

<b>
They more or less -- for all intents and purposes -- did the same amount of work in about the same amount of time.

Moving onto 1,000 iterations however</b>

ops$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(FALSE,1000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(TRUE,1000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_stop(10000);
Run1 ran in 111 hsecs
Run2 ran in 102 hsecs
run 1 ran in 108.82% of the time

Name                                  Run1        Run2        Diff
STAT...session logical reads        38,219      12,897     -25,322
STAT...consistent changes           31,182       4,376     -26,806
STAT...data blocks consistent       30,673       3,859     -26,814
STAT...consistent gets - exami      32,022       5,206     -26,816
STAT...consistent gets              32,870       5,942     -26,928
LATCH.cache buffers chains         115,595      41,229     -74,366
STAT...session pga memory          107,996       7,812    -100,184
STAT...redo size                 5,443,144   5,599,576     156,432

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
171,337      92,963     -78,374    184.31%

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>

<b>we start to see something interesting -- first, the one without open/close is taking longer by the wall clock BUT more interesting is the latching that is going on.  We almost doubled the latching by not opening/closing here.  That inhibits scalability massively -- in a multi-user test, the one without open/close would not scale very well at all.


Moving upto 2,000 writeappends</b>


ops$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(FALSE,2000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(TRUE,2000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_stop(10000);
Run1 ran in 1258 hsecs
Run2 ran in 706 hsecs
run 1 ran in 178.19% of the time

Name                                  Run1        Run2        Diff
STAT...db block gets                19,044      29,575      10,531
LATCH.undo global data              18,837       4,022     -14,815
LATCH.simulator hash latch          36,148       2,215     -33,933
STAT...redo size                15,471,664  15,910,000     438,336
STAT...consistent changes        1,741,844      11,075  -1,730,769
STAT...data blocks consistent    1,741,335      10,557  -1,730,778
STAT...session logical reads     1,783,277      46,937  -1,736,340
STAT...consistent gets - exami   1,753,600      13,920  -1,739,680
STAT...consistent gets           1,765,441      18,570  -1,746,871
LATCH.cache buffers chains       5,349,496     147,523  -5,201,973

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
5,541,018     277,802  -5,263,216  1,994.59%

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>


<b>now the wall clock difference is very measurable -- but look at those latches!  that is *huge*.

At 5,000</b>

ops$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(FALSE,5000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec p(TRUE,5000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_stop(10000);
Run1 ran in 8925 hsecs
Run2 ran in 1956 hsecs
run 1 ran in 456.29% of the time

Name                                  Run1        Run2        Diff
STAT...no work - consistent re       2,478      18,398      15,920
STAT...db block gets                64,682      96,602      31,920
STAT...db block changes            136,403      84,374     -52,029
STAT...redo entries                114,133      44,234     -69,899
LATCH.redo allocation              114,690      44,735     -69,955
LATCH.cache buffers lru chain       86,428       5,496     -80,932
STAT...free buffer requested        85,405       4,074     -81,331
STAT...calls to kcmgas              86,058       4,712     -81,346
STAT...cleanout - number of kt      84,750         378     -84,372
STAT...active txn count during      84,743         351     -84,392
STAT...immediate (CR) block cl      84,729         312     -84,417
STAT...cleanouts and rollbacks      84,722         288     -84,434
STAT...CR blocks created            84,750         288     -84,462
LATCH.undo global data             173,491      13,620    -159,871
LATCH.simulator hash latch         697,188       4,117    -693,071
STAT...redo size                48,304,392  46,655,964  -1,648,428
STAT...consistent changes       17,086,252      37,143 -17,049,109
STAT...data blocks consistent   17,085,741      36,626 -17,049,115
STAT...consistent gets - exami  17,179,776      46,216 -17,133,560
STAT...session logical reads    17,337,689     169,999 -17,167,690
STAT...consistent gets          17,277,215      77,605 -17,199,610
LATCH.cache buffers chains      52,010,113     508,652 -51,501,461

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
53,376,618     865,837 -52,510,781  6,164.74%

PL/SQL procedure successfully completed.


<b>now it is getting serious.
</b>


 

Hi Tom,
These tests indicate no difference. Does this mean that what documentation says is not totally correct?
OR
I'm missing something during the tests?
What's your opinion?


Using DBMS_LOB.WRITEAPPEND ...

SQL> create table lob_effect(id int primary key, loc clob)
  2  tablespace users
  3  lob(loc) store as lob_lob_effect_segment (tablespace users)
  4  /

Table created.

Elapsed: 00:00:00.00
SQL> set serveroutput on
SQL> set timing on
SQL> DECLARE
  2      l_cnt        PLS_INTEGER;
  3      l_start_time PLS_INTEGER;
  4      lob_loc      CLOB;
  5      data         VARCHAR2(32767) := RPad('*',
  6                                           32767,
  7                                           '*');
  8  BEGIN
  9      l_start_time := dbms_utility.get_time;
 10      FOR idx IN 1 .. 100
 11      LOOP
 12          INSERT INTO lob_effect
 13          VALUES
 14              (idx,
 15               empty_clob())
 16          RETURNING loc INTO lob_loc;
 17          l_cnt := 0;
 18          WHILE (l_cnt < 32767)
 19          LOOP
 20              dbms_lob.WRITEAPPEND(lob_loc,
 21                             length(SubStr(data,
 22                                           l_cnt + 1,
 23                                           10000)),
 24                             SubStr(data,
 25                                    l_cnt + 1,
 26                                    10000));
 27              l_cnt := l_cnt + 10000;
 28          END LOOP;
 29      END LOOP;
 30      dbms_output.put_line('Time:' || (dbms_utility.get_time - l_start_time));
 31      COMMIT;
 32  END;
 33  /
Time:3934

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.03
SQL> truncate table lob_effect
  2  /

Table truncated.

Elapsed: 00:00:00.08
SQL> DECLARE
  2      l_cnt        PLS_INTEGER;
  3      l_start_time PLS_INTEGER;
  4      lob_loc      CLOB;
  5      data         VARCHAR2(32767) := RPad('*',
  6                                           32767,
  7                                           '*');
  8  BEGIN
  9      l_start_time := dbms_utility.get_time;
 10      FOR idx IN 1 .. 100
 11      LOOP
 12          INSERT INTO lob_effect
 13          VALUES
 14              (idx,
 15               empty_clob())
 16          RETURNING loc INTO lob_loc;
 17          l_cnt := 0;
 18
 19          dbms_lob.OPEN(lob_loc,
 20                        DBMS_LOB.lob_readwrite);
 21
 22          WHILE (l_cnt < 32767)
 23          LOOP
 24              dbms_lob.WRITEAPPEND(lob_loc,
 25                             length(SubStr(data,
 26                                           l_cnt + 1,
 27                                           10000)),
 28                             SubStr(data,
 29                                    l_cnt + 1,
 30                                    10000));
 31              l_cnt := l_cnt + 10000;
 32          END LOOP;
 33
 34          dbms_lob.CLOSE(lob_loc);
 35
 36      END LOOP;
 37      dbms_output.put_line('Time:' || (dbms_utility.get_time - l_start_time));
 38      COMMIT;
 39  END;
 40  /
Time:3924

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.02
SQL>
SQL>

And, using DBMS_LOB.WRITE ....


SQL> set serveroutput on
SQL> set timing on
SQL> DECLARE
  2      l_cnt        PLS_INTEGER;
  3      l_start_time PLS_INTEGER;
  4      lob_loc      CLOB;
  5      data         VARCHAR2(32767) := RPad('*',
  6                                           32767,
  7                                           '*');
  8  BEGIN
  9      l_start_time := dbms_utility.get_time;
 10      FOR idx IN 1 .. 100
 11      LOOP
 12          INSERT INTO lob_effect
 13          VALUES
 14              (idx,
 15               empty_clob())
 16          RETURNING loc INTO lob_loc;
 17          l_cnt := 0;
 18          WHILE (l_cnt < 32767)
 19          LOOP
 20              dbms_lob.WRITE(lob_loc,
 21                             length(SubStr(data,
 22                                           l_cnt + 1,
 23                                           10000)),
 24                             l_cnt + 1,
 25                             SubStr(data,
 26                                    l_cnt + 1,
 27                                    10000));
 28              l_cnt := l_cnt + 10000;
 29          END LOOP;
 30      END LOOP;
 31      dbms_output.put_line('Time:' || (dbms_utility.get_time - l_start_time));
 32      COMMIT;
 33  END;
 34  /
Time:3822

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.02
SQL> truncate table lob_effect
  2  /

Table truncated.

Elapsed: 00:00:00.07
SQL> DECLARE
  2      l_cnt        PLS_INTEGER;
  3      l_start_time PLS_INTEGER;
  4      lob_loc      CLOB;
  5      data         VARCHAR2(32767) := RPad('*',
  6                                           32767,
  7                                           '*');
  8  BEGIN
  9      l_start_time := dbms_utility.get_time;
 10      FOR idx IN 1 .. 100
 11      LOOP
 12          INSERT INTO lob_effect
 13          VALUES
 14              (idx,
 15               empty_clob())
 16          RETURNING loc INTO lob_loc;
 17          l_cnt := 0;
 18
 19          dbms_lob.OPEN(lob_loc,
 20                        DBMS_LOB.lob_readwrite);
 21
 22          WHILE (l_cnt < 32767)
 23          LOOP
 24              dbms_lob.WRITE(lob_loc,
 25                             length(SubStr(data,
 26                                           l_cnt + 1,
 27                                           10000)),
 28                             l_cnt + 1,
 29                             SubStr(data,
 30                                    l_cnt + 1,
 31                                    10000));
 32              l_cnt := l_cnt + 10000;
 33          END LOOP;
 34
 35          dbms_lob.CLOSE(lob_loc);
 36
 37      END LOOP;
 38      dbms_output.put_line('Time:' || (dbms_utility.get_time - l_start_time));
 39      COMMIT;
 40  END;
 41  /
Time:3815

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.01
SQL>
SQL>
 

Tom,
Is it possible to return CLOB output from a stored procedure? We are on Oracle 9.2.0.4.
Thanks

Hi Tom,

We have an issue currently in that we have AL32UTF8 data in our database. When we select the data into a varchar2 column then everything seems fine. However, we need to select this data and assign to a clob. When we do this, all the 8-bit characters become unreadable.

We have tried || to append and also dbms_lob.writeappend with the same result.

A test case would be:
1-191-50|OFTAMOLOGÍA / GLAUCOMA-DR|85656
1-191-159|PARKE DAVIS - DAVIS MEDICA-DR|169896
to be two rows of data.
Select it into a varchar2 variable line by line and try to display it looks exactly the same. However, when select into a clob variable and try to display, it looks totally different.

declare
v_error clob := '1-191-166|OFTAMOLOGÍA / GLAUCOMA-DL|8565';
begin
dbms_output.put_line (v_error);
end;
/

results in: 1-191-166|OFTAMOLOG� / GLAUCOMA-DL|8565

declare
v_error varchar2(100) := '1-191-166|OFTAMOLOGÍA / GLAUCOMA-DL|8565';
begin
dbms_output.put_line (v_error);
end;
/

results in: 1-191-166|OFTAMOLOGÍA / GLAUCOMA-DL|8565

Database character set: AL32UTF8
NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Hi Tom,

In my process I generate a SQL Query that is stored in a CLOB field. This SQL Query is more than 32767 bytes long.

When I try to open a cursor for this SQL Query or try execute immediate on this SQL Query stored as a CLOB I get the error:

PLS-00382: expression is of wrong type

When I use TO_CHAR on the CLOB field I to convert into a VARCHAR2 so that the open cursor can understand I get the following error:

ORA-06502: PL/SQL: numeric or value error

This error seems to be generated when the CLOB is over 32767 bytes long, anything under this value the the OPEN cursor FOR TO_CHAR(clob) works fine.

Any workarounds for the 32767 bytes long limitation?

Thank you.

Please suggest on following error

ERROR: Failed to get TD Servers properties; Failed to load admin properties from the database; Failed to cast Result Set value of field 'LICENSE_KEY' from type '2005' to string : Failed to convert clob to string : [Oracle] #3 ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_LOB' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ; STACK: java.sql.SQLException: Failed to cast Result Set value of field 'LICENSE_KEY' from type '2005' to string : Failed to convert clob to string : [Oracle] #3 ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_LOB' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored at

I know thats not the complete information but
what I can provide is
We were doing installation of quality tool the Application team forward me this message
as they think its related to dbms_lob ...
Please suggest something to move in right direction


Thanks!

Can you decode this @#$%^&*^%$$$.
I think its some crytic code.
You got a problem thats good. Dont Panic and totally be dependant on others to run the magic stick.Think logically and try to investigate the problem sometimes the solutions are very very simple but we tend to think this is the most difficult problem of the century.


Relax every problem has a solution.If you go to the Doctor you tell him whats wrong with you. You dont just sit and say OK diagnose and give me pills.

TOM will help only those who help Themselves.( Or was it GOD)
Hows that !!!!

OWNER OBJECT_NAME

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREA
TED LAST_DDL_ TIMESTAMP STATUS T G S
------------------------------ -------------------------------------------------
-------------------------------------------------------------------------------
------------------------------ ---------- -------------- ------------------ ----
----- --------- ------------------- ------- - - -
SYS DBMS_LOB

2775 PACKAGE 06-A
UG-01 17-FEB-05 2004-08-09:23:29:41 VALID N N N
SYS DBMS_LOB

2777 PACKAGE BODY 06-A
UG-01 17-FEB-05 2004-08-09:23:29:42 VALID N N N
PUBLIC DBMS_LOB

2776 SYNONYM 06-A
UG-01 06-AUG-01 2001-08-06:17:31:24 VALID N N N



dbms_lob is installed.Please suggest .

I tried to run the same code as in the original question, nevertheless, I got ORA-06502. What's wrong?

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table clob_test(
  2  id_col number
  3  ,value_col clob);

Tabulka vytvoøena.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE CLOB_TAB_TEST IS
  2       NUM       CLOB_TEST.ID_COL%TYPE;  -- INT
  3       V_VALUE      CLOB_TEST.VALUE_COL%TYPE; -- CLOB
  4   BEGIN
  5    NUM := 2;
  6    V_VALUE := rpad('*', 32999, '*');
  7    INSERT INTO CLOB_TEST VALUES (NUM, V_VALUE);
  8    COMMIT;
  9    DBMS_OUTPUT.PUT_LINE('Length = ' || to_char(length(v_value)));
 10   END;
 11  /

Procedura vytvoøena.

SQL> 
SQL> begin
  2    CLOB_TAB_TEST;
  3  end;
  4  /
begin
*
ERROR na øádku 1:
ORA-06502: PL/SQL: èíselná nebo hodnotová chyba: vyrovnávací pamì znakového
øetìzce je pøíliš malá
ORA-06512: na "DV.CLOB_TAB_TEST", line 6
ORA-06512: na line 2
 

I run the same on 10g, with the ORA-06502:

valasekd@DV10G> drop table clob_test
2 /

Table dropped.

valasekd@DV10G>
valasekd@DV10G> create table clob_test(
2 id_col number
3 ,value_col clob);

Table created.

valasekd@DV10G>
valasekd@DV10G> CREATE OR REPLACE PROCEDURE CLOB_TAB_TEST IS
2 NUM CLOB_TEST.ID_COL%TYPE; -- INT
3 V_VALUE CLOB_TEST.VALUE_COL%TYPE; -- CLOB
4 BEGIN
5 NUM := 2;
6 V_VALUE := rpad('*', 32999, '*');
7 INSERT INTO CLOB_TEST VALUES (NUM, V_VALUE);
8 COMMIT;
9 DBMS_OUTPUT.PUT_LINE('Length = ' || to_char(length(v_value)));
10 END;
11 /

Procedure created.

valasekd@DV10G>
valasekd@DV10G> begin
2 CLOB_TAB_TEST;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "VALASEKD.CLOB_TAB_TEST", line 6
ORA-06512: at line 2

I think the above errors you are getting is because of the rpad function which is a character function with a limit of 32K.

Got nothing to do with the CLOB.
Just replace it with
rpad('*', 32767, '*') and it works.

I think that the rpad limitation of 32K.

You may want to use some dbms_loB function to get strings of length greator than 32K.( Done know which ones )

Tom Please correct me if I am wrong.

begin

dbms_output.put_line( length( rpad('*', 32999, '*')) ) ;
end ;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 3

See this work fine :
CREATE OR REPLACE PROCEDURE CLOB_TAB_TEST IS
NUM CLOB_TEST.ID_COL%TYPE; -- INT
V_VALUE CLOB_TEST.VALUE_COL%TYPE; -- CLOB
BEGIN
NUM := 2;
V_VALUE := rpad('*', 32767, '*') ||rpad('*', 32767, '*') ;
INSERT INTO CLOB_TEST VALUES (NUM, V_VALUE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Length = ' || to_char(length(v_value)));
END;
/

So no CLOB problem RPAD problemo

Tom who can I test a procedure that returns a clob using SQLPLUS?

fondose@VENUS> var r refcursor
fondose@VENUS> var c clob
fondose@VENUS> exec pkg_ibtransac.pp_fondos('109030792', :r, :c);
BEGIN pkg_ibtransac.pp_fondos('109030792', :r, :c); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FONDOSE.PKG_IBTRANSAC", line 299
ORA-06512: at "FONDOSE.PKG_IBTRANSAC", line 316
ORA-06512: at "FONDOSE.PKG_IBTRANSAC", line 327
ORA-06512: at line 1

--on production we bind all variables!

I have fix it, but now I get another error:

fondose@VENUS> exec pkg_ibtransac.pp_fondos('109030792', :r, :c);
fondose@VENUS> print c

C
-----------------------------------------------------------
SP2-0642: SQL*Plus internal error state 2131, context 0:0:0
Unsafe to proceed
SP2-0625: Error printing variable "c"
--***************************
I have read the note 579067.993 on Metalink, but I don'nt get a solution.

ops$tkyte@ORA9IR2> create or replace procedure p( p_clob in out clob )
  2  as
  3  begin
  4          p_clob := 'hello world';
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x clob
ops$tkyte@ORA9IR2> exec p(:x);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> print x

X
-------------------------------------------------------------------------------
hello world


<b>need test case, as always</b>
 

I had found that the CLOB parameter can not return NULL, so I assigned an empty string (p_clob := '';), but I was the same, so I assigned an space (p_clob := ' ';), and it works, now it is going to check by the developers.

I have a table with a clob type attribute and so many rows in that table. Very few rows will have some data for this clob attribute.
Then I have a PL/SQL block where in I have to check the 'is null' condition for this clob column. If it is not null then that particular row has to be inserted into some other table.

But the problem is, since it is a clob data type we can't write like, for eg:,

If large_object is not null then

Loop



-- do some calculations or processing;



End loop;



Where ,assume, large_object is the name of the clob attribute in the source table.

Instead , I need some way where I can check for the clob data type is null or not.

why can't you?

ops$tkyte@ORA9IR2> create table t ( x int, y clob );

Table created.

ops$tkyte@ORA9IR2> insert into t values ( 1, null );

1 row created.

ops$tkyte@ORA9IR2> insert into t values ( 2, 'hello world' );

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          for x in ( select * from t )
  3          loop
  4                  if ( x.y is null )
  5                  then
  6                          dbms_output.put_line( 'when x = ' || x.x || ' y was null' );
  7                  else
  8                          dbms_output.put_line( 'when x = ' || x.x || ' y was NOT null' );
  9                  end if;
 10          end loop;
 11  end;
 12  /
when x = 1 y was null
when x = 2 y was NOT null

PL/SQL procedure successfully completed.
 

Thanks for the reply for my earlier question.But this may not give the correct result for the scenario i have.
In your follow up you inserted one row with null for the clob type, but i am doing some thing different.
I have an excel sheet where in all the data for the table is filled in. The first column in the excell will feed in the data for the first attribute in the table and so on. Now this table will act as the source where i need to check for the is null condition with the clob type. 
Now the below query returns no rows.
SQL> select * from questionnaire_stage where large_object is null;

no rows selected

SQL> 

Here large_object is clob type.

In the excel sheet, i left the cells empty for the clob type. Still the result is above. So in my code if i check the condition is null for this particular attribute no rows will get selected.

 

>>1 drop table t1;

Table dropped.

>>1 create table t1 (x clob);

Table created.

>>1 run
1* insert into t1 values(rpad('!',32767,'$'))

1 row created.

>>1 commit;

Commit complete.

>>1 select x,dbms_lob.getlength(x)from t1;

X DBMS_LOB.GETLENGTH(X)
-------------------------------------------------------------------------------- -------------------
!$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 4000

1 row selected.

>>>>>>>>>> Small QUESTION: Why is the length of the column value = 4000? Why not 32767?

>>1 update t1 set x=x||x;

1 row updated.

>>1 commit;

Commit complete.

>>1 select x,dbms_lob.getlength(x)from t1;

X DBMS_LOB.GETLENGTH(X)
-------------------------------------------------------------------------------- -------------------
!$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 8000

1 row selected.

>>>>>>>>>> NOTE: Well, at least it's consistent with the previous length of 4000.

>>1 select dbms_lob.substr(x)from t1;
select dbms_lob.substr(x)from t1
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

>>>>>>>>>> MAIN QUESTION: How can I get rid of this error?

Hi Tom,

I think if we try to assign any number to a clob variable, then implicit conversion does not take place but is should happen . Following test case proves this.

I am trying to assign number 1 of 60,000 occurenecs to a clob variable. If I don't do explicit conversion then it throws error.

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  str1 CLOB := ' ';
  3  str VARCHAR2(32000);
  4  amt NUMBER := 200;
  5  --CURSOR c1 IS SELECT agmt_key FROM sdc_agreement WHERE ROWNUM < 7000;
  6  BEGIN
  7  --FOR rec IN c1 LOOP
  8  FOR rec IN 1..33000 LOOP
  9  --str1 := rpad('*',35000,'*')||rpad('*',32000,'*');
 10  str1 := str1||'*';                                  ------ See I am assigning character
 11  END LOOP;
 12  dbms_output.put_line('String1: '||LENGTH(str1));
 13* END;
SQL> /
String1: 33001

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  str1 CLOB := ' ';
  3  str VARCHAR2(32000);
  4  amt NUMBER := 200;
  5  --CURSOR c1 IS SELECT agmt_key FROM sdc_agreement WHERE ROWNUM < 7000;
  6  BEGIN
  7  --FOR rec IN c1 LOOP
  8  FOR rec IN 1..33000 LOOP
  9  --str1 := rpad('*',35000,'*')||rpad('*',32000,'*');
 10  str1 := str1||to_char(1);                           ---------- See I am explicitly coverting number into character
 11  END LOOP;
 12  dbms_output.put_line('String1: '||LENGTH(str1));
 13* END;
SQL> /
String1: 33001

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  str1 CLOB := ' ';
  3  str VARCHAR2(32000);
  4  amt NUMBER := 200;
  5  --CURSOR c1 IS SELECT agmt_key FROM sdc_agreement WHERE ROWNUM < 7000;
  6  BEGIN
  7  --FOR rec IN c1 LOOP
  8  FOR rec IN 1..60000 LOOP
  9  --str1 := rpad('*',35000,'*')||rpad('*',32000,'*');
 10  str1 := str1||to_char(1);                            ---------- See I am explicitly coverting number into character
 11  END LOOP;
 12  dbms_output.put_line('String1: '||LENGTH(str1));
 13* END;
SQL> /
String1: 60001

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  str1 CLOB := ' ';
  3  str VARCHAR2(32000);
  4  amt NUMBER := 200;
  5  --CURSOR c1 IS SELECT agmt_key FROM sdc_agreement WHERE ROWNUM < 7000;
  6  BEGIN
  7  --FOR rec IN c1 LOOP
  8  FOR rec IN 1..60000 LOOP
  9  --str1 := rpad('*',35000,'*')||rpad('*',32000,'*');
 10  str1 := str1||1;                                     ---------- See I am not coverting number into character but got error
 11  END LOOP;
 12  dbms_output.put_line('String1: '||LENGTH(str1));
 13* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10


Could you please tell me why such implicit conversion is not happening ?

Thanks
Deba 

it is happening, it is just happening differently, the number is causing a to_char to be implicitly applied to both sides.

and the string is limited to 32k then.


ops$tkyte%ORA10GR2> l
  1  DECLARE
  2  str1 CLOB := ' ';
  3  BEGIN
  4  FOR rec IN 1..60000 LOOP
  5  begin
  6  str1 := str1||1;
  7  exception
  8     when others then dbms_output.put_line( 'error ' || rec );
  9  dbms_output.put_line('String1: '||LENGTH(str1));
 10     raise;
 11  end;
 12  END LOOP;
 13  dbms_output.put_line('String1: '||LENGTH(str1));
 14* END;
ops$tkyte%ORA10GR2> /
error 32768
String1: 32768
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10



see it breaks at 32k.

so, beware of implicit conversions - it was not a number conversion, it is a string overflow. 

Hi Tom,

Thanks for the reply. Here your explanation is not very clear. You are saying that "the number is causing a to_char to be implicitly applied to both sides " but
"both sides" is not clear to me . Both side of which one and how ? Plesae explain me

Here I am not forming any string at all. I am just adding one number ( which in turn should be implicitly converted to a character ) to a clob. So here a clob gets  formed, not a string. If you call it as string how we are able to add 60,000 characters to a string ( Here I call it clob ) where it is crossing 32k ? see the below please:

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  str1 CLOB := ' ';
  3  str VARCHAR2(32000);
  4  amt NUMBER := 200;
  5  --CURSOR c1 IS SELECT agmt_key FROM sdc_agreement WHERE ROWNUM < 7000;
  6  BEGIN
  7  --FOR rec IN c1 LOOP
  8  FOR rec IN 1..60000 LOOP
  9  --str1 := rpad('*',35000,'*')||rpad('*',32000,'*');
 10  str1 := str1||to_char(1);                 ---------- See I am explicitly coverting number into character
 11  END LOOP;
 12  dbms_output.put_line('String1: '||LENGTH(str1));
 13* END;
SQL> /
String1: 60001

PL/SQL procedure successfully completed.

Here I am not using any char function or char variables. There should not be any string formation.
Please clraify me.

Thanks
Deba 

Can you explain what is happening here? It looks like you can "have" a VARCHAR2 value that is 64k long. Is it an implicit conversion to a CLOB, or is something else going on?

Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production

SQL> set serveroutput on;
SQL> DECLARE 
  2      PROCEDURE foo( p_bar IN VARCHAR2 ) IS
  3      BEGIN
  4          dbms_output.put_line(LENGTH(p_bar));
  5      END;
  6  BEGIN
  7      foo( RPAD('x', 65534, 'x') );
  8  END;
  9  /
65534

PL/SQL procedure successfully completed.

I have a packaged procedure p1 (p_clob in clob, p_clob_out out clob).

I will have to test the procedure whether it is running fine or not. I am getting a biiiiig string which is of size >32kb (say 76 KB).
I need to execute the procedure.
Please tell me how will I do that?

whenever I am calling the procedure p1(that biiiig string, outxml) it is giving an error string lateral too long.

I have the function as below:
FUNCTION insert_xml_in_table (p_xml IN clob) RETURN NUMBER IS
v_orig_xml sys.xmltype;
v_inserted_xml_id NUMBER;
vBuffer VARCHAR2 (32767);
l_amount BINARY_INTEGER := 32767;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob CLOB := EMPTY_CLOB;

BEGIN
-- initalize the new clob
dbms_lob.createtemporary(newClob,TRUE);

l_clob_len := dbms_lob.getlength(p_xml);
dbms_output.put_line(l_clob_len);
WHILE l_pos < l_clob_len
LOOP
dbms_lob.READ(p_xml, l_amount, l_pos, vBuffer);

IF vBuffer IS NOT NULL THEN
dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
END IF;
l_pos := l_pos + l_amount;
END LOOP;
v_orig_xml:=sys.xmltype.createXML(newClob);
dbms_output.put_line('Converted');
INSERT INTO T_PYWORKPAGEXML VALUES(seq_pega_xml.NEXTVAL, v_orig_xml)
returning id INTO v_inserted_xml_id;
COMMIT;
dbms_output.put_line(v_inserted_xml_id);
dbms_output.put_line('XML inserted INTO the TABLE');
RETURN v_inserted_xml_id;
EXCEPTION
WHEN OTHERS
THEN RAISE;
END insert_xml_in_table;


This function will have the input parameter which has the size of 76 KB.
And I could not run this function with the big xml as it is giving "string lateral too long". And when I run it from a BPM tool, it showed me an error like "numeric or value error".
Please suggest something.

Hi Tom,

I have the below question (No. 4) on CLOB .

1) I have a table with below structure

create table magazine_articles
(author varchar2(30),article_name varchar2(50),article_date date,article_data clob)
TABLESPACE LOB_DATA
STORAGE (INITIAL 2M NEXT 2M)
LOB (ARTICLE_DATA)
STORE AS (DISABLE STORAGE IN ROW)
NOCACHE

2) Then I have populated some rows in the above table

3) I have created an another table as given below

CREATE TABLE ORACLE9I_REFERENCES
( ORACLE9I_ARTICLE ROWID, INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE)
TABLESPACE USERS;

4) now the question is :
For any row in the MAGAZINE_ARTICLES table that contains three or more references to data insert the corresponding rowid and a timestamp for the time that it was inserted into the ORACLE9I_REFERENCES table.

Thank you...

Tom:

In 9i R2 is the clob variable size limitation 32 K or 4 g bytes.
I thought everything was limited to 32K but i was told clob is not.


But then I have a procedure that fails when my pl/sql clob variable size exceeds 32K.
This procedure creates an xml file and then saves it into a log table with CLOB column.
The log record is already created so i need to update (not insert)
the CLOB. Do you i have to do write to table every 32K and initialize the l_var or i can have CLOB variable up to 4 gig
and at then end write it to the table .can yo ushow me how you do it.



PROCEDURE RUN_REPORT (
AS
l_my_var clob;

procedure print(p_string in varchar2)
is
begin
htp.p(p_string);
l_my_var := l_my_var ||p_string; ---(failing here when > 32K with value error)
end;

BEGIN
FOR x in (SELECT * from magz)
LOOP
print(' <ROW>');
print(' '||'<MAGNO>'||x.magno||'</magno>');
print(' '||'<PATH>'||x.dir||'</PATH>');
print(' </ROW>');

END LOOP;

UPDATE_LOG(v_log_no,l_my_var);


END;

PROCEDURE UPDATE_LOG
( p_log_no IN VARCHAR2 DEFAULT NULL,
p_xml_text IN VARCHAR2 DEFAULT NULL)

IS
pragma autonomous_transaction;

BEGIN


UPDATE log_tbl
SET mesg_out = p_xml_text,
date_out = sysdate
WHERE log_no = p_log_no;

COMMIT;

END; -- Procedure

ops$tkyte%ORA9IR2> create table t ( x int, y clob );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 1, null );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure update_log( p_x in t.x%type, p_my_var in t.y%type )
  2  as
  3  begin
  4          update t set y = p_my_var where x= p_x;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA9IR2> show err
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure run_report
  2  as
  3          l_my_var clob;
  4  begin
  5          for x in (select owner,object_name,object_type from all_objects where rownum <= 1000)
  6          loop
  7                  l_my_var := l_my_var ||
  8                             '<row><owner>' || x.owner ||
  9                             '</owner><object_name>' || x.object_name ||
 10                                     '</object_name><owner>' || x.owner ||
 11                                     '</owner></row>';
 12          end loop;
 13          update_log( 1, l_my_var );
 14  end;
 15  /

Procedure created.

ops$tkyte%ORA9IR2> show err
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec run_report

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select x, dbms_lob.getlength(y) from t;

         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         1                101571

Tom:
It was failing because of the 32K limitation for long pl/sql variable.

I changed that to CLOB and i change the IN parameter in update_LOG to CLOB.

But i also used the DBMS_LOB to initialize the variable and the WRITEAPPEND to write to it. Are you saying that i do not need that in ORacle 9i and things will work normal?


PROCEDURE RUN_REPORT (...)
AS
l_my_var clob;
l_line varchar2(2000);
BEGIN
-- initialize temporary clob
DBMS_LOB.CREATETEMPORARY(l_my_var,TRUE);

FOR x in (SELECT * from magz)
LOOP

l_line := ' <ROW>' ;
l_line := l_line ||' '||'<MAGNO>'||x.magno||'</magno>';
l_line := l_line ||' '||'<PATH>'||x.dir||'</PATH>';
l_line := l_line ||' </ROW>';

htp.p(l_line);
dbms_lob.writeappend(l_my_var, length(l_line), l_line);
END LOOP;

UPDATE_LOG(v_log_no,l_my_var);

-- time to let it go ...
DBMS_LOB.FREETEMPORARY(l_my_var);
END;

scott@10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.01
scott@10GR2>
scott@10GR2> variable x clob;
scott@10GR2>
scott@10GR2> CREATE OR REPLACE FUNCTION fn_exe
  2     RETURN CLOB
  3  IS
  4     v1       VARCHAR2 (32767) := ' select ''senthil'' from dual where 1<>2';
  5     v2       VARCHAR2 (32767);
  6     v3       VARCHAR2 (32767);
  7     l_clob   CLOB;
  8  BEGIN
  9     v1 := RPAD (v1, 32764, ' and 2=2 ');
 10     v1 := SUBSTR (v1, 1, LENGTH (v1) - 3);
 11     v2 := RPAD (' and 2=2 ', 32764, ' and 2=2 ');
 12     v2 := SUBSTR (v2, 1, LENGTH (v2) - 3);
 13     v3 :=  v2;
 14  --   l_clob := v1 || v2 || v3;
 15     dbms_output.put_line (length(v1));
 16     dbms_output.put_line (length(v2));
 17     dbms_output.put_line (length(v3));
 18     RETURN l_clob;
 19
 20  END;
 21  /

Function created.

Elapsed: 00:00:00.06
scott@10GR2>
scott@10GR2>
scott@10GR2> exec :x := fn_exe;
32761
32761
32761

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
scott@10GR2>
scott@10GR2>
scott@10GR2> print x;

X
--------------------------------------------------------------------------------


scott@10GR2>
scott@10GR2>
scott@10GR2> CREATE OR REPLACE FUNCTION fn_exe
  2     RETURN CLOB
  3  IS
  4     v1       VARCHAR2 (32767) := ' select ''senthil'' from dual where 1<>2';
  5     v2       VARCHAR2 (32767);
  6     v3       VARCHAR2 (32767);
  7     l_clob   CLOB;
  8  BEGIN
  9     v1 := RPAD (v1, 32764, ' and 2=2 ');
 10     v1 := SUBSTR (v1, 1, LENGTH (v1) - 3);
 11     v2 := RPAD (' and 2=2 ', 32764, ' and 2=2 ');
 12     v2 := SUBSTR (v2, 1, LENGTH (v2) - 3);
 13     v3 :=  v2;
 14     l_clob := v1 || v2 || v3;
 15     dbms_output.put_line (length(v1));
 16     dbms_output.put_line (length(v2));
 17     dbms_output.put_line (length(v3));
 18     RETURN l_clob;
 19  END;
 20  /

Function created.

Elapsed: 00:00:00.04
scott@10GR2>
scott@10GR2> exec :x := fn_exe;
BEGIN :x := fn_exe; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.FN_EXE", line 14
ORA-06512: at line 1


Elapsed: 00:00:00.04
scott@10GR2>
scott@10GR2>
scott@10GR2>

Tom:

Is that CLOB is not able to process 96K of SQL string in plsql?

ops$tkyte%ORA10GR2> CREATE OR REPLACE FUNCTION fn_exe
  2     RETURN CLOB
  3  IS
  4     v1       VARCHAR2 (32767) := ' select ''senthil'' from dual where 1<>2';
  5     v2       VARCHAR2 (32767);
  6     v3       VARCHAR2 (32767);
  7     l_clob   CLOB;
  8  BEGIN
  9     v1 := RPAD (v1, 32764, ' and 2=2 ');
 10     v1 := SUBSTR (v1, 1, LENGTH (v1) - 3);
 11     v2 := RPAD (' and 2=2 ', 32764, ' and 2=2 ');
 12     v2 := SUBSTR (v2, 1, LENGTH (v2) - 3);
 13     v3 :=  v2;<b>
 14     l_clob := l_clob || v1 || v2 || v3;</b>
 15     dbms_output.put_line (length(v1));
 16     dbms_output.put_line (length(v2));
 17     dbms_output.put_line (length(v3));
 18     RETURN l_clob;
 19  END;
 20  /

Function created.

ops$tkyte%ORA10GR2> exec :x := fn_exe;
32761
32761
32761

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( dbms_lob.getlength(:x) )
98283

PL/SQL procedure successfully completed.

Hi Tom ,
we are constructing SQL which has more that 300 column ,2 FACT table & 4 or 5 dimension joins , with sub queries, each column have long alias names. SQL is a "merge" statement so SQL length get doubled with insert & update. This SQL is of 8 page length.

When i run the SQL its just hangs even for 30 minutes we don't what happening,
even tables are kept empty. For me it looks like parsing of this SQL is taking so much time.

I am not sure how to find what is the root cause for this issue, how can I figure out this problem.
Is there is any limit on SQL Query string's length ? Or any resource crunch should I check.

Same SQL is running fine if number for column limited to 50+
please tell what i should check.

Thanks
Ramki

SQL is constructed in JAVA environment, executed via JDBC.