1: PACKAGE BODY BISM_EXPORT_READER AS
2: /* $Header: bibexpb.pls 120.2 2006/04/03 05:21:58 akbansal noship $ */
3: PROCEDURE delete_objects(a_timeinsecs integer)
4: IS
5: v_secs INTEGER;
34: -- create timestamp
35:
36: SELECT to_timestamp(v_temp, 'DD-MON-YY HH24.MI.SS') into v_timestamp from dual;
37: -- delete objects from local cache
38: EXECUTE IMMEDIATE 'delete from bism_export_temp where to_char(time_created, ''DD-MON-YY HH24.MI.SS'') < :1' using v_temp;
39: -- we do not want to delete from actual table because an adversary having access (EXECUTE priv)
40: -- to BISM_EXPORT_READER package can potentially wipe out all objects
41: --
42: -- delete from bism_export where to_char(time_created, 'DD-MON-YY HH24.MI.SS') < v_temp;
36: SELECT to_timestamp(v_temp, 'DD-MON-YY HH24.MI.SS') into v_timestamp from dual;
37: -- delete objects from local cache
38: EXECUTE IMMEDIATE 'delete from bism_export_temp where to_char(time_created, ''DD-MON-YY HH24.MI.SS'') < :1' using v_temp;
39: -- we do not want to delete from actual table because an adversary having access (EXECUTE priv)
40: -- to BISM_EXPORT_READER package can potentially wipe out all objects
41: --
42: -- delete from bism_export where to_char(time_created, 'DD-MON-YY HH24.MI.SS') < v_temp;
43: COMMIT;
44:
38: EXECUTE IMMEDIATE 'delete from bism_export_temp where to_char(time_created, ''DD-MON-YY HH24.MI.SS'') < :1' using v_temp;
39: -- we do not want to delete from actual table because an adversary having access (EXECUTE priv)
40: -- to BISM_EXPORT_READER package can potentially wipe out all objects
41: --
42: -- delete from bism_export where to_char(time_created, 'DD-MON-YY HH24.MI.SS') < v_temp;
43: COMMIT;
44:
45: END;
46:
65: IF a_filename IS NOT NULL THEN
66: a_filename_is_not_null := TRUE;
67: END IF;
68:
69: -- NOTE : IT IS OK to delete objects from bism_export as long as the user provides
70: -- GNAME (really guid). Since this parameter is hard to guess, if user presents this
71: -- parameter, we trust him and delete the matching object (not all) from bism_export
72:
73: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = TRUE THEN
67: END IF;
68:
69: -- NOTE : IT IS OK to delete objects from bism_export as long as the user provides
70: -- GNAME (really guid). Since this parameter is hard to guess, if user presents this
71: -- parameter, we trust him and delete the matching object (not all) from bism_export
72:
73: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = TRUE THEN
74: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND group_id = a_guid AND file_name = a_filename;
75: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND group_id = :2 AND file_name = :3' using a_gname , a_guid, a_filename;
70: -- GNAME (really guid). Since this parameter is hard to guess, if user presents this
71: -- parameter, we trust him and delete the matching object (not all) from bism_export
72:
73: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = TRUE THEN
74: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND group_id = a_guid AND file_name = a_filename;
75: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND group_id = :2 AND file_name = :3' using a_gname , a_guid, a_filename;
76: ELSIF a_gname_is_not_null = TRUE AND a_filename_is_not_null = true THEN
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
71: -- parameter, we trust him and delete the matching object (not all) from bism_export
72:
73: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = TRUE THEN
74: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND group_id = a_guid AND file_name = a_filename;
75: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND group_id = :2 AND file_name = :3' using a_gname , a_guid, a_filename;
76: ELSIF a_gname_is_not_null = TRUE AND a_filename_is_not_null = true THEN
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
79: ELSIF a_gname_is_not_null = TRUE THEN
73: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = TRUE THEN
74: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND group_id = a_guid AND file_name = a_filename;
75: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND group_id = :2 AND file_name = :3' using a_gname , a_guid, a_filename;
76: ELSIF a_gname_is_not_null = TRUE AND a_filename_is_not_null = true THEN
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
79: ELSIF a_gname_is_not_null = TRUE THEN
80: DELETE FROM BISM_EXPORT WHERE group_name= a_gname;
81: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1' using a_gname;
74: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND group_id = a_guid AND file_name = a_filename;
75: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND group_id = :2 AND file_name = :3' using a_gname , a_guid, a_filename;
76: ELSIF a_gname_is_not_null = TRUE AND a_filename_is_not_null = true THEN
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
79: ELSIF a_gname_is_not_null = TRUE THEN
80: DELETE FROM BISM_EXPORT WHERE group_name= a_gname;
81: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1' using a_gname;
82: END IF;
76: ELSIF a_gname_is_not_null = TRUE AND a_filename_is_not_null = true THEN
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
79: ELSIF a_gname_is_not_null = TRUE THEN
80: DELETE FROM BISM_EXPORT WHERE group_name= a_gname;
81: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1' using a_gname;
82: END IF;
83:
84: COMMIT;
77: DELETE FROM BISM_EXPORT WHERE group_name= a_gname AND file_name = a_filename;
78: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1 AND file_name = :2' using a_gname , a_filename;
79: ELSIF a_gname_is_not_null = TRUE THEN
80: DELETE FROM BISM_EXPORT WHERE group_name= a_gname;
81: EXECUTE IMMEDIATE 'DELETE FROM BISM_EXPORT_TEMP WHERE group_name= :1' using a_gname;
82: END IF;
83:
84: COMMIT;
85: END;
88: FUNCTION get_object(a_gname in raw,a_guid in out nocopy raw, a_filename in nvarchar2)
89: RETURN SchemaCurType
90: IS
91: v_rc SchemaCurType;
92: v_clob bism_export.text%TYPE;
93: v_temp_clob bism_export.text%TYPE;
94: v_read_amount integer;
95: v_read_offset integer;
96: v_buffer nvarchar2(32767);
89: RETURN SchemaCurType
90: IS
91: v_rc SchemaCurType;
92: v_clob bism_export.text%TYPE;
93: v_temp_clob bism_export.text%TYPE;
94: v_read_amount integer;
95: v_read_offset integer;
96: v_buffer nvarchar2(32767);
97: a_gname_is_not_null BOOLEAN := FALSE;
119: END IF;
120:
121: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = true THEN
122:
123: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
124: 'values (:1,:2,:3, '||
125: '(SELECT time_created FROM bism_export WHERE '||
126: 'group_name = :4 and '||
127: 'group_id = :5 AND ' ||
121: IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = true THEN
122:
123: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
124: 'values (:1,:2,:3, '||
125: '(SELECT time_created FROM bism_export WHERE '||
126: 'group_name = :4 and '||
127: 'group_id = :5 AND ' ||
128: 'file_name = :6 ),'||
129: '(SELECT text FROM bism_export WHERE '||
125: '(SELECT time_created FROM bism_export WHERE '||
126: 'group_name = :4 and '||
127: 'group_id = :5 AND ' ||
128: 'file_name = :6 ),'||
129: '(SELECT text FROM bism_export WHERE '||
130: 'group_name = :7 and '||
131: 'group_id = :8 AND ' ||
132: 'file_name = :9 ),'||
133: '(SELECT binarydata FROM bism_export WHERE '||
129: '(SELECT text FROM bism_export WHERE '||
130: 'group_name = :7 and '||
131: 'group_id = :8 AND ' ||
132: 'file_name = :9 ),'||
133: '(SELECT binarydata FROM bism_export WHERE '||
134: 'group_name = :10 and '||
135: 'group_id = :11 AND ' ||
136: 'file_name = :12 ) )';
137:
135: 'group_id = :11 AND ' ||
136: 'file_name = :12 ) )';
137:
138:
139: v_query := 'SELECT text,binarydata FROM BISM_EXPORT_TEMP WHERE '||
140: 'group_name = '||''''|| a_gname ||''''||' and '||
141: 'group_id = '|| '''' || a_guid || ''''||' and '||
142: 'file_name = '|| '''' || a_filename || '''';
143:
153: END;
154:
155:
156: ELSIF a_gname_is_not_null = TRUE and a_filename_is_not_null = true THEN
157: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
158: 'values (:1,null,:2, '||
159: '(select time_created from bism_export WHERE '||
160: 'group_name = :3 and file_name = :4),'||
161: '(SELECT text FROM bism_export WHERE '||
155:
156: ELSIF a_gname_is_not_null = TRUE and a_filename_is_not_null = true THEN
157: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
158: 'values (:1,null,:2, '||
159: '(select time_created from bism_export WHERE '||
160: 'group_name = :3 and file_name = :4),'||
161: '(SELECT text FROM bism_export WHERE '||
162: 'group_name = :5 and '||
163: 'file_name = :6),'||
157: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
158: 'values (:1,null,:2, '||
159: '(select time_created from bism_export WHERE '||
160: 'group_name = :3 and file_name = :4),'||
161: '(SELECT text FROM bism_export WHERE '||
162: 'group_name = :5 and '||
163: 'file_name = :6),'||
164: '(SELECT binarydata FROM bism_export WHERE '||
165: 'group_name = :7 and '||
160: 'group_name = :3 and file_name = :4),'||
161: '(SELECT text FROM bism_export WHERE '||
162: 'group_name = :5 and '||
163: 'file_name = :6),'||
164: '(SELECT binarydata FROM bism_export WHERE '||
165: 'group_name = :7 and '||
166: 'file_name = :8))';
167:
168: v_query := 'SELECT text,binarydata FROM BISM_EXPORT_TEMP WHERE '||
164: '(SELECT binarydata FROM bism_export WHERE '||
165: 'group_name = :7 and '||
166: 'file_name = :8))';
167:
168: v_query := 'SELECT text,binarydata FROM BISM_EXPORT_TEMP WHERE '||
169: 'group_name = '|| '''' || a_gname||''''||' and '||
170: 'file_name = '|| '''' || a_filename || '''';
171:
172: SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname and file_name = a_filename;
168: v_query := 'SELECT text,binarydata FROM BISM_EXPORT_TEMP WHERE '||
169: 'group_name = '|| '''' || a_gname||''''||' and '||
170: 'file_name = '|| '''' || a_filename || '''';
171:
172: SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname and file_name = a_filename;
173:
174:
175: begin
176: EXECUTE IMMEDIATE v_publishdata using a_gname, a_filename,a_gname, a_filename, a_gname, a_filename,a_gname, a_filename;
182: END;
183:
184: ELSIF a_gname_is_not_null = TRUE THEN
185: -- THIS CASE SHOULD NEVER OCCUR, GNAME AND FILENAME ARE ALWAYS PRESENT
186: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
187: 'values (:1,null,null, '||
188: '(SELECT time_created FROM bism_export WHERE '||
189: 'group_name = :2), '||
190: '(SELECT text FROM bism_export WHERE '||
184: ELSIF a_gname_is_not_null = TRUE THEN
185: -- THIS CASE SHOULD NEVER OCCUR, GNAME AND FILENAME ARE ALWAYS PRESENT
186: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
187: 'values (:1,null,null, '||
188: '(SELECT time_created FROM bism_export WHERE '||
189: 'group_name = :2), '||
190: '(SELECT text FROM bism_export WHERE '||
191: 'group_name = :3), null)';
192:
186: v_publishdata := 'INSERT into BISM_EXPORT_TEMP(GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) '||
187: 'values (:1,null,null, '||
188: '(SELECT time_created FROM bism_export WHERE '||
189: 'group_name = :2), '||
190: '(SELECT text FROM bism_export WHERE '||
191: 'group_name = :3), null)';
192:
193: v_query := 'SELECT text FROM BISM_EXPORT_TEMP WHERE '||
194: 'group_name = '|| '''' || a_gname||'''';
189: 'group_name = :2), '||
190: '(SELECT text FROM bism_export WHERE '||
191: 'group_name = :3), null)';
192:
193: v_query := 'SELECT text FROM BISM_EXPORT_TEMP WHERE '||
194: 'group_name = '|| '''' || a_gname||'''';
195:
196: SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname ;
197:
192:
193: v_query := 'SELECT text FROM BISM_EXPORT_TEMP WHERE '||
194: 'group_name = '|| '''' || a_gname||'''';
195:
196: SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname ;
197:
198: begin
199: EXECUTE IMMEDIATE v_publishdata using a_gname, a_gname,a_gname;
200: EXCEPTION