|
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>
|