1 PACKAGE BODY cct_check_seq_pkg AS
2 /* $Header: cctcksqb.pls 120.0.12010000.1 2008/07/25 23:41:46 appldev ship $ */
3
4
5 -- Check whether the current value of the sequence number is less than the max of
6 -- the column from the table.
7 -- If the sequence number is bigger, then do nothing.
8 -- Otherwise, recreate the sequence with the max of the column from the table
9
10 PROCEDURE check_sequence (
11 table_name IN VARCHAR2,
12 column_name IN VARCHAR2,
13 sequence_name IN VARCHAR2,
14 cct_schema IN VARCHAR2
15 ) IS
16
17 sequence_not_found exception;
18 pragma exception_init(sequence_not_found, -942);
19 l_missing_sequence VARCHAR2(2000);
20
21 v_cursorID INTEGER;
22 v_seq_stmt VARCHAR2(2000);
23 l_seq_val VARCHAR2(10);
24 v_dummy INTEGER;
25
26 v_cursorID1 INTEGER;
27 v_max_stmt VARCHAR2(2000);
28 l_max_val VARCHAR2(10);
29 v_dummy1 INTEGER;
30
31 v_create_tbl_stmt VARCHAR2(2000);
32 v_drop_tbl_stmt VARCHAR2(2000);
33 v_inc_stmt VARCHAR2(2000);
34 v_ins_stmt VARCHAR2(2000);
35
36 v_new_begin INTEGER;
37 diff INTEGER;
38 l_num NUMBER;
39
40 BEGIN
41 SAVEPOINT CCTCKSEQ;
42
43 -- Getting the current Sequence value
44 BEGIN
45
46 v_cursorID:=DBMS_SQL.OPEN_CURSOR;
47 v_seq_stmt:='SELECT '||cct_schema||'.'||sequence_name||'.NEXTVAL from dual';
48 --dbms_output.put_line(v_seq_stmt);
49 DBMS_SQL.PARSE(v_cursorID,v_seq_stmt,DBMS_SQL.V7);
50 --dbms_output.put_line('after parsing');
51 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,l_seq_val,10);
52 --dbms_output.put_line('after define column');
53 v_dummy:=DBMS_SQL.EXECUTE(v_cursorID);
54 --dbms_output.put_line('after execute 1');
55 Loop
56 if DBMS_SQL.FETCH_ROWS(v_cursorID)=0 THEN
57 --dbms_output.put_line('exiting');
58 exit;
59 end if;
60 --dbms_output.put_line('before col valu');
61 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,l_seq_val);
62 --dbms_output.put_line(l_seq_val);
63 END LOOP;
64 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
65 Exception
66 When others then
67 l_missing_sequence:=sequence_name;
68 raise sequence_not_found;
69 end;
70
71 -- Getting the max column value from the table
72 BEGIN
73 v_cursorID1:=DBMS_SQL.OPEN_CURSOR;
74 v_max_stmt:='SELECT max('||column_name||') from '||cct_schema||'.'||table_name;
75 --dbms_output.put_line(v_max_stmt);
76 DBMS_SQL.PARSE(v_cursorID1,v_max_stmt,DBMS_SQL.V7);
77 --dbms_output.put_line('after parsing');
78 DBMS_SQL.DEFINE_COLUMN(v_cursorID1,1,l_max_val,10);
79 --dbms_output.put_line('after define column');
80 v_dummy1:=DBMS_SQL.EXECUTE(v_cursorID1);
81 --dbms_output.put_line('after execute 2');
82 Loop
83 if DBMS_SQL.FETCH_ROWS(v_cursorID1)=0 THEN
84 --dbms_output.put_line('exiting');
85 exit;
86 end if;
87 --dbms_output.put_line('before col valu');
88 DBMS_SQL.COLUMN_VALUE(v_cursorID1,1,l_max_val);
89 --dbms_output.put_line(l_max_val);
90 END LOOP;
91 DBMS_SQL.CLOSE_CURSOR(v_cursorID1);
92 Exception
93 When others then
94 raise_application_error(-20000, 'Column '||table_name||':'||column_name||' SQLERRM::'||sqlerrm || '. Could not get the max value.');
95 end;
96
97 -- if the sequence value is less than the max column value then bump up the sequence to
98 -- max column value, otherwise do nothing
99 -- dbms_output.put_line('Sequence='||l_seq_val||', max column='||l_max_val);
100
101 if (to_number(l_seq_val) <= to_number(l_max_val) )
102 then
103 begin
104
105 v_new_begin := to_number(l_max_val)+1;
106 diff := to_number(l_max_val) - to_number(l_seq_val) + 1;
107 --dbms_output.put_line('diff is '||diff);
108 end;
109
110 begin
111 v_inc_stmt:='alter SEQUENCE '||cct_schema||'.'||sequence_name||' INCREMENT BY '||diff;
112 --dbms_output.put_line('Increase Sequence stmt='||v_inc_stmt);
113 EXECUTE IMMEDIATE v_inc_stmt;
114
115 EXECUTE IMMEDIATE 'select ' || cct_schema||'.'||sequence_name || '.nextval from dual where rownum=1 ' INTO l_num;
116 -- dbms_output.put_line('Increase Sequence value='||l_num);
117 v_inc_stmt:='alter SEQUENCE '||cct_schema||'.'||sequence_name||' INCREMENT BY 1';
118 --dbms_output.put_line('Increase Sequence stmt='||v_inc_stmt);
119 EXECUTE IMMEDIATE v_inc_stmt;
120
121 Exception
122 When others then
123 l_missing_sequence:=sequence_name;
124 raise sequence_not_found;
125 end;
126
127 END IF;
128
129 COMMIT WORK;
130
131 EXCEPTION
132 WHEN sequence_not_found THEN
133 ROLLBACK TO SAVEPOINT CCTCKSEQ;
134 raise_application_error(-20000, 'Sequence '||l_missing_sequence||' not found') ;
135
136
137 WHEN OTHERS THEN
138 ROLLBACK TO SAVEPOINT CCTCKSEQ;
139 raise_application_error(-20000, sqlerrm || '. Could not modify sequence') ;
140 END check_sequence;
141
142
143 END cct_check_seq_pkg;
[Home] [Help]