[Home] [Help]
PACKAGE BODY: SYS.OWA_OPT_LOCK
Source
1 package body owa_opt_lock
2 as
3
4 last_column exception;
5 pragma exception_init( last_column, -1007 );
6
7 /******************************************************************/
8 /* Function to check if the argument is a valid object */
9 /******************************************************************/
10 procedure validate_object_name (p_owner in varchar2, p_object in varchar2)
11 is
12 stmt_cursor number;
13 rc number;
14 found number := 0;
15 p_schema varchar2(30) := upper(p_owner);
16 p_obj varchar2(30) := upper(p_object);
17 begin
18 stmt_cursor := dbms_sql.open_cursor;
19 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
20 'begin
21 select count(*)
22 into :found
23 from all_objects
24 where owner = :p_schema
25 and object_name = :p_obj;
26 exception
27 when others then
28 :found := 0;
29 end;', dbms_sql.v7);
30 dbms_sql.bind_variable(stmt_cursor, ':p_schema', p_schema);
31 dbms_sql.bind_variable(stmt_cursor, ':p_obj', p_obj);
32 dbms_sql.bind_variable(stmt_cursor, ':found', found);
33 rc := dbms_sql.execute(stmt_cursor);
34 dbms_sql.variable_value(stmt_cursor, ':found', found);
35 dbms_sql.close_cursor(stmt_cursor);
36
37 if (found = 0) then
38 raise_application_error(-20001, 'Cannot resolve object');
39 end if;
40 end;
41
42 /******************************************************************/
43 /* Function to calculate checksum */
44 /******************************************************************/
45 function checksum( p_buff in varchar2 ) return number
46 is
47 l_sum number default 0;
48 l_n number;
49 l_nu number;
50 l_nl number;
51 begin
52 for i in 1 .. trunc(length(p_buff||'x')/2) loop
53 if ascii(substr(p_buff||'x', 1+(i-1)*2, 1))>255 then
54 --2byte char + (1byte char or 2byte char)
55 l_nu:=0;
56 l_nl:=ascii(substr(p_buff||'x', 1+(i-1)*2, 1));
57 l_n := l_nl;
58 l_sum := mod( l_sum+l_n, 4294967296);
59
60 if ascii(substr(p_buff||'x', 2+(i-1)*2, 1))>255 then
61 --2byte char + 2byte char
62 l_nu:=0;
63 l_nl:=ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
64 l_n := l_nl;
65 l_sum := mod( l_sum+l_n, 4294967296);
66 else
67 --2byte char + 1byte char
68 l_nu:=ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
69 l_nl:=ascii('x');
70 l_n := l_nu*256 + l_nl;
71 l_sum := mod( l_sum+l_n, 4294967296);
72 end if;
73
74 elsif ascii(substr(p_buff||'x', 2+(i-1)*2, 1))>255 then
75 --1byte char + 2byte char
76 l_nu:=0;
77 l_nl:=ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
78 l_n := l_nl;
79 l_sum := mod( l_sum+l_n, 4294967296);
80
81 l_nu:=ascii('x');
82 l_nl:=ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
83 l_n := l_nu*256 + l_nl;
84 l_sum := mod( l_sum+l_n, 4294967296);
85
86 else
87 --1byte char + 1byte char
88 l_nu:=ascii(substr(p_buff||'x', 1+(i-1)*2, 1));
89 l_nl:=ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
90 l_n := l_nu*256 + l_nl;
91 l_sum := mod( l_sum+l_n, 4294967296);
92
93 -- dbms_output.put_line('l_n : '||l_n);
94 end if;
95 end loop;
96
97 -- dbms_output.put_line('l_sum : '||l_sum);
98
99 while ( l_sum > 65536 ) loop
100 -- l_sum := bitand( l_sum, 65535 ) + trunc(l_sum/65536);
101 l_sum := mod( l_sum, 65536 ) + trunc(l_sum/65536);
102 end loop;
103 return l_sum;
104 end checksum;
105
106 function checksum( p_owner in varchar2,
107 p_tname in varchar2,
108 p_rowid in rowid ) return number
109 is
110 l_theQuery varchar2(4096) default NULL;
111 l_cursor integer;
112 l_variable number;
113 l_status number;
114 l_column_name varchar2(255);
115 l_data_type varchar2(106);
116 p_schema varchar2(30) := upper(p_owner);
117 p_obj varchar2(30) := upper(p_tname);
118 begin
119 -- Verify that there is no SQL injection
120 validate_object_name (p_schema, p_obj);
121
122 l_cursor := dbms_sql.open_cursor;
123
124 -- Fix 789868 - Common schema may not have access to this info.
125 sys.dbms_sys_sql.parse_as_user(
126 l_cursor,
127 'select column_name, data_type
128 from all_tab_columns
129 where owner = :p_schema
130 and table_name = :p_obj
131 order by column_id', dbms_sql.native);
132 dbms_sql.bind_variable(l_cursor, ':p_schema', p_schema);
133 dbms_sql.bind_variable(l_cursor, ':p_obj', p_obj);
134 dbms_sql.define_column(l_cursor, 1, l_column_name, 255);
135 dbms_sql.define_column(l_cursor, 2, l_data_type, 106);
136
137 l_status := dbms_sql.execute(l_cursor);
138 loop
139 l_status := dbms_sql.fetch_rows(l_cursor);
140 if (l_status <= 0) then
141 exit;
142 end if;
143 dbms_sql.column_value(l_cursor, 1, l_column_name);
144 dbms_sql.column_value(l_cursor, 2, l_data_type);
145 if (l_theQuery is NULL) then
146 l_theQuery := 'select owa_opt_lock.checksum(';
147 else
148 l_theQuery := l_theQuery || '||';
149 end if;
150 -- if the column type is XMLTYPE, then we cannot concatenate
151 -- just the l_column_name, as it undergoes xml2char conversion
152 -- which has a limitation for converted length that it cannot
153 -- exceed 4000, otherwise it throws ORA-19011,
154 -- instead, we need to wrap xml column with xmltype.getclobval
155 if (l_data_type != 'XMLTYPE') then
156 l_theQuery := l_theQuery || DBMS_ASSERT.ENQUOTE_NAME(l_column_name);
157 else
158 l_theQuery := l_theQuery || 'xmltype.getclobval(' ||
159 DBMS_ASSERT.ENQUOTE_NAME(l_column_name) || ')';
160 end if;
161 end loop;
162 dbms_sql.close_cursor(l_cursor);
163
164 l_theQuery := l_theQuery || ') from ' || DBMS_ASSERT.ENQUOTE_NAME(p_schema) || '.' || DBMS_ASSERT.ENQUOTE_NAME(p_obj) ||
165 ' where rowid = :x1 for update';
166
167 l_cursor := dbms_sql.open_cursor;
168 sys.dbms_sys_sql.parse_as_user( l_cursor, l_theQuery, dbms_sql.v7);
169 dbms_sql.bind_variable( l_cursor, ':x1', p_rowid );
170 dbms_sql.define_column( l_cursor, 1, l_variable );
171
172 l_status := dbms_sql.execute(l_cursor);
173 l_status := dbms_sql.fetch_rows(l_cursor);
174 dbms_sql.column_value( l_cursor, 1, l_variable );
175 dbms_sql.close_cursor( l_cursor );
176
177 return l_variable;
178 end;
179
180 /******************************************************************/
181 /* Procedure to store values before modifying values */
182 /******************************************************************/
183 procedure store_values( p_owner in varchar2,
184 p_tname in varchar2,
185 p_rowid in rowid )
186 is
187 l_theQuery varchar2(4096);
188 l_cursor integer;
189 l_variable varchar2(2000);
190 l_status number;
191 l_col_cnt number default 0;
192 p_schema varchar2(30) := upper(p_owner);
193 p_obj varchar2(30) := upper(p_tname);
194 begin
195 -- Verify that there is no SQL injection
196 validate_object_name (p_schema, p_obj);
197
198 l_theQuery := 'select rowid, a.* from ' || DBMS_ASSERT.ENQUOTE_NAME(p_schema) || '.' || DBMS_ASSERT.ENQUOTE_NAME(p_obj) ||
199 ' a where rowid = :x1';
200
201 l_cursor := dbms_sql.open_cursor;
202
203 sys.dbms_sys_sql.parse_as_user( l_cursor, l_theQuery, dbms_sql.v7 );
204 dbms_sql.bind_variable( l_cursor, ':x1', p_rowid );
205 for i in 1 .. 255 loop
206 begin
207 dbms_sql.define_column( l_cursor, i, l_variable, 2000 );
208 l_col_cnt := l_col_cnt + 1;
209 exception
210 when last_column then exit;
211 end;
212 end loop;
213
214 l_status := dbms_sql.execute(l_cursor);
215 l_status := dbms_sql.fetch_rows(l_cursor);
216
217 htp.formHidden( 'old_' || p_tname, htf.escape_sc(p_owner) );
218 htp.formHidden( 'old_' || p_tname, htf.escape_sc(p_tname) );
219 for i in 1 .. l_col_cnt loop
220 dbms_sql.column_value( l_cursor, i, l_variable );
221 htp.formHidden( 'old_'||p_tname, htf.escape_sc(l_variable) );
222 end loop;
223
224 dbms_sql.close_cursor( l_cursor );
225 end;
226
227 /******************************************************************/
228 /* Function to verify stored values */
229 /******************************************************************/
230 function verify_values( p_old_values in vcArray ) return boolean
231 is
232 l_theQuery varchar2(4096);
233 l_cursor integer;
234 l_variable varchar2(2000);
235 l_status number;
236 l_col_cnt number default 0;
237 l_return_val boolean default TRUE;
238 p_schema varchar2(30) := upper(p_old_values(1));
239 p_obj varchar2(30) := upper(p_old_values(2));
240 begin
241 -- Verify that there is no SQL injection
242 validate_object_name (p_schema, p_obj);
243
244 l_theQuery := 'select * from ' || DBMS_ASSERT.ENQUOTE_NAME(p_schema) || '.' || DBMS_ASSERT.ENQUOTE_NAME(p_obj) ||
245 ' where rowid = :x1 for update';
246
247 l_cursor := dbms_sql.open_cursor;
248
249 sys.dbms_sys_sql.parse_as_user( l_cursor, l_theQuery, dbms_sql.v7 );
250 dbms_sql.bind_variable( l_cursor, ':x1', p_old_values(3) );
251 for i in 1 .. 255 loop
252 begin
253 dbms_sql.define_column( l_cursor, i, l_variable, 2000 );
254 l_col_cnt := l_col_cnt + 1;
255 exception
256 when last_column then exit;
257 end;
258 end loop;
259
260 l_status := dbms_sql.execute(l_cursor);
261 l_status := dbms_sql.fetch_rows(l_cursor);
262
263 for i in 1 .. l_col_cnt loop
264 dbms_sql.column_value( l_cursor, i, l_variable );
265 if ( l_variable <> p_old_values(i+3) AND
266 l_variable is not null AND
267 p_old_values(i+3) is not null ) then
268 l_return_val := FALSE;
269 exit;
270 end if;
271 end loop;
272
273 dbms_sql.close_cursor( l_cursor );
274 return l_return_val;
275 end;
276
277 /******************************************************************/
278 /* Internal function used by verify_values */
279 /******************************************************************/
280 function get_rowid( p_old_values in vcArray ) return rowid
281 is
282 begin
283 return p_old_values(3);
284 end;
285
286 end;