1 PACKAGE BODY fnd_odf_upd AS
2 /* $Header: fndpoupb.pls 115.6 2004/03/12 19:43:42 bhthiaga noship $ */
3 PROCEDURE odfupd_row (p_selst IN VARCHAR2,
4 p_updst IN VARCHAR2,
5 p_errorCode OUT NOCOPY VARCHAR2,
6 p_retmsg OUT NOCOPY VARCHAR2)
7 IS
8 /* p_selSt : The select statement which return the rowids of all
9 * the rows that have a null value.
10 * p_updSt : The update statement which fills the null values with
11 * some default value.
12 */
13 l_rowIdBulk DBMS_SQL.Varchar2_Table; -- Array to hold the rowids.
14 curSel NUMBER; -- Cursor for select statement
15 curUpd NUMBER; -- Cursor for update statement
16 dummy NUMBER; -- Dummy number
17 ret NUMBER; -- Variable to hold the no. of rows processed.
18 snapshot_old EXCEPTION;
19 PRAGMA EXCEPTION_INIT(snapshot_old, -1555); -- Define the Exception for
20 -- snapshot too old problem
21
22 BEGIN
23
24
25 /* steps:
26 * Parse the dynamic query
27 * Bind an array to hold the selected rowid.
28 * Execute the query to get the data to buffer.
29 */
30 curSel := DBMS_SQL.OPEN_CURSOR;
31 DBMS_SQL.PARSE(curSel, p_selSt, DBMS_SQL.NATIVE);
32 DBMS_SQL.DEFINE_ARRAY(curSel, 1 , l_rowIdBulk, 100, 0);
33 ret := DBMS_SQL.EXECUTE(curSel);
34
35 /* Create the cursor for the update statement.
36 * The dynamic update query is parsed.
37 */
38 curUpd := DBMS_SQL.OPEN_CURSOR;
39 DBMS_SQL.PARSE(curUpd,p_updst,DBMS_SQL.NATIVE);
40
41
42 LOOP
43 -- Fetch the next batch of rows into buffer.
44 ret := DBMS_SQL.FETCH_ROWS(curSel);
45
46 if ( ret > 0 ) then
47
48 /* Snapshot too old problem.
49 * We try update for a max of 6 times.
50 */
51 FOR j IN 1..6 LOOP
52 BEGIN
53 /* Read the column value into the array */
54 DBMS_SQL.COLUMN_VALUE(curSel, 1, l_rowIdBulk);
55
56 /* Bind the array to the update cursor */
57 DBMS_SQL.BIND_ARRAY(curUpd, ':1',l_rowIdBulk );
58 dummy := DBMS_SQL.EXECUTE(curUpd);
59
60
61 COMMIT;
62 EXIT; -- no errors so break out of the inner for loop
63 EXCEPTION
64 when snapshot_old then
65 null;
66 when OTHERS THEN
67 RAISE;
68
69 END;
70 END LOOP;
71
72 end if;
73
74 EXIT when ret <> 100; -- Exit when not 100 records is returned.
75 END LOOP;
76 /* Close both the cursors. */
77 DBMS_SQL.CLOSE_CURSOR(curUpd);
78 DBMS_SQL.CLOSE_CURSOR(curSel);
79
80 EXCEPTION
81 when others then
82 if DBMS_SQL.IS_OPEN(curUpd) then
83 DBMS_SQL.CLOSE_CURSOR(curUpd);
84 end if;
85 if DBMS_SQL.IS_OPEN(curSel) then
86 DBMS_SQL.CLOSE_CURSOR(curSel);
87 end if;
88 raise;
89 end odfupd_row;
90 end fnd_odf_upd;
[Home] [Help]