CAN I USE DDL STATEMENTS IN STORED PROCEDURE

[2314]

CAN I USE DDL STATEMENTS IN STORED PROCEDURE

 

[SantaMufasa]

2314,

Following is a session that illustrates DDL in a stored procedure:

create or replace procedure droptab (tabname varchar2) is
begin
    execute immediate 'drop table '||tabname;
end;
/

Procedure created.

desc yada

 Name              Null?    Type
 ----------------- -------- ------------
 TXT                        VARCHAR2(10)

exec droptab('yada')

PL/SQL procedure successfully completed.

desc yada
ERROR:
ORA-04043: object yada does not exist

Does this provide the information you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:10 (22Nov04) UTC (aka "GMT" and "Zulu"),
@ 00:10 (22Nov04) Mountain Time

 

[2314]

hI,
WHEN I TRY TO EXECUTE IT GIVE ME INSUFFICENT PRIVELIGES ERROR EVEN THROUGH I CAN CREATE TABLE PRIVELIGE.

 

[SantaMufasa]

2314,

My crystal ball is especially fuzzy late on Sunday night. Could you please post the results of a "describe <table>" with which you are trying to work and also post the actual PL/SQL code you are trying to execute, along with the resulting error message?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:30 (22Nov04) UTC (aka "GMT" and "Zulu"),
@ 00:30 (22Nov04) Mountain Time