illustrates a DDL statement which is built dynamically from the procedure parameters. : dbms_sql « System Packages « Oracle PL / SQL






  1. Oracle PL / SQL
  2. System Packages
  3. dbms_sql

illustrates a DDL statement which is built dynamically from the procedure parameters.

  
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicSequence(
  2    p_SequenceName IN VARCHAR2,
  3    p_IncrementBy IN NUMBER := NULL,
  4    p_StartWith IN NUMBER := NULL,
  5    p_MaxValue IN NUMBER := NULL,
  6    p_MinValue IN NUMBER := NULL,
  7    p_Cycle IN VARCHAR2 := NULL,
  8    p_Cache IN NUMBER := NULL) AS
  9
 10    v_CreateStmt VARCHAR2(200);
 11    v_CursorID INTEGER;
 12  BEGIN
 13    v_CreateStmt := 'CREATE SEQUENCE ' || p_SequenceName;
 14
 15    IF p_IncrementBy IS NOT NULL THEN
 16      v_CreateStmt :=
 17        v_CreateStmt || ' INCREMENT BY ' || p_IncrementBy;
 18    END IF;
 19
 20    IF p_StartWith IS NOT NULL THEN
 21      v_CreateStmt :=
 22        v_CreateStmt || ' START WITH ' || p_StartWith;
 23    END IF;
 24
 25    IF p_MaxValue IS NOT NULL THEN
 26      v_CreateStmt :=
 27        v_CreateStmt || ' MAXVALUE ' || p_MaxValue;
 28    END IF;
 29
 30    IF p_MinValue IS NOT NULL THEN
 31      v_CreateStmt :=
 32        v_CreateStmt || ' MINVALUE ' || p_MinValue;
 33    END IF;
 34
 35    IF p_Cycle IS NOT NULL THEN
 36      v_CreateStmt :=
 37        v_CreateStmt || ' ' || p_Cycle || ' ';
 38    END IF;
 39
 40    IF p_Cache IS NOT NULL THEN
 41      v_CreateStmt :=
 42        v_CreateStmt || ' CACHE ' || p_Cache;
 43    END IF;
 44
 45    -- And now execute it.
 46    v_CursorID := DBMS_SQL.OPEN_CURSOR;
 47    DBMS_SQL.PARSE(v_CursorID, v_CreateStmt, DBMS_SQL.NATIVE);
 48  END DynamicSequence;
 49  /

Procedure created.

SQL>
SQL>
SQL>








Related examples in the same category

1.First DBMS_SQL Example
2.Pass a query statement to a stored procedure
3.Use dbms_sql.open_cursor create a cursor
4.Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.Use dbms_sql.bind_array to bind array to a cursor
6.Use dbms_sql.parse to bind a select statement to a cursor
7.Call dbms_sql.describe_columns2 to get info for a column
8.Non-Query DML and DDL Statements
9.Use DBMS_SQL with the RETURNING clause.
10.DBMS_SQL.NATIVE;
11.DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.Dynamic sql statement with variable binding
13.Dynamic select statement and row fetch
14.Execute PL/SQL block
15.DBMS_SQL package: dynamic SQL
16.Pass where clause to a stored procedure
17.Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
18.Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
19.drop the supplied table using dynamic SQL.
20.DBMS_SQL.BIND_VARIABLE_CHAR
21.illustrate the importance of setting out_value_size.
22.Calling a function which uses dynamic SQL from within an SQL statement.
23.Illustrates the interaction of roles and dynamic SQL.
24.Use dbms_sql to process query, cursor and value
25.Use dbms_sql.bind_variable to bind variable
26.dbms_sql.varchar2_table type variable