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;
6 v_sec integer;
7 v_min integer;
8 v_hour integer;
9 v_num integer;
10 v_timestamp timestamp;
11 v_temp varchar2(30);
12 BEGIN
13
14
15 IF a_timeinsecs = 0 THEN
16 -- nothing to do
17 RETURN;
18 END IF;
19
20 -- time in secs since the beginning of the day
21 SELECT
22 TO_CHAR(systimestamp,'HH24') * 60*60+
23 + TO_CHAR(systimestamp,'MI') * 60
24 + TO_CHAR(systimestamp,'SS') into v_secs FROM dual;
25
26 v_secs := v_secs - a_timeinsecs;
27
28 -- reconstruct the time after substracting the time duration
29 v_sec := MOD (v_secs, 60);
30 v_min := MOD (trunc (v_secs/60), 60);
31 v_hour := MOD (trunc (v_secs/3600), 24);
32 -- stage it as character data
33 v_temp := to_char(sysdate,'DD-MON-YY') || ' '|| v_hour||'.'||v_min||'.'||v_sec;
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;
43 COMMIT;
44
45 END;
46
47
48 PROCEDURE delete_object(a_gname raw,a_guid raw,a_filename nvarchar2)
49 IS
50 a_gname_is_not_null BOOLEAN := FALSE;
51 a_guid_is_not_null BOOLEAN := FALSE;
52 a_filename_is_not_null BOOLEAN := FALSE;
53 BEGIN
54 IF a_gname IS NOT NULL THEN
55 a_gname_is_not_null := TRUE;
56 ELSE
57 -- GNAME MUST ALWAYS BE THERE, ELSE MAKE IT A NO-OP
58 return;
59 END IF;
60
61 IF a_guid IS NOT NULL THEN
62 a_guid_is_not_null := TRUE;
63 END IF;
64
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
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;
83
84 COMMIT;
85 END;
86
87
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);
97 a_gname_is_not_null BOOLEAN := FALSE;
98 a_guid_is_not_null BOOLEAN := FALSE;
99 a_filename_is_not_null boolean := FALSE;
100 v_publishdata VARCHAR2(512);
101 v_query VARCHAR2(256);
102
103 BEGIN
104
105 IF a_gname IS NOT NULL THEN
106 a_gname_is_not_null := TRUE;
107 ELSE
108 -- GNAME MUST ALWAYS BE THERE, MAKE IT A No-Op OTHERWISE
109 a_guid := null;
110 return v_rc;
111 END IF;
112
113 IF a_guid IS NOT NULL THEN
114 a_guid_is_not_null := TRUE;
115 END IF;
116
117 IF a_filename IS NOT NULL THEN
118 a_filename_is_not_null := TRUE;
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 ' ||
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 '||
134 'group_name = :10 and '||
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
144
145 begin
146
147 EXECUTE IMMEDIATE v_publishdata using a_gname, a_guid, a_filename, a_gname, a_guid, a_filename,a_gname, a_guid, a_filename,a_gname, a_guid, a_filename;
148 EXCEPTION
149 WHEN OTHERS THEN
150 IF SQLCODE = -00942 THEN
151 Raise_Application_Error(-20900,'Temporary Table does not not exist');
152 END IF;
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 '||
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 '||
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;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF SQLCODE = -00942 THEN
180 Raise_Application_Error(-20900,'Temporary Table does not not exist');
181 END IF;
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 '||
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
198 begin
199 EXECUTE IMMEDIATE v_publishdata using a_gname, a_gname,a_gname;
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF SQLCODE = -00942 THEN
203 Raise_Application_Error(-20900,'Temporary Table does not not exist');
204 END IF;
205 END;
206
207 END IF;
208
209
210
211 OPEN v_rc FOR v_query;
212
213 RETURN v_rc;
214 END;
215
216 END;
[Home] [Help]