DDL COMMANDS USED IN PL/SQL BLOCKS!POSSIBLE!

Hi !

Working on Oracle v8i with OS as Win2000.

!Could we use DDL commands in PL/SQL Blocks like in a Procedure as:

EXECUTE IMMEDIATE ALTER TRIGGER TRIG_NAME DISABLE;
OR
EXEC DBMS_SQL PACKAGE.

Could I have the syntax for this and what procedure/function are to be used with the above package.

Thanks & Regards,

Amit.

You've almost got it right yourself:

EXECUTE IMMEDIATE 'ALTER TRIGGER TRIG_NAME DISABLE';

Just remember that EXECUTE IMMEDIATE requires the command to be a string.

This can be put into a stored procedure like:

CREATE OR REPLACE PROCEDURE ddl_command (p_cmd IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_cmd;
END;
/

The procedure owners priviledges will dictate which commands it can perform.

Cheers