Mega Code Archive
Illustrates a DDL statement which is built dynamically from the procedure parameters
<![CDATA[
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>
]]>