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






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>








31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query