1 PACKAGE BODY BISM_EXPORT_WRITER
2 /* $Header: bibexpwb.pls 120.2 2006/04/03 05:23:02 akbansal noship $ */
3 AS
4 FUNCTION get_guid RETURN raw
5 IS
6 oid1 raw(16);
7 oid2 raw(16);
8 BEGIN
9
10 select sys_guid() into oid1 from dual;
11 select sys_guid() into oid2 from dual;
12 return oid1||oid2;
13
14 END;
15
16 FUNCTION insert_object(a_gname raw,a_guid raw, a_filename nvarchar2, a_data nclob,a_binary_data blob)
17 RETURN timestamp
18 IS
19 v_time timestamp;
20 v_clob BISM_EXPORT.text%TYPE;
21 v_blob BISM_EXPORT.binarydata%TYPE;
22 BEGIN
23 IF a_data IS NULL AND a_binary_data IS NULL THEN
24 -- nothing to insert
25 RETURN NULL;
26 END IF;
27
28 v_time:= SYSDATE;
29
30 IF a_data IS NOT NULL THEN
31 BEGIN
32 SELECT text into v_clob FROM BISM_EXPORT WHERE
33 group_name=a_gname AND
34 group_id = a_guid AND
35 file_name = a_filename for update;
36 -- write piece wise
37 dbms_lob.write ( v_clob, dbms_lob.getlength(a_data), dbms_lob.getlength(v_clob)+1, a_data );
38 EXCEPTION WHEN
39 No_Data_Found then
40 -- data does not exist yet, create it
41 -- insert null for binary data (BLOB)
42 INSERT INTO BISM_EXPORT (GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) VALUES(a_gname,a_guid,a_filename,v_time,a_data,null);
43 END;
44 ELSIF a_binary_data IS NOT NULL THEN
45 BEGIN
46 SELECT binarydata into v_blob FROM BISM_EXPORT WHERE
47 group_name=a_gname AND
48 group_id = a_guid AND
49 file_name = a_filename for update;
50 -- write piece wise
51 dbms_lob.write ( v_blob, dbms_lob.getlength(a_binary_data), dbms_lob.getlength(v_blob)+1, a_binary_data );
52 EXCEPTION WHEN
53 No_Data_Found then
54 -- data does not exist yet, create it
55 -- insert null for text data (CLOB)
56 INSERT INTO BISM_EXPORT (GROUP_NAME, GROUP_ID, FILE_NAME, TIME_CREATED, TEXT, BINARYDATA) VALUES(a_gname,a_guid,a_filename,v_time,null,a_binary_data);
57 END;
58
59 END IF;
60
61 COMMIT;
62
63 RETURN v_time;
64 END;
65
66 PROCEDURE delete_object(a_gname raw,a_guid raw, a_filename nvarchar2)
67 IS
68 a_gname_is_not_null BOOLEAN := FALSE;
69 a_guid_is_not_null BOOLEAN := FALSE;
70 a_filename_is_not_null boolean := FALSE;
71 BEGIN
72 IF a_gname IS NOT NULL THEN
73 a_gname_is_not_null := TRUE;
74 END IF;
75
76 IF a_guid IS NOT NULL THEN
77 a_guid_is_not_null := TRUE;
78 END IF;
79
80 IF a_filename IS NOT NULL THEN
81 a_filename_is_not_null := TRUE;
82 END IF;
83
84
85 IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = true THEN
86 DELETE FROM BISM_EXPORT WHERE
87 group_name= a_gname AND
88 group_id = a_guid AND
89 file_name=a_filename;
90 ELSIF a_gname_is_not_null = TRUE and a_filename_is_not_null = true THEN
91 DELETE FROM BISM_EXPORT WHERE
92 group_name= a_gname AND
93 file_name=a_filename;
94 ELSIF a_gname_is_not_null = TRUE THEN
95 DELETE FROM BISM_EXPORT WHERE
96 group_name= a_gname;
97 END IF;
98
99 COMMIT;
100 END;
101
102 PROCEDURE delete_objects(a_timeinsecs integer )
103 IS
104 v_secs INTEGER;
105 v_sec integer;
106 v_min integer;
107 v_hour integer;
108 v_num integer;
109 v_timestamp timestamp;
110 v_temp varchar2(30);
111 BEGIN
112
113
114 IF a_timeinsecs = 0 THEN
115 -- nothing to do
116 RETURN;
117 END IF;
118
119 -- time in secs since the beginning of the day
120 SELECT
121 TO_CHAR(systimestamp,'HH24') * 60*60+
122 + TO_CHAR(systimestamp,'MI') * 60
123 + TO_CHAR(systimestamp,'SS') into v_secs FROM dual;
124
125 v_secs := v_secs - a_timeinsecs;
126
127 -- reconstruct the time after substracting the time duration
128 v_sec := MOD (v_secs, 60);
129 v_min := MOD (trunc (v_secs/60), 60);
130 v_hour := MOD (trunc (v_secs/3600), 24);
131 -- stage it as character data
132 v_temp := to_char(sysdate,'DD-MON-YY') || ' '|| v_hour||'.'||v_min||'.'||v_sec;
133 -- create timestamp
134
135 SELECT to_timestamp(v_temp, 'DD-MON-YY HH24.MI.SS') into v_timestamp from dual;
136 -- delete the objects
137
138 delete from bism_export where to_char(time_created, 'DD-MON-YY HH24.MI.SS') < v_temp;
139 COMMIT;
140
141 END;
142
143
144 FUNCTION get_object(a_gname in raw,a_guid in out nocopy raw, a_filename in nvarchar2)
145 RETURN SchemaCurType
146 IS
147 v_rc SchemaCurType;
148 v_clob bism_export.text%TYPE;
149 a_gname_is_not_null BOOLEAN := FALSE;
150 a_guid_is_not_null BOOLEAN := FALSE;
151 a_filename_is_not_null boolean := FALSE;
152 BEGIN
153
154 IF a_gname IS NOT NULL THEN
155 a_gname_is_not_null := TRUE;
156 END IF;
157
158 IF a_guid IS NOT NULL THEN
159 a_guid_is_not_null := TRUE;
160 END IF;
161
162 IF a_filename IS NOT NULL THEN
163 a_filename_is_not_null := TRUE;
164 END IF;
165
166 IF a_gname_is_not_null = TRUE AND a_guid_is_not_null = true AND a_filename_is_not_null = true THEN
167 -- no need to fetch groupd_id
168 OPEN v_rc FOR SELECT text,binarydata FROM BISM_EXPORT WHERE group_name = a_gname and
169 group_id = a_guid and file_name = a_filename;
170 ELSIF a_gname_is_not_null = TRUE and a_filename_is_not_null = true THEN
171 SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname and file_name = a_filename;
172 OPEN v_rc FOR SELECT text,binarydata FROM BISM_EXPORT WHERE group_name = a_gname and file_name = a_filename;
173 ELSIF a_gname_is_not_null = TRUE THEN
174 SELECT group_id into a_guid FROM BISM_EXPORT WHERE group_name = a_gname;
175 OPEN v_rc FOR SELECT text,binarydata FROM BISM_EXPORT WHERE group_name = a_gname and file_name = a_filename;
176 END IF;
177 RETURN v_rc;
178 END;
179
180 END;
[Home] [Help]