Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 30, 2002 - 10:04 pm UTC

Answered by: Tom Kyte - Last updated: October 04, 2010 - 7:48 am UTC

Category: SQL*Plus - Version: 9.0.1.1.0

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I understand there is a length limitation (max 32000) for all the plsql variable. However, I have the following procedure.

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('*', 32999, '*');

INSERT INTO CLOB_TEST VALUES (NUM, V_VALUE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Length = ' || to_char(length(v_value)));
END;
/

The V_VALUE variable is assigned a 32999 long string and the procedure can be run without any error.

Am I just lucky here? Or is there anything that i am not aware of?

Another question is: If I use "||" to build a clob string instead of dbms_log.write, will I get wrong data?

Thank You!

Junying

and we said...

9i has made vast improvements to the lob types. You can treat a clob like it was a string for most purposes.

So, lobs behave pretty much like strings under the covers in 9i and up.

and you rated our response

  (40 ratings)

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

Reviews

So is there a max length Oracle can handle?

May 31, 2002 - 8:38 am UTC

Reviewer: Junying from Melbourne, Australia

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

Tom Kyte

Followup  

May 31, 2002 - 8:54 am UTC

In my book -- I used a varchar2 implementation, that is limited to 32k.

If you use a lob, it is 4gig.



Using more than 32k

June 27, 2002 - 12:22 pm UTC

Reviewer: Vikas from Delhi India

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



Tom Kyte

Followup  

June 27, 2002 - 12:47 pm UTC

declare
l_clob clob;
begin
insert into t values ( empty_clob() ) returning clob_col into l_clob;

for i in 1 .. 10
loop
dbms_lob.writeAppend( l_clob, 32000, rpad( '*', 32000, '*' ) );
end loop;
commit;
end;
/

is an example? don't know what you are looking for here really. A clob is a lot like a file -- you can "seek" in it (dbms_lob.substr, dbms_lob.read), you can write to it (dbms_lob.write, writeappend) and so on. pretend it is a file and you might be better off understanding it.

May 28, 2003 - 4:36 am UTC

Reviewer: A reader

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?

Tom Kyte

Followup  

May 28, 2003 - 7:06 am UTC

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.

 

May 28, 2003 - 7:50 am UTC

Reviewer: A reader

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

Tom Kyte

Followup  

May 28, 2003 - 7:52 am UTC

without an example -- no, not really. if you run mine, what happens for you.

May 28, 2003 - 12:32 pm UTC

Reviewer: A reader

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


DBMS_LOB.OPEN

May 28, 2003 - 1:21 pm UTC

Reviewer: Kamal Kishore from New Jersey, USA

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,


Tom Kyte

Followup  

May 28, 2003 - 7:13 pm UTC

benchmark it for us -- see runstats on asktom.oracle.com/~tkyte

I've never heard that myself, sounds like an interesting test to setup ;)

Was able to locate it...

May 28, 2003 - 9:29 pm UTC

Reviewer: Kamal Kishore from New Jersey, USA

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.


Tom Kyte

Followup  

August 03, 2008 - 2:15 pm UTC

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>


 

And, here is some benchmark tests...

May 28, 2003 - 11:42 pm UTC

Reviewer: Kamal Kishore from New Jersey, USA

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 Kyte

Followup  

May 29, 2003 - 8:05 am UTC

Your test only wrote to the lob 4 times with 10,000 10,000 10,000 2,767 bytes in succession.

You have to hit it lots more then that to see the performance issue -- see the benchmark above.

Can I use CLOB as output from a stored procedure

February 03, 2004 - 2:24 pm UTC

Reviewer: A reader

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


Tom Kyte

Followup  

February 03, 2004 - 3:34 pm UTC

yes,

procedure p( p_clob out clob )
....


absolutely.

Character Conversion

February 02, 2005 - 1:29 pm UTC

Reviewer: Christopher Soza from UK

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.

Tom Kyte

Followup  

February 03, 2005 - 1:11 am UTC

how about the entire test case. something I could cut and paste if I wanted to...

if the al32utf8 data in a al32utf8 database? give lots more detail here please.

OK here it is a very simple test case..

February 03, 2005 - 5:35 am UTC

Reviewer: Christopher Soza from UK

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


Tom Kyte

Followup  

February 03, 2005 - 1:41 pm UTC

i'll try to look at this when I can -- can you give the DUMP() output as well of the varchar. I'll have to set up a test instance. might be a while (on travel)

CLOB to VARCHAR2 Conversion limited to 32767

February 15, 2005 - 8:11 am UTC

Reviewer: Jasbir Kular from Toronto, Ontario

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.



Tom Kyte

Followup  

February 15, 2005 - 3:30 pm UTC

not using native dynamic sql in plsql

using dbms_sql, yes.

dbms_lob

February 18, 2005 - 1:36 pm UTC

Reviewer: abc

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

Tom Kyte

Followup  

February 18, 2005 - 2:59 pm UTC

you are kidding right.


I like this error better:

[tkyte@xtkyte-pc tkyte]$ oerr ora 1234
01234, 00000, "cannot end backup of file %s - file is in use or recovery"
// *Cause: Attempted to end an online backup of file when the file is busy.
// Some operation such as recovery or rename may be active, or there
// may still be some instance that has the database open with this
// file online.
// *Action: If there is an instance with the database open then the backup
// can be ended there by using the ALTER TABLESPACE command. Otherwise
// wait for the completion of the other operation.
[tkyte@xtkyte-pc tkyte]$


i mean, what am I supposed to say here?

no idea what environment, anything here. just a bunch of text. no example to work with.



LOL

February 18, 2005 - 4:08 pm UTC

Reviewer: A reader


help

February 23, 2005 - 2:04 am UTC

Reviewer: abc

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!


Tom Kyte

Followup  

February 23, 2005 - 2:11 am UTC

how could anyone point you anywhere with just an error message like that?


you are installing a 3rd party tool -- question needs to go to 3rd party vendor (but hey, it does look like dbms_lob might not be installed, as that is a very very basic package that would be in all databases -- you might ask the DBA "did you do this right?" just log in your self and see if you see dbms_lob)

Question for abc

February 23, 2005 - 10:22 am UTC

Reviewer: A reader

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 !!!!

DBMS_LOB

February 23, 2005 - 12:05 pm UTC

Reviewer: abc

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 .

some doubt

February 24, 2005 - 3:42 am UTC

Reviewer: Dushan from Czech rep.

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
 

Tom Kyte

Followup  

February 24, 2005 - 5:44 am UTC

looks like it reverted back to 32k with 9.2 -- not sure which is the "bug" that it worked in 9.0 or that it does not work in 9.2

FYI, the same story for 10g

February 24, 2005 - 10:30 am UTC

Reviewer: Dushan from Czech rep.

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


Tom Kyte

Followup  

February 24, 2005 - 4:54 pm UTC

as did for me, same answer as above.

Its the RPAD limitation

February 24, 2005 - 12:49 pm UTC

Reviewer: A reader

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.

Tom Kyte

Followup  

February 24, 2005 - 5:14 pm UTC

that is what I concluded as well

ITs the RPAD - Continued

February 24, 2005 - 12:55 pm UTC

Reviewer: A reader

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



ITs RPAD continued 2

February 24, 2005 - 2:08 pm UTC

Reviewer: A reader

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

Return a CLOB

October 20, 2005 - 1:19 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

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!

Tom Kyte

Followup  

October 20, 2005 - 4:51 pm UTC

you did it right, the code failed inside doing a string assignment, not really sure this is even remotely related to the clob.

Return a CLOB

October 20, 2005 - 2:02 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

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.

Tom Kyte

Followup  

October 20, 2005 - 4:53 pm UTC

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>
 

Return a CLOB

October 20, 2005 - 3:05 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

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.

Check whether the clob field is empty

January 13, 2006 - 7:42 am UTC

Reviewer: Raviraj from India

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.





Tom Kyte

Followup  

January 13, 2006 - 11:16 am UTC

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.
 

Check whether the clob field is empty

January 16, 2006 - 5:06 am UTC

Reviewer: Raviraj from India

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.

 

Tom Kyte

Followup  

January 16, 2006 - 9:39 am UTC

Hey, you were the one that said "i have to check the 'is null'..." I had to assume you meant what you said ;)

what is in the clob then? Just an empty_clob() -- you have to tell me what you put in there. If it is an empty_clob(), perhaps you are looking for "where length(large_object) = 0"



Problem Converting CLOB to VARCHAR2

February 16, 2006 - 10:18 am UTC

Reviewer: A reader

>>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?

Tom Kyte

Followup  

February 17, 2006 - 8:33 am UTC

varchar2's in sql are limited to 4000 bytes, period.


with dbms_lob.substr - select out 4000 or less bytes, SQL is limited to 4000 bytes.

PLSQL on the other hand is limited to 32k. You should select out the lob locator (just x) and then in plsql you can dbms_lob.substr upto 32k of it.

Problem in implicit conversion from number to character while assigning to a clob variable

December 07, 2006 - 4:56 am UTC

Reviewer: Deba from India

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 

Tom Kyte

Followup  

December 07, 2006 - 12:39 pm UTC

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. 

Problem in implicit conversion from number to character while assigning to a clob variable

December 08, 2006 - 12:14 am UTC

Reviewer: Deba from India

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 

Tom Kyte

Followup  

December 08, 2006 - 7:39 am UTC

both sides of the assignment

the clob is apparently being placed into a string temporarily when you rely on implicit conversion

and you should not rely on implicit conversion - ever, recipe for disaster


so the solution is really clear - use explicit conversions.

VARCHAR2 longer than 32k?

January 18, 2007 - 3:19 pm UTC

Reviewer: Milo van der Leij from Midland, MI USA

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.

assign a value to a clob

July 31, 2008 - 9:47 am UTC

Reviewer: Dibyendu from India

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.

Tom Kyte

Followup  

August 02, 2008 - 5:47 pm UTC

don't use a string literal, they are limited to 32k.

how do you want to 'test' this procedure, why not just have a table of 'test data' that you select from, and pass to this procedure.

Number or value error

July 31, 2008 - 12:08 pm UTC

Reviewer: Dibyendu from India

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

Followup  

August 03, 2008 - 1:35 pm UTC

the client calling this would have to send a clob, string literals are 32k in length - period.

Your "bpm" tool needs to allocate a clob, write the data into the clob and then send it to the database - you need to do this not only because of 32k, but because you NEED TO BIND inputs, not hard code them into a string.

clob

August 03, 2008 - 1:15 pm UTC

Reviewer: A reader


Kindly help to solve the below query

August 27, 2008 - 10:31 am UTC

Reviewer: Ajay J from INDIA

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 Kyte

Followup  

August 28, 2008 - 9:06 am UTC

I don't get the question. what does "three or more references to data" mean in this context?

I see NO foreign keys, I see nothing to "reference"


you are missing primary keys, stop using rowid - do not do that, use primary keys to identify rows. Too many things change rowids these days, reorganizations and other operations.

clob size

May 19, 2009 - 4:58 pm UTC

Reviewer: A reader

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

Followup  

May 23, 2009 - 12:41 pm UTC

longs/long raws are 2gb

clobs/blobs in 9i are 4gb
in 10g - could be terabytes - blocksize and os dependent.


In plsql - a variable of type varchar2 (long is a subtype of varchar2 - long in plsql is limited to 32k) is limited to 32k

a variable of type clob can point to upto 4g (in 9i) or more.


In older releases (older than 9i) you might have to use dbms_lob create temporary and dbms_lob.writeappend - the ability to directly manipulate lobs of any length in plsql is 'relatively new' (as in the last ten years)


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




clob

May 26, 2009 - 9:38 pm UTC

Reviewer: A reader

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;

Tom Kyte

Followup  

May 27, 2009 - 8:05 am UTC

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

umm, did you see my example??!?! It sort of exceeded 32k.


read my example, run it, I did it in 9i

Function Return CLOB in 10gR2.

August 16, 2010 - 9:26 am UTC

Reviewer: Rajeshwaran, Jeyabal

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?
Tom Kyte

Followup  

August 19, 2010 - 1:07 am UTC

it must be using a string temporary for the v1||v2||v3 - which fails.

but this works:

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.





that makes the temporary itself a clob before assigning to the clob.

Limit on SQL Query String's length

October 04, 2010 - 6:45 am UTC

Reviewer: Ramki

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

Followup  

October 04, 2010 - 7:48 am UTC

sql_trace it, you can tail -f the trace file and see what it is doing in real time.

and when it is running, from another session you can "watch it" by querying v$session_event/session_wait

see what it is waiting on.

Limit on SQL Query String's length

October 04, 2010 - 6:48 am UTC

Reviewer: Ramki

SQL is constructed in JAVA environment, executed via JDBC.