Monday, August 15, 2011

How can I execute DDL statement from PL/SQL block

Scenario:
What happens when following code is executed?
BEGIN

CREATE TABLE EMPLOYEE
(EMP_NO NUMBER(10),
EMP_NAME VARCHAR2(30));
END;

Result:
A parsing error will occur. The above block is trying to execute a DDL statement which is not allowed in PL/SQL.

Solution:

Example 1:

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE EMPLOYEE
(EMP_NO NUMBER(10),
EMP_NAME VARCHAR2(30))';
END;

anonymous block completed.

>>Here table is succesfully created.

Example 2:

BEGIN
EXECUTE IMMEDIATE 'truncate table tablename';
END;
anonymous block completed.

>>table is successfully truncated.

No comments:

Post a Comment