DBMS_SQL.PARSE : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_SQL

SQL>
SQL>
SQL> Create or replace Procedure create_dyn_table(i_region_name VARCHAR2,returnCode OUT NUMBER,errmsg OUT VARCHAR2)
  2  authid current_user
  3  is
  4    cur_id INTEGER;
  5    ret_code INTEGER;
  6    sqlString VARCHAR2(1000);
  7    tableName VARCHAR2(21);
  8  Begin
  9    tableName :='ORDERS_FOR_'||i_region_name;
 10
 11    sqlString :='CREATE TABLE '||tableName||
 12    '(order_id NUMBER(10)PRIMARY KEY,total_price NUMBER(15,2))';
 13      cur_id :=DBMS_SQL.OPEN_CURSOR;
 14      DBMS_SQL.PARSE(cur_id,sqlString,DBMS_SQL.V7);
 15      ret_code :=DBMS_SQL.EXECUTE(cur_id);
 16      DBMS_SQL.CLOSE_CURSOR(cur_id);
 17      returnCode :=0;
 18  EXCEPTION WHEN OTHERS THEN
 19    returnCode :=SQLCODE;
 20    errmsg :='ERR:Creating table '||tableName ||'-'||SQLERRM;
 21  End;
 22  /

Procedure created.

SQL>