Dynamic DDL : 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_table
  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 :='ORDERS_FOR_'||replace(trim(i_region_name),'','_');
 11    sqlString :='CREATE TABLE '||tableName||
 12    '(order_id NUMBER(10)PRIMARY KEY,
 13      sale_date DATE NOT NULL,
 14      total_number NUMBER,
 15      total_price NUMBER(15,2),
 16      company_id NUMBER(6))';
 17    EXECUTE IMMEDIATE sqlString ;
 18    returnCode :=0;
 19  EXCEPTION WHEN OTHERS THEN
 20    returnCode :=SQLCODE;
 21    errorMessage :='ERR:Creating table '||tableName ||'-'||SQLERRM;
 22  End;
 23  /

Procedure created.

SQL>
SQL>
SQL>