Using DDL (CREATE, DROP, ALTER) statement in Procedure (PL/SQL) in Oracle.

Purpose: error will occur if you simply write “Create table” or “Drop table” command in simple procedure program.

Solution:

Oracle allows you to write DDL commands in PL/SQL using “Execute Immediate”.

Example:

CREATE OR REPLACE PROCEDURE XX_TEST_P

AS

BEGIN

EXECUTE IMMEDIATE

'DROP TABLE XX_TEST';

EXECUTE IMMEDIATE

'CREATE TABLE XX_TEST(ID NUMBER,

NAME VARCHAR2(4000))';

END;