Skip to Main Content
  • Questions
  • Max string allowed in EXECUTE IMMEDIATE.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Francisco.

Asked: October 26, 2002 - 11:21 am UTC

Last updated: August 05, 2007 - 10:13 am UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

Hi Tom.

I am trying to pass a string like 2151 characters in length, to the EXECUTE IMMEDIATE command.

Example:

EXECUTE IMMEDIATE 'string with 2151 characters'; /* All the grants from user */

I am trying to replicate the system privileges from a user which was granted with the ALL PRIVILEGES.

SQL> GRANT ALL PRIVILEGES TO FRANK;

There are like 115 privileges granted to this user.

What can I do to replicate these privileges using PL/SQL?

Any advice will be really appreciated.

Best regards.

Francisco Mtz.
Oracle DBA & Developer
OCP DBA 8 & 8i



and Tom said...

A string of upto 32k is allowed (biggest plsql variable string length).

However, in order to grant more then one thing in a single statement, the statement would have to look like:

begin
l_string := 'begin
execute immediate ''grant select on t to scott'';
execute immediate ''grant update on t to scott'';
....
end;';

execute immediate l_string;
end;


I'm guessing you have:

l_string := 'grant select on t to scott;
grant select on x to scott;
.....';

that would be a sqlplus script - not an executable sql statement.


But -- given as I have no example from you of the problem -- thats just a guess.


Rating

  (19 ratings)

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

Comments

It is that I am doing.

Francisco Martinez, October 26, 2002 - 1:57 pm UTC

Thanks a lot Tom.

My code is as follow:

CREATE OR REPLACE PROCEDURE REPLICATE_SQL_STATEMENT (pDatabase IN VARCHAR2, pSQL_statement IN VARCHAR2) IS
vSQL_statement VARCHAR2(3000);
BEGIN
IF pSQL_statement IS NOT NULL
THEN vSQL_statement := 'BEGIN ARBOR.Execute_SQL_Statement@'||pDatabase||'('''||pSQL_statement||'''); END;';
EXECUTE IMMEDIATE vSQL_statement;
END IF;
END;
/



CREATE OR REPLACE FUNCTION GET_SYS_PRIVS_STMT (pDatabase IN VARCHAR2, pUsername IN VARCHAR2, pEvent IN VARCHAR2, pAdmin IN VARCHAR2) RETURN VARCHAR2 IS

vSQL_statement VARCHAR2(3000);

vSysprivs_statement VARCHAR2(1000);
TYPE EmpCurTyp IS REF CURSOR;
cSysprivs EmpCurTyp;
vSyspriv VARCHAR2(100);
vSyspriv_list VARCHAR2(1000);

BEGIN
IF (pAdmin = 'ADMIN') OR (pAdmin = 'NOADMIN')
THEN IF pEvent = 'GRANT'
THEN vSysprivs_statement := 'SELECT A.PRIVILEGE'||
' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'||
' WHERE B.GRANTEE (+) = A.GRANTEE'||
' AND B.PRIVILEGE (+) = A.PRIVILEGE'||
' AND B.GRANTEE IS NULL'||
' AND B.PRIVILEGE IS NULL'||
' AND A.GRANTEE = '''||pUsername||''''||
' AND A.ADMIN_OPTION = DECODE('''||pAdmin||''', ''ADMIN'', ''YES'', ''NO'')';
OPEN cSysprivs FOR vSysprivs_statement;
LOOP
FETCH cSysprivs INTO vSyspriv;
EXIT WHEN cSysprivs%NOTFOUND;
SELECT vSyspriv_list||DECODE(vSyspriv_list, NULL, NULL, ', ')||vSyspriv INTO vSyspriv_list FROM DUAL;
END LOOP;
SELECT DECODE( vSyspriv_list, NULL, NULL, 'GRANT '||vSyspriv_list||' TO '||pUsername||DECODE(pAdmin, 'ADMIN', ' WITH ADMIN OPTION', NULL)) INTO vSQL_statement FROM DUAL;
ELSIF pEvent = 'REVOKE'
THEN vSysprivs_statement := 'SELECT B.PRIVILEGE'||
' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'||
' WHERE B.GRANTEE = A.GRANTEE (+)'||
' AND B.PRIVILEGE = A.PRIVILEGE (+)'||
' AND B.GRANTEE = '''||pUsername||''''||
' AND B.ADMIN_OPTION = DECODE('''||pAdmin||''', ''ADMIN'', ''YES'', ''NO'')'||
' AND A.GRANTEE IS NULL'||
' AND A.PRIVILEGE IS NULL'||
' UNION '||
'SELECT A.PRIVILEGE'||
' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'||
' WHERE B.GRANTEE = A.GRANTEE'||
' AND B.PRIVILEGE = A.PRIVILEGE'||
' AND B.ADMIN_OPTION <> A.ADMIN_OPTION'||
' AND A.GRANTEE = '''||pUsername||'''';
OPEN cSysprivs FOR vSysprivs_statement;
LOOP
FETCH cSysprivs INTO vSyspriv;
EXIT WHEN cSysprivs%NOTFOUND;
SELECT vSyspriv_list||DECODE(vSyspriv_list, NULL, NULL, ', ')||vSyspriv INTO vSyspriv_list FROM DUAL;
END LOOP;
SELECT DECODE(vSyspriv_list, NULL, NULL, 'REVOKE '||vSyspriv_list||' FROM '||pUsername) INTO vSQL_statement FROM DUAL;
ELSE RAISE_APPLICATION_ERROR(-20101, 'ERROR: pEvent parameter value is invalid in procedure Get_Role_Privs_Stmt.');
END IF;
ELSE RAISE_APPLICATION_ERROR(-20101, 'ERROR: pAdmin parameter value is invalid in procedure Get_Role_Privs_Stmt.');
END IF;
RETURN (vSQL_statement);
END;
/

I have to exclude some of privileges like "ADMINISTER RESOURCE MANAGER", "DEQUEUE ANY QUEUE", "ENQUEUE ANY QUEUE" and "MANAGE ANY QUEUE", which are granted with packages.

Note: I haven't modified my function(GET_SYS_PRIVS_STMT) with this conditions.

The REPLICATE_SQL_STATEMENT is called from an other main procedure.

The returned value from the GET_SYS_PRIVS function is passed to the REPLICATE_SQL_STATEMENT, which is executed in the remote database.

I get from my function a tring like this one:

GRANT DROP ANY TABLE, EXECUTE ANY PROCEDURE, ... until 115 privileges.

I have just tested this function and it is working, returning me all the 115 privileges without any error.

But the problem is when the string is sent to the EXECUTE IMMEDIATE command in the remote database.

Any advice will be really appreciated.

Sincerely I appreciate your valuable help.

Best regards.


Tom Kyte
October 26, 2002 - 2:05 pm UTC

well, seeing as how DDL over dblinks isn't a permitted operation -- that could have something to do with it.

You'll want to use dbms_job@remote site to schedule a stored procedure to execute after you commit -- so the ddl is a local transaction.

And - you always want to include error messages and codes!

Also -- please use bind variables -- it is easier to use binds then NOT use binds with execute immediate!!

execute immediate 'begin procedure( :x ); end;' using some_string;



Max string length in execute immediate

Ray White, March 05, 2003 - 3:35 pm UTC

Please excuse tacking on this question but ...
You have indicated that the max string length for execute immediate is 32K.

We are using execute immediate to create generated packages and we are currently passing it > 35000 chars

by execute immediate v_myvc1 || my_vc2

vc1 and vc2 are 32 k varchar2 vars.
whose combined length is currently > 35000

All on 8.1.7

My Question is what is the maximum length for the execute immediate string cause I was worried it was 32k and we are already over it, and I'm not sure when I'm going to hit the wall.

Tom Kyte
March 05, 2003 - 6:58 pm UTC

interesting -- never would have thought to do it that way.

That appears to work -- will it hit a wall?  not sure, I would never have gone over 32k.

looks like it can go pretty large:

ops$tkyte@ORA817DEV> declare
  2          l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * ';
  3          l_str2 long := 'from /* '   || rpad( '*', 15000, '*' ) || ' */ dual';
  4          l_str3 long := '/* '   || rpad( '*', 32000, '*' ) || ' */ ';
  5          l_result dual.dummy%type;
  6  begin
  7          execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result;
  8          dbms_output.put_line( l_result );
  9  end;
 10  /
X

PL/SQL procedure successfully completed.

 

Interesting

trevor, March 05, 2003 - 7:16 pm UTC

Never had to go >32k but
interesting that this works..

For no particular reason....

Mike Jones, March 06, 2003 - 7:51 am UTC

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production on HP-UX seems to accept an sql statement of 143,799 charecters

While running Personal Oracle9i Release 9.2.0.1.0 - Production on a ropey Win2K laptop only gets up to 65,535 charecters

I hazard a guess that it's either platform dependent or has been shrunk to a sensible length in 9i (or is a diff between Personal and Enterprise Oracle, or is a Unix / Win thing or is a ... )

Why anyone would want to execute 143K sql statement is beyond me though...

Tom Kyte
March 06, 2003 - 8:12 am UTC

I would suggest this:

o keep it below 32k for execute immediate
o use DBMS_SQL to parse anything over 32k as it supports a documented "pass me
a sql statement of any length in an array, a line at a time and I'll do it for
you"


I would consider it unreliable to use execute immediate with more then 32k.

Max Length of execute immediate

Ray White, March 06, 2003 - 5:38 pm UTC

Thanks for the feedback
Great site.

Great

Kalyan A Samaddar, November 19, 2003 - 3:49 am UTC

So far I was thinking that size is the only difference between EXECUTE IMMEDIATE and DBMS_SQL. Now it's pretty clear that size of the string is not a limitation of EXECUTE IMMEDIATE in any practical situation.

Is there any difference between EXECUTE IMMEDIATE and DBMS_SQL then ?

Tom Kyte
November 21, 2003 - 11:25 am UTC

dbms_sql can

a) parse once
b) execute over and over and over

execute immediate cannot do that. it parse/executes each time.

How to return/refer values in/from block of statements?

Tony, March 25, 2004 - 1:04 am UTC

I use EXECUTE IMMEDIATE to execute block of statments
( DECLARE ... BEGIN ..... END). At the end of execution, I wanna refer/return values of some of the local variables in the block executed. Could you please let me know how to achieve this?



Tom Kyte
March 25, 2004 - 9:12 am UTC

use bind variables -- fully documented in the PLSQL guide, you can bind IN, IN OUT, or OUT parameters to dynamic blocks.

More help

Sanjaya Balasuriya, April 29, 2004 - 6:36 am UTC

Hi Tom,

I'm tryig to create a procedure for stats collection;

stmt varchar2(200);

BEGIN
for x in (select distinct owner
from sys.dba_objects)
loop
stmt := 'dbms_stats.gather_schema_stats('||''''||x.owner||''''||');';

begin
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
END; -- Procedure


But I run this procedure, it says invalid statement.

ORA-00900

But when I run generated statements separately, works.

Where is the problem ?


Tom Kyte
April 29, 2004 - 8:19 am UTC

dbms_stats is plsql. just call it


BEGIN
for x in (select distinct owner
from sys.dba_objects)
loop
dbms_stats.gather_schema_stats(x.owner);
end loop;
END;
/


Never use a when others like that, horrible! you just want to silently ignore errors??? never.

Thanks

Sanjaya Balasuriya, April 29, 2004 - 11:25 am UTC

Thanks a lot.

I'll try to avoid using when others :)



"passing a sql statement within an Array

Keith Jamieson, June 01, 2004 - 7:54 am UTC

Hi Tom,

I have a sample procedure below which uses dbms_sql. My insert statement is huge, and is well over the 32K limit.

Can you please supply an example of how to pass the sql statement within an array.

CREATE OR REPLACE PROCEDURE DBMS_SQL_TEST
AS
l_cursor integer;
l_status integer;
l_statement varchar2(4000);
l_min_emp_id integer := 0;
l_increment integer := 5000;
BEGIN

l_statement := 'insert into emp(emp_id)' ||
'select policy_id from emp ' ||
'WHERE emp_id >= :BV1 ' ||
'and emp_id < :BV2';
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse (l_cursor,
l_statement,
dbms_sql.native);

while l_min_emp_id < 100000 + l_increment
loop
dbms_sql.bind_variable(l_cursor,
':bv1', l_min_emp_id);

dbms_sql.bind_variable(l_cursor,
':bv2',
l_min_emp_id + l_increment);

l_status := dbms_sql.execute(l_cursor);
l_min_emp_id := l_min_emp_id + l_increment;
end loop;
commit;
END DBMS_SQL_TEST;



Tom Kyte
June 01, 2004 - 8:59 am UTC

your insert is over 32k? how did you do that?


anyway, just fill up an array and parse it (but ask yourself, why..... seems pretty large no?)

declare
l_stmt dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
begin
l_stmt(1) := 'create';
l_stmt(2) := 'table';
l_stmt(3) := 'foo ( x int primary key )';

dbms_sql.parse( c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE,
language_flag => dbms_sql.native );
l_rows := dbms_sql.execute(l_cursor);



dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end;
/


works in 8i not in 9i

Ashiq Shamsudeen A, June 01, 2004 - 9:31 am UTC

mars@MARS> sho rel
release 801070000
mars@MARS> declare
2 l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * ';
3 l_str2 long := 'from /* ' || rpad( '*', 15000, '*' ) || ' */ dual';
4 l_str3 long := '/* ' || rpad( '*', 32000, '*' ) || ' */ ';
5 l_result dual.dummy%type;
6 begin
7 execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result;
8 dbms_output.put_line( l_result );
9 end;
10 /
X

PL/SQL procedure successfully completed.

mars@MARS>

But in Oracle9i it doesn't

scott@SHAM> sho rel
release 902000100
scott@SHAM> declare
2 l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * ';
3 l_str2 long := 'from /* ' || rpad( '*', 15000, '*' ) || ' */ dual';
4 l_str3 long := '/* ' || rpad( '*', 32000, '*' ) || ' */ ';
5 l_result dual.dummy%type;
6 begin
7 execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result;
8 dbms_output.put_line( l_result );
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7


scott@SHAM>

What's the problem ?



Tom Kyte
June 01, 2004 - 11:50 am UTC

it should never have worked, that it did was an accident.

strings are 32k in plsql.



Thanks, just what I needed.

Keith Jamieson, June 01, 2004 - 9:41 am UTC

I assume that the iteration from the first row to the last row within the plql table is handled within the procedure. I also note that each string is limited to 256 characters (on 8i anyway) when using this approach.

There are lots of reasons for this long insert statement, eg a 700+ column table, and here I prefer one large insert rather than a smaller insert followed by lots of updates.

ie perform one sql statement rather than several.

Further information

Keith Jamieson, June 01, 2004 - 12:51 pm UTC

I ran a test on oracle 8i, and this worked perfectly. I then changed some of the array numbers within the pl/sql table so that they were still increasing (my gaps were in the where clause) , but had gaps, and I came up with the no_data_found error.

If this is a true pl/sql table then I would expect to be able to have gaps in the numbers.
I suspect that the code just adds 1 to the previous number rather than use the plsql function that finds the next element in the table.



Tom Kyte
June 01, 2004 - 3:38 pm UTC

it is assuming a contigous array with indices from first..last in order.

Execute Immediate

Dinesh Kumar Dubey, July 20, 2005 - 10:11 am UTC

Yes i got to know that execute immediate will have probls if it is too long.

How to execute a long SQL which is in a .sql file

A reader, July 31, 2007 - 1:51 pm UTC


Hello Tom---I have a unique problem here.We have two .SQL files.We need to perform a condition checks and if true then execute the statements in one file else run the second file.

In brief:

create procedure test
begin
if a=TRUE
then execute file1
else
execute file2


I read that execute statement has 32K limitation.So I tried with "@/file1" inside the procedure, but got errors.

Is there a way to run the statements in the files using a procedure?

Thanks for your time and service.






Tom Kyte
August 02, 2007 - 10:10 am UTC

sqlplus runs scripts.

stored procedures run in the server - heck, they might not even have access to the scripts (the scripts reside on the client, plsql runs in the server)

sqlplus needs to do this.

You can use a technique like this:

variable filename varchar2(20)

begin
        if (1=1)
        then
                :filename := 'file1';
        else
                :filename := 'file2';
        end if;
end;
/

column filename new_val F
select :filename filename from dual;
@&F


for example:

ops$tkyte%ORA10GR2> host echo "prompt file one at your service" > file1.sql

ops$tkyte%ORA10GR2> host echo "prompt file two at your service" > file2.sql

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable filename varchar2(20)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          if (1=1)
  3          then
  4                  :filename := 'file1';
  5          else
  6                  :filename := 'file2';
  7          end if;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column filename new_val F
ops$tkyte%ORA10GR2> select :filename filename from dual;

FILENAME
--------------------------------
file1

ops$tkyte%ORA10GR2> @&F
ops$tkyte%ORA10GR2> prompt file one at your service
file one at your service
ops$tkyte%ORA10GR2>

Please save Oracle from comments

A reader, August 01, 2007 - 5:16 pm UTC

Tom--Please suggest.Your tricks are needed here.
My developer says Oracle has lot of commands but nothing useful to accomplish simple tasks like this:

DECLARE  flag number ;

BEGIN

select count(*) into test from product where product_name ='ABC';
if test = 0 then
 @.\DepViews\c_test.sql;
 @.\DepClasses\c_test_config.sql;
 @.\RepViews\R_test.sql;
end if;
end


I tried to use execute immediate, start, run and @....
But got errors.We need to ship this to customers as
.SQL file.

Is there any other way to accomplish this task?
Please save oracle from comments.....
Tom Kyte
August 05, 2007 - 10:13 am UTC

did you read my answer from days ago above???

it, well, shows exactly how to accomplish this.

A reader, August 01, 2007 - 5:17 pm UTC

Oops...my mistake

DECLARE test number ;
BEGIN

select count(*) into test from product where product_name ='ABC';
if test = 0 then
@.\DepViews\c_test.sql;
@.\DepClasses\c_test_config.sql;
@.\RepViews\R_test.sql;
end if;
end

A reader, August 09, 2007 - 3:36 pm UTC

Excellent solution from a master brain.I know i would get it ony from you.

Thanks Tom.

awesome!!

sam, August 30, 2007 - 11:22 am UTC

great piece of code...i feel so stupid...had used utl package to get the proc in a local variable and execute!!!:(