Skip to Main Content
  • Questions
  • problem in execute immediate using CLOB datatype

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ashiq.

Asked: December 24, 2002 - 8:57 am UTC

Last updated: August 28, 2013 - 7:19 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom

1) I've problem executing a native dynamic sql using clob data type.Then i referred the docs its say native dynamic won't support CLOB datatypes.

Below i've give the table , pl/sql block and error.

ashiq@RMSREAL8> desc a
Name Null? Type
----------------------------------------------------- -------- ------
NAME CLOB


ashiq@RMSREAL8> select * from a;

NAME
--------------------------------------------------
select * from tab



ashiq@RMSREAL8> ed
Wrote file afiedt.buf

1 declare
2 v_strsql clob;
3 begin
4 select name
5 into v_strsql
6 from a;
7 execute immediate v_strsql;
8* end;
ashiq@RMSREAL8> /
execute immediate v_strsql;
*
ERROR at line 7:
ORA-06550: line 7, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored


Can u give me another way around to do this with an example.

Even if i try to delete the records ,its giving


ashiq@RMSREAL8> delete from a where name = 'ashiq';
delete from a where name = 'ashiq'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes

Why its throwing this error???

Since the sql statement what we store in database will be morethan 4000 bytes ,so we changed to CLOB datatype.


2) what's the maximum number index can i've for a table?

3) what is the max number of lines(in the sense number of codes of lines) in can i've procedure / function ? ? ?


and Tom said...

In 9i, when a string and a clob are virtually synonymous -- this is easy. In 8i, you must use the dbms_lob package for most lob interactions.


ops$tkyte@ORA817DEV> create table t (name clob);

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t values ( 'select * from dual' );

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
2 l_strsql clob;
3 begin
4 select name into l_strsql from t;
5 execute immediate l_strsql;
6 end;
7 /
execute immediate l_strsql;
*
ERROR at line 5:
ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored


ops$tkyte@ORA817DEV> declare
2 l_strsql LONG;
3 l_dummy dual.dummy%type;
4 begin
5 select dbms_lob.substr( name, 32765, 1 ) into l_strsql from t;
6 execute immediate l_strsql into l_dummy;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> delete from t where name = 'x';
delete from t where name = 'x'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes


ops$tkyte@ORA817DEV> delete from t where dbms_lob.substr( name, 32765, 1 ) = 'x';

0 rows deleted.


2) infinite

3) there is the max number possible.

Then there is the max number PRACTICAL. Max number possible is many orders of magnitude higher then practical.

A procedure or function shouldn't have more then a couple dozen/hundred lines of code.

It can have 10's of thousands. But don't do it!

There is no hard limit on the number of lines, rather it is a function of the complexity of the code itself.



Rating

  (25 ratings)

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

Comments

problem in execute immediate using CLOB datatype

Ashiq, December 24, 2002 - 11:01 am UTC

what this actually do dbms_lob.substr( name, 32765, 1 ) ?

Tom Kyte
December 24, 2002 - 12:22 pm UTC

fortunately, we document all of this stuff!!


</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/toc.htm <code>

ctl-f for dbms_lob -- but feel free to read some of the others.

Over 4000 characters

John, January 20, 2003 - 7:09 pm UTC

Is there a way to get around this in PL/SQL (using 8.1.7):

<PRE>
SQL> select dbms_lob.getlength( name ) from t;

DBMS_LOB.GETLENGTH(NAME)
------------------------
                    4001
SQL> declare
  2   l_strsql LONG;
  3   l_dummy  dual.dummy%type;
  4  begin
  5   select dbms_lob.substr( name, 32765, 1 ) into l_strsql from t;
  6   execute immediate l_strsql into l_dummy;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
ORA-06512: at line 5
</PRE>
 

Tom Kyte
January 20, 2003 - 7:26 pm UTC

....

  subtype LONG is VARCHAR2(32760);

.....


as defined in package standard, you can get another 5 characters like this:

ops$tkyte@ORA817DEV> declare
  2          l_strsql varchar2(32765);
  3  begin
  4          l_strsql := rpad( '*', 32765, '*' );
  5          dbms_output.put_line( length(l_strsql) );
  6  end;
  7  /
32765

PL/SQL procedure successfully completed.


is that what "get around this" means?  not sure what "this" is. 

I think this is what he meant...

Jon, January 20, 2003 - 8:08 pm UTC

Hi Tom,

I think what John from Sydney was saying is this:

declare
vStr long;
begin
select dbms_lob.substr(xml,4000,1) into vStr
from correspondence
where correspondence_id = 1;
end;

PL/SQL procedure successfully completed

declare
vStr long;
begin
select dbms_lob.substr(xml,4001,1) into vStr
from correspondence
where correspondence_id = 1;
end;

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

It is something that is a real pain in 8.1.7 and earlier - can't select into anything longer than 4000 chars even in PL/SQL. In the same way, you can't concat > 4000 chars in PL/SQL:

declare
vStr long;
begin
select rpad('*',2000,'*')||rpad('#',2000,'#')
into vStr
from dual;
end;

PL/SQL procedure successfully completed

declare
vStr long;
begin
select rpad('*',2001,'*')||rpad('#',2000,'#')
into vStr
from dual;
end;

ORA-01489: result of string concatenation is too long
ORA-06512: at line 4

I rate right up there as the most annoying *feature* - part of that artificial distinction between PL/SQL and SQL. I understand that has been addressed in 9i with the combining of the PL/SQL and SQL compilers. Is that correct?

Tom Kyte
January 20, 2003 - 8:17 pm UTC

It is not PLSQL -- it is a SQL limitation -- still there.  in SQL varchar2 is 4000 characters -- period.

this is the way to accomplish the above:

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

Table created.

ops$tkyte@ORA817DEV> declare
  2          l_clob  clob;
  3  begin
  4          insert into t values ( empty_clob() ) returning x into l_clob;
  5          for i in 1 .. 10
  6          loop
  7                  dbms_lob.writeAppend( l_clob, 32000, rpad('*', 32000, '*' ) );
  8          end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select dbms_lob.getlength( x ) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
               320000

<b>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_clob  clob;
  3          l_str   long;
  4  begin
  5          select x into l_clob from t;
  6          l_str := dbms_lob.substr( l_clob, 32760, 1 );
  7
  8          l_str := rpad( '*', 2001, '*' ) || rpad( '#', 2000, '#' );
  9  end;
 10  /

PL/SQL procedure successfully completed.

</b>

why would you use a select to concatenate two strings??  anyway -- it is all about SQL, not PLSQL here -- 4000 is the limit.  Fortunately they preserved this "artificial barrier" and keep it so plsql can do 32k strings, not 4000 character strings</b>g

 

Agree with you, but...

Jon, January 20, 2003 - 9:00 pm UTC

Yes, understand that. However try explaining to someone new to PL/SQL that:

declare
vName long;
begin
select title||','||name||','||address||','||hobbies
into vName
from person
where person_id = 1234;
end;

1) This is part SQL, part PL/SQL, where abouts one ends and the other begins, and that rules can be different for both
-and-
2) could produce an error depending on data in these columns if, say hobbies was an varchar2(4000), even though we are working with a 32K string.

That is what I mean about artificial distinction - it is quirky to say the least. From the inside it makes sense (knowing architecture) - but from the outside looking in, it certainly causes confusion and frustration. Seen it many a times, especially for those coming from other programming environments, or other DBs. T-SQL doesn't have such restrictions.

Has this behaviour been modified in 9i?

Tom Kyte
January 21, 2003 - 8:54 am UTC

1) well, the SQL starts with one of 5 keywords -- select, insert, update, delete and merge.... I guess I would start there?



T-SQL, yes, funny to compare t-sql to plsql. I've written with both. writing with tsql is like trying to write with the full functionality of a command.com scripting language.


sql = 4000
plsql = bigger than 4000

4000 limit

John, January 20, 2003 - 9:22 pm UTC

Thanks Jon, that was kind of what I was getting at. 
The problem actually was executing native dynamic SQL, where the text in the CLOB was over 4000 characters.

Tom's answer above can be used with a slight modification to do the clob->long conversion in PL/SQL rather than in SQL(for all the reasons you've talked about):

SQL> DECLARE
  2   l_clob   CLOB;
  3   l_strsql LONG;
  4   l_dummy  dual.dummy%TYPE;
  5  BEGIN
  6   SELECT name INTO l_clob FROM t;
  7   l_strsql := dbms_lob.SUBSTR( l_clob, 32765, 1 );
  8   EXECUTE IMMEDIATE l_strsql INTO l_dummy;
  9* END;
SQL> /

PL/SQL procedure successfully completed. 

Tom Kyte
January 21, 2003 - 9:13 am UTC

or even:

SQL> DECLARE
  2   l_clob   CLOB;
  4   l_dummy  dual.dummy%TYPE;
  5  BEGIN
  6   SELECT name INTO l_clob FROM t;
  8   EXECUTE IMMEDIATE dbms_lob.SUBSTR( l_clob, 32765, 1 ) INTO l_dummy;
  9* END;
SQL> /

Or in 9i when clobs are just big "strings" in plsql (where you can use a varchar2, you can use a clob -- you can LIKE a clob, instr a clob, whatever)


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

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          l_str long;
  3  begin
  4          l_str := rpad( ' ', 32000, ' ' ) || 'select ''Hello World'' from dual';
  5          insert into t values ( l_str );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select dbms_lob.getlength(x) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
                32030

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          l_dummy varchar2(25);
  3          l_clob  long;
  4  begin
  5          select x into l_clob from t;
  6          execute immediate l_clob into l_dummy;
  7          dbms_output.put_line( l_dummy );
  8  end;
  9  /
Hello World

PL/SQL procedure successfully completed.
 

CLOBS aren't always like CHARS or VARCHARS in 9i

Mike, August 11, 2004 - 1:13 pm UTC

Take for example the following:

create table clobtest (cdata clob);
insert into clobtest (cdata) values ('Hello');
insert into clobtest (cdata) values ('Goodbye');
select cdata from clobtest where cdata like 'Goodbye';
select cdata from clobtest where cdata = 'Goodbye';

The select with the "like" comparsion works, but the one with the "=" comparison throws an ora-00932 exception.  Is this a bug?

SQL> create table clobtest (cdata clob);

Table created.

SQL> insert into clobtest (cdata) values ('Hello');

1 row created.

SQL> insert into clobtest (cdata) values ('Goodbye');

1 row created.

SQL> select cdata from clobtest where cdata like 'Goodbye';

CDATA
---------------------------------------------------------------------------

Goodbye

SQL> select cdata from clobtest where cdata = 'Goodbye';
select cdata from clobtest where cdata = 'Goodbye'
                                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
 

Tom Kyte
August 11, 2004 - 2:03 pm UTC

the like is flipping an implicit conversion -- the "=" is not.

to_char() fixes this ambiguity.

A reader, October 20, 2005 - 12:01 am UTC

Is there any way to overcome the 32K limit of execute immediate. I've have a dynamic string, which may exceed 32K. Can I execute strings larger than 32K in PL/SQL ?



Tom Kyte
October 20, 2005 - 7:56 am UTC

dbms_sql has an interface to parse that accepts a plsql table of strings of any size. (The strings are limited in size, but you have as many "lines" as you could possibly need - if you exceed the +- 2g limit - your sql statement is wrong anyway...)

Could you give us an example of dbms_lob.convertToClob?

Kato, December 20, 2005 - 1:23 pm UTC

Hi:

Could you give us an example of dbms_lob.convertToClob?

Thanks.

Kato

Tom Kyte
December 20, 2005 - 2:08 pm UTC

not without a 'cause'. I don't have any data in a blob to convert to text using character set conversion.

That is what the function does - sort of a special case function. Say someone does a fileupload into your database into a blob (using mod_plsql/htmldb)....

You could use this to convert that blob from the us7ascii text (assumption) they uploaded to your databases utf-8 character set, or vice versa, maybe they uploaded a unicode file but you are we8iso...

dbms_lob.convertToClob

Kato, December 20, 2005 - 4:04 pm UTC

I want search string codes into BLOB with a SELECT LIKE, but is not possible. A option is convert BLOB to CLOB, and I tried with this code:

DECLARE
file_blob blob;
file_txt clob;
src_offset INTEGER := 1;
dest_offset INTEGER := 1;
men INTEGER;
lang_ctx INTEGER := dbms_lob.default_lang_ctx;
BEGIN

SELECT BLOB_FILE
INTO file_blob
FROM TAB_BLOB_FILE
WHERE NOM_FILE = 'ARCHIVO.EXE';

SELECT EMPTY_CLOB()
INTO file_txt
FROM DUAL;

dbms_lob.convertToClob(
file_txt,
file_blob,
DBMS_LOB.LOBMAXSIZE,
dest_offset,
src_offset,
DBMS_LOB.default_csid,
lang_ctx,
men);

INSERT INTO TAB_ARCHIVOS_CLOB (nom_file, clob_file)
VALUES
('ARCHIVO.EXE',archivo_txt);

-- then i can select like clob_file

END;

But dbms_lob.convertToClob doesn't work.

I need help with this.

Tanks.

Kato

Tom Kyte
December 20, 2005 - 6:54 pm UTC

what is in archivo.exe - is it in fact *TEXT* or is that an executable (which will necessarily contain things that "are not text in your characterset")

you say 'it does not work' but do not describe 'why'

you can use dbms_lob.instr and search the blob for something like utl_raw.cast_to_raw( 'YOUR_STRING_TO_LOOK_FOR' )

DBMS_LOB.INSTR

Kato, December 21, 2005 - 2:21 pm UTC

You right, I will use DBMS_LOB.INSTR and UTL_RAW.cast_to_raw('TEXT') to search text into BLOB.

Thanks a lot.

Kato

CLOB in EXECUTE IMMEDIATE not working on 9.2.0.7

Karsten Spang, February 15, 2006 - 4:55 am UTC

I just tried the original simple example on 9.2.0.7:

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 15 10:30:21 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL> declare
  2      v_strsql  clob := 'update a set x=1';
  3  begin
  4      execute immediate v_strsql;
  5  end;
  6  /
    execute immediate v_strsql;
                      *
ERROR at line 4:
ORA-06550: line 4, column 23:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

Has the support for CLOB in EXECUTE IMMEDIATE been removed again in 9.2.0.7? 

Tom Kyte
February 15, 2006 - 9:32 am UTC

ops$tkyte@ORA10GR2> create table t ( x clob );
 
Table created.
 
ops$tkyte@ORA10GR2> insert into t values ( 'begin null; end;' );
 
1 row created.
 
ops$tkyte@ORA10GR2> commit;
 
Commit complete.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
  2          l_string clob;
  3  begin
  4      select x into l_string from t;
  5      execute immediate to_char(l_string);
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 

EXECUTE IMMEDIATE ON CLOB

SASIKALA, June 15, 2006 - 1:17 pm UTC

IT WAS VERY GOOD INFO. THAT SOLVED MY ISSUE.

Simple Illustrative Example

Brian, April 24, 2007 - 3:17 pm UTC

Tom's answer to the above question asked on October 20, 2005 ("Is there any way to overcome the 32K limit of execute immediate?") was extremely helpful.

I have a need to exceed the 32K limit as well.

Using Tom's information, I constructed a small example that creates a package spec and body illustrating how it works:

declare

 v_intCur        pls_integer;
 v_intIdx        pls_integer;
 v_intNumRows    pls_integer;
 v_vcStmt        dbms_sql.varchar2a;

begin

 v_intIdx := 1;
 v_vcStmt(v_intIdx) := 'create or replace package pkg_junk is';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := chr(9) || 'procedure prc_junk;';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := 'end pkg_junk;';
 v_intCur := dbms_sql.open_cursor;
 dbms_sql.parse(
  c => v_intCur,
  statement => v_vcStmt,
  lb => 1,
  ub => v_intIdx,
  lfflg => true,
  language_flag => dbms_sql.native);
 v_intNumRows := dbms_sql.execute(v_intCur);
 dbms_sql.close_cursor(v_intCur);
 
 v_intIdx := 1;
 v_vcStmt(v_intIdx) := 'create or replace package body pkg_junk is';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := chr(9) || 'procedure prc_junk is';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := chr(9) || 'begin';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := chr(9) || chr(9) || 'null;';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := chr(9) || 'end prc_junk;';
 v_intIdx := v_intIdx + 1;
 v_vcStmt(v_intIdx) := 'end pkg_junk;';
 v_intCur := dbms_sql.open_cursor;
 dbms_sql.parse(
  c => v_intCur,
  statement => v_vcStmt,
  lb => 1,
  ub => v_intIdx,
  lfflg => true,
  language_flag => dbms_sql.native);
 v_intNumRows := dbms_sql.execute(v_intCur);
 dbms_sql.close_cursor(v_intCur);

end;

Native dynamic sql and CLOBs

EBer, October 23, 2008 - 6:55 am UTC

Hi Tom,

your last response didn't solve the problem (size limit).

If the clob script is >32KB (that's why I use clob) the "to_char" statement fails and returns "numeric or value error".

Regards,
EBer

Tom Kyte
October 23, 2008 - 1:40 pm UTC

we didn't have a size limit when I answered the question, we had a DATATYPE issue - and that I solved.

You - you have an entirely DIFFERENT issue.

you can either

a) use 11g, supports clobs of any length with native dynamic sql
b) use dbms_sql in 10g or before

that is all - there is no other solution.

Still is not working in 10g rel2

Lucian, March 11, 2010 - 5:46 am UTC

I have a table with a CLOB column view_desc, where I stored the DDL for the view (10.2.0.4 version).
When I run:

declare
v_viewStmt varchar2(32000);
begin
select view_desc into v_viewstmt from hh_viewdefs_l where rownum=1;
execute immediate v_viewstmt;
END;
/

I receive:

ORA-00911: invalid character at the line where I have the execute immediate statement

I have strings lower than 32000.
Tom Kyte
March 11, 2010 - 8:33 am UTC

that is because this was a new feature in 11g Release 1 - to support a clob in native dynamic sql.

for any release prior to 11g Release 1, you must use dbms_sql and an array of strings (each of which can be 32k in size) to parse a sql statement larger than 32k.

Using dbms_sql or execute

Thiru, September 23, 2010 - 11:24 am UTC

In 10.2.1, I have a variable currently defined to be varchar2(30000). The value that goes into this variable is now very close to this figure (it is the range-list partition-subpartition statements that run each day for any new values ). The length of this variable is shortly going to be > 32k. I am unable to use clob with execute immediate. Can you guide me to some example as to how to go about ? If dbms_sql is the only way, is there a small simple example how to get the ddl executed .

Thanks a lot.

pl/sql

siri, December 30, 2010 - 10:37 pm UTC

i have the data in a table like this
1 siri
1 rs
1 ram
2 krishan
2 sidhu
3 ramya
3 lakshmi....
i want to concat strings where no=1.if it is 1 then output should be like this (sirirsram).how can i write plsql code for this program.


Tom Kyte
January 03, 2011 - 8:31 am UTC

why would you want to write plsql code - just use sql.

You can either

a) search this site for stragg and once installed:

ops$tkyte%ORA11GR2> l
  1* select deptno, stragg(ename) enames from scott.emp group by deptno
ops$tkyte%ORA11GR2> /

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,MILLER,KING
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD


that works in 9i and above.

b) in 10g and above, you could

ops$tkyte%ORA11GR2> select deptno,
  2         max(sys_connect_by_path(ename,',')) enames
  3    from (
  4  select deptno, ename, row_number() over (partition by deptno order by ename) rn
  5    from scott.emp
  6         )
  7   start with rn = 1
  8  connect by prior deptno = deptno and prior rn+1 = rn
  9   group by deptno
 10  /

    DEPTNO ENAMES
---------- ----------------------------------------
        30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
        20 ,ADAMS,FORD,JONES,SCOTT,SMITH
        10 ,CLARK,KING,MILLER



c) or in 11g and above you would simply:

ops$tkyte%ORA11GR2> select deptno,
  2         listagg( ename, ',' ) within group (order by ename) enames
  3    from scott.emp
  4   group by deptno
  5  /

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

4000 limit

Yane, March 08, 2012 - 7:48 am UTC

Hi Tom

I use oracle11g.
I try to execute :

declare
v_clob clob;
begin
v_clob := to_clob(4800_characterString);
execute immediate v_clob;
end;

I have an error : ORA-01704 : string literal too long
I tryied to convert to 'long' or 'char' but it did not work.
Before, I used 'Varchar2(32000)' instead of 'Clob' and I had the same error.

Can you help me to fix it, please?
Best regards,
Yane.
Tom Kyte
March 09, 2012 - 9:07 am UTC

you shall have to provide an actual example because, even though I tried, I cannot figure out what you might have been doing. These were my attempts:

ops$tkyte%ORA11GR2> declare
  2          v_clob clob;
  3  begin
  4          v_clob := to_clob( 'declare /*
  5  this is a comment, this is a comment, this is a comment, this is a comment, this is a comment
  ...
 57  this is a comment, this is a comment, this is a comment, this is a comment, this is a comment
 58  this is a comment, this is a comment, this is a comment, this is a comment, this is a comment
 59  */
 60                  x number;
 61          begin
 62                  null;
 63          end;' );
 64          dbms_output.put_line( length( v_clob ) );
 65          execute immediate v_clob;
 66  end;
 67  /
5122
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          v_clob clob;
  3          l_string varchar2(5200) := 'declare /*
  4  this is a comment, this is a comment, this is a comment, this is a comment, this is a comment
 ...
 57  this is a comment, this is a comment, this is a comment, this is a comment, this is a comment
 58  */
 59                  x number;
 60          begin
 61                  null;
 62          end;';
 63  begin
 64          v_clob := to_clob( l_string );
 65          dbms_output.put_line( length( v_clob ) );
 66          execute immediate v_clob;
 67  end;
 68  /
5122
PL/SQL procedure successfully completed.

EXECUTE IMMEDIATE of CLOB contains DDL

hakim, April 26, 2012 - 10:17 am UTC

HI tom
I use Oracle 10g.
I want to execute immediate the ddl contains in a clob.(more than one ddl in the clob),I develop a procedure get_clob where I use dbms_lob.substr,to keep one by DDl.
the procedure compile successufully.
but when I run
I have this error:
ORA-06512: à "scema.get_CLOB", ligne 31
00922. 00000 - "missing or invalid option"

thanks for responding.



Tom Kyte
April 26, 2012 - 10:58 am UTC

the ddl you are trying to execute is malformed.


suggest you dbms_output.put_line it and test in sqlplus or something so you can see what ddl you are trying to execute

Interpretation that worked for me

DavidsPilot, April 30, 2012 - 7:56 pm UTC

After reviewing your answers, I was finally able to get this to work for me. I am counting on your usual bluntness to tell me where I interpreted this wrong. I am posting this in hopes it may clairfy parts for other newbies.

--Create a temporary table to hold the sample data inserted.
--  Just for testing not required in final solution.
CREATE TABLE TESTER (COLUMN_1 VARCHAR2(30 BYTE));

--Optional, To Check what SQL Statement was executed,
-- we can copy the data into a table at the end.
CREATE TABLE DYN_SQL (SQL_STMT_CLOB CLOB);


--Create variables to hold sql Statements.
DECLARE
  v_Sql_Stmt_P1   VARCHAR(4000); -- Can be up to ~8 parts (~32,000)
  v_Sql_Stmt_P2   VARCHAR(4000); -- Part 2
  v_Temp_Clob     CLOB;
BEGIN
  --Initialize the Temporary Clob variable to hold dynamic sql statments.
  dbms_lob.createtemporary(
    v_Temp_Clob,
    TRUE);

  --Assign Dynamic sql up to 4000 character chunk into First part variable.
  --rpad is being used just to insert blanks to prove solution
  --works with greater than 4000 characters in the end.
  v_Sql_Stmt_P1      := 'INSERT INTO TEST(COLUMN_1)' ||
                        rpad(
                          ' ',
                          3970,
                          ' ');

  --Assign Dynamic sql up to 4000 char chunk in second part variable.
  v_Sql_Stmt_P2      := 'VALUES(''Hello World'')';

  --Use Oracle Large OBject Function WriteAppend to add first part to Temp Clob.
  dbms_lob.writeAppend(
    v_Temp_Clob,
    Length(v_Sql_Stmt_P1),
    v_Sql_Stmt_P1);

  --Use Oracle Large Object Function WriteAppend to add second part to Temp Clob.
  dbms_lob.writeAppend(
    v_Temp_Clob,
    Length(v_Sql_Stmt_P2),
    v_Sql_Stmt_P2);

  --Execute Complete SQL Stmt.
  EXECUTE IMMEDIATE to_char(v_Temp_Clob);

  --Store Dynamic SQL during testing otherwise not needed.
  INSERT INTO
    DYN_SQL(SQL_STMT_CLOB)
  VALUES
    (v_Temp_Clob);
END;

--To check that the example worked we can check the Test Table.
--If testing over and over delete all data in table before running above just for clarity.
TRUNCATE TABLE TEST;

SELECT * FROM TEST; --Should be 'Hello World'                                                       

--Fancier formated  test...
SELECT
  COLUMN_1,
  (CASE COLUMN_1 WHEN 'Hello World' THEN 'TRUE' ELSE 'FALSE' END) TEST_PASSED
FROM
  TEST;

--To Check WHAT SQL Statment was executed we can copy the data into a table at the end.
--  Remember mistakes are easier because of no syntax checking. 
--  I like to copy results back into the IDE when solving issues.
--If testing over and over delete all data in table before running above just for clarity.
TRUNCATE TABLE DYN_SQL;

SELECT SQL_STMT_CLOB FROM DYN_SQL; --Should be really long sql stmt.

--How long?
SELECT dbms_lob.getlength(SQL_STMT_CLOB) sql_Length FROM DYN_SQL;

--My Version
select * from v$version where banner like 'Oracle%';
--Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


Tom Kyte
May 01, 2012 - 3:37 pm UTC

I don't really know what I'm supposed to be looking for?


I can say I don't like the idea of sql in a table in general...

RE:Implementation that worked for me

DavidsPilot, May 01, 2012 - 7:07 pm UTC

I was hoping you could look for things that were stupid. As you often have many best practice type of advice. However, if nothing jumps out at you, the above is working for me, so no problems found by you is good enough for me. And although your advice was correct it took me a long time to piece together a complete working example. I hoped it may help some other newbie like me.

As for the idea of sql in a table, I agree. I just could not find any other way to output the Dynamic Sql that was greater than 4000 characters. I tried using
DBMS_OUTPUT.PUT_LINE(v_Temp_Clob);
at the end, which did not cause an error per say but did truncate the Dynamic SQL shown to 4000 characters. I also got errors when the Output exceeded 20,000 characters in total as I have a loop over all this not shown in the example above. I know, way too much dynamic SQL, but due to the original design (same table across multiple schemas) of some specialized off the shelf software it was required.

Thus, my final question, do you know a better way for testing purposes, to output these large dynamic SQL strings that does not involve a table?

Tom Kyte
May 02, 2012 - 1:10 pm UTC

<b>ops$tkyte%ORA11GR2> set serveroutput on size unlimited
</b>ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_clob clob := 'select * /* ';
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  l_clob := l_clob || ' comment ' || i;
  7          end loop;
  8          l_clob := l_clob || ' */ from dual';
  9          dbms_output.put_line( l_clob );
 10          dbms_output.put_line( 'length = ' || length(l_clob) );
 11  end;
 12  /
select * /*  comment 1 comment 2 comment 3 comment 4 comment 5 comment 6
comment 7 comment 8 comment 9 comment 10 comment 11 comment 12 comment 13
comment 14 comment 15 comment 16 comment 17 comment 18 comment 19 comment 20
...
comment 982 comment 983 comment 984 comment 985 comment 986 comment 987 comment
988 comment 989 comment 990 comment 991 comment 992 comment 993 comment 994
comment 995 comment 996 comment 997 comment 998 comment 999 comment 1000 */
from dual
length = 11918

PL/SQL procedure successfully completed.




what problem did you have exactly?

RE:Implementation that worked for me

DavidsPilot, May 02, 2012 - 7:03 pm UTC

I get the following error both from my code and yours above:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

Which the SET SERVEROUTPUT ON SIZE UNLIMITED should fix. However, if I add that line I then get the following error:
ORA-00922: missing or invalid option

I am using Toad for Data Analysis when I received the error above, I just tried the same code in Toad for Oracle and it works fine…. Thus it appears that Toad for Data Analysis has a bug or does not support the SET SERVEROUTPUT Statement.

Thanks for your help!

Tom Kyte
May 03, 2012 - 8:45 am UTC

well, here is a small routine I used to use before sqlplus and dbms_output allowed for unlimited line lengths:

create or replace procedure prt ( p_str in varchar2 )
is
   l_str   long := p_str;
begin
   loop
      exit when l_str is null;
      dbms_output.put_line( substr( l_str, 1, 250 ) );
      l_str := substr( l_str, 251 );
   end loop;
end;
/


it'll do up to 32k (you could make it use a clob and do as much as you want)


Is it possible to switch substitution vars on/off from PL/SQL

Badri, July 24, 2013 - 12:22 pm UTC

Hi Tom

Please check the below log,

SQL> set define off serveroutput on
SQL> DECLARE
  2  str varchar2(1000);
  3  BEGIN
  4  str:=str||'set define on serveroutput on'||chr(10);
  5  str:=str||'declare'||chr(10);
  6  str:=str||'str1 varchar2(200);'||chr(10);
  7  str:=str||'begin'||chr(10);
  8  str:=str||'str1:=''&string'';'||chr(10);
  9  str:=str||'dbms_output.put_line(str1);'||chr(10);
 10  str:=str||'end;'||chr(10);
 11  dbms_output.put_line(str);
 12  execute immediate str;
 13  END;
 14  /
set define on serveroutput on
declare
str1 varchar2(200);
begin
str1:='&string';
dbms_output.put_line(str1);
end;

DECLARE
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 12

--trying to execute the block manually and it works fine as below
SQL> set define on serveroutput on
SQL> declare
  2  str1 varchar2(200);
  3  begin
  4  str1:='&string';
  5  dbms_output.put_line(str1);
  6  end;
  7  /
Enter value for string: Hello
old   4: str1:='&string';
new   4: str1:='Hello';
Hello

PL/SQL procedure successfully completed.

SQL>

Request you to kindly help me know the reason for getting ORA-00922 error.

Thanks in advance
Badri

Tom Kyte
July 31, 2013 - 6:55 pm UTC

I don't know what you mean.

you set define off. so, when you set define off the string gets '&string' assigned to it. you try to execute immediate '&string' and it fails.

when you set define on, sqlplus rewrites the code BEFORE it gets to the server (defines are a sqlplus'ism, plsql knows nothing at all about them).

if you want the rewrite to happen, set define on
if you don't set define off


if this doesn't answer your question, please make your question more of a question :)

EXECUTE IMMEDIATE is SQL, not SQL Plus

Mike, August 01, 2013 - 10:44 am UTC

The string you are submitting to SQL starts with 'set define on' which is a SQL Plus command. SQL will not be able to do this.

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

A reader, August 28, 2013 - 3:51 am UTC

could you please help me to resolve this? When I am trying to select a blob value, its returning error like ORA-06502: PL/SQL: numeric or value error: raw variable length too long
I am using the below query

select dbms_lob.substr(columnname,dbms_lob.getlength(columnname),1) from tablename;
Tom Kyte
August 28, 2013 - 7:19 pm UTC

how big is the getlength returning?


need a full example here to work with.


if this is in plsql and getlength returns more than 32k, that won't fly as a raw is only 32k or less in plsql...

if this is in a client program and getlength returns more than 2000 bytes, that won't fly as raw is limited (in 11g and before) to 2000 bytes and varchar2 to 4000 bytes in SQL.


you want to select out the LOB LOCATION. that can be hundreds of gigabytes in size no problem. But the raw type is going to be 2000 or 32k (or just 32k in 12c if you enable long strings - new feature)

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here