You Asked

Hi Tom

I was wondering if it's possible use bind variables with execute immediate and DDL

such as

begin
for i in (select * from dba_segments where owner='LSC'
and (segment_type='INDEX' or segment_name='EMP'))
loop
if i.segment_type = 'INDEX'
then
dbms_output.put_line(i.segment_name);
execute immediate 'alter index '||:seg||' rebuild tablespace tools' using i.segment_name;
else
dbms_output.put_line(i.segment_name);
execute immediate 'alter table '||:seg||' move tablespace users' using i.segment_name;
end if;
end loop;
exception
when others
then
dbms_output.put_line(sqlerrm);
end;
/

This doesnt work, but is it possible to acomplish this?

Cheers

and Tom said...

you cannot use bind variables with DDL -- there is nothing "replaceable" in DDL. For example, the series of steps needed to perform:

alter table T move tablespace users

is TOTALLY different then

alter table EMP move tablespace users

In fact, you'll never see DDL in the shared pool for the simple reason that you cannot really share it!

You just use concatenation here.

Rating

(2 ratings)

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

Comments

Thank you as usual

ls cheng, December 23, 2001 - 7:26 pm UTC

Thank you Tom for answering my question on Sunday!

A reader, January 10, 2003 - 3:09 pm UTC

Can you help me on this please? Why is this procedure generationg error? It is compiled and run as a user with DBA privilage. I am getting no clue what is going on here!!

Thanks for all your help.

SQL> ed
Wrote file afiedt.buf

line 17 truncated.
  1  create or replace procedure
  2   pr_create_user( p_user in varchar2,
  3                 p_passwd in varchar2 )
  4  as
  5   st varchar2(100);
  6  begin
  7  st:='create user '||p_user||' identified by '||p_passwd||'
  8    default tablespace users temporary tablespace temp';
  9  dbms_output.put_line(st);
 10  execute immediate st;
 11  -- execute immediate 'grant connect to '||p_user;
 12  dbms_output.put_line(p_user||' Created.');
 13  exception
 14   when others then
 15    dbms_output.put_line(sqlerrm);
 16* end;
 17  /

Procedure created.

SQL> exec pr_create_user('abcd', 'abcd');
create user abcd identified by abcd
  default tablespace users temporary
tablespace temp
ORA-01031: insufficient privileges

PL/SQL procedure successfully completed.

SQL> create user abcd identified by abcd
  2    default tablespace users temporary
  3  tablespace temp
  4  /

User created.

SQL> drop user abcd;

User dropped.

January 10, 2003 - 3:49 pm UTC

read

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thank you as usual

More to Explore

DBMS_OUTPUT

bind variables in DDL with execute immediate

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thank you as usual

More to Explore

DBMS_OUTPUT