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.
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:
– 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)’);
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:
- How to get database version info using Oracle dbms_utility package
- How to find Oracle database object dependency using dbms_utility package
- How to use dbms_utility.analyze_schema to Analyze all objects in a schema
- How to get current time using dbms_utility package
- How to use dbms_utility.analyze_database to analyze Oracle database