Create dynamic table : Dynamic SQL statement « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Dynamic SQL statement

SQL> Create or replace Procedure create_dyn_table2
  2                  (i_region_name VARCHAR2,
  3                   returnCode OUT NUMBER,
  4                   errorMessage OUT VARCHAR2)
  5  authid current_user
  6  Is
  7    sqlString VARCHAR2(1000);
  8    tableName VARCHAR2(30);
  9  Begin
 10    tableName :='ORDER_ITEMS_FOR_'||replace(trim(i_region_name),'','_');
 11    sqlString :='CREATE TABLE '||tableName||
 12    '(order_id NUMBER(10)NOT NULL,
 13      item_id VARCHAR2(10)NOT NULL,
 14      unit_price NUMBER(11,2)NOT NULL,
 15      quantity NUMBER)';
 16
 17    EXECUTE IMMEDIATE sqlString ;
 18
 19    returnCode :=0;
 20
 21  EXCEPTION WHEN OTHERS THEN
 22
 23    returnCode :=SQLCODE;
 24
 25    errorMessage :='ERR:Creating/Altering table '||tableName ||'-'||SQLERRM;
 26  End;
 27  /

Procedure created.

SQL>
SQL>
SQL>