How to execute ddl statement in Oracle 8x using dbms_utility.exec_ddl_statement

exec_ddl_statement is to execute DDL statements.

This is a procedure in Oracle built-in called dbms_utility. EXECUTE_IMMEDIATE is another command that you can use to execute DDL statements in an Oracle database from PL/SQL.

Precedure signature:

procedure exec_ddl_statement(parse_string in varchar2);

The command is passed as a variable which is then executed by the package.

The example below shows – using dbms_utility package how to create a table and an index on that table from an anonymous pl/sql block:

Example:

declare
l_cnt number;
begin
– create a table called emp
dbms_utility.exec_ddl_statement(‘create table emp ( empno number, ename varchar2(10), age number)’);

– create index if the table is there
dbms_utility.exec_ddl_statement(‘create index emp_ind1 on emp (empno)’);
end;

Notice that there is no semi colon after the the command – i.e. create table emp ( empno number, ename varchar2(10), age number) .It is because dbms_utility package will automatically put a semicolon for you while it runs this command. If you put a semicolon then it will not work.

Learn more on dbms_utility in action:



Loading