[Home] [Help]
PACKAGE BODY: APPS.FND_DSQL
Source
1 PACKAGE BODY fnd_dsql AS
2 /* $Header: AFUTSQLB.pls 120.1.12020000.2 2012/07/31 19:58:35 hgeorgi ship $ */
3
4
5 TYPE VARCHAR2_TBL_TYPE IS VARRAY(512) OF VARCHAR2(2000);
6 TYPE DATE_TBL_TYPE IS VARRAY(512) OF DATE;
7 TYPE NUMBER_TBL_TYPE IS VARRAY(512) OF NUMBER;
8
9 g_maximum_varray_size CONSTANT pls_integer := 512;
10
11 --
12 -- Global Variables:
13 --
14 g_dsql_text VARCHAR2(32000);
15 g_cursor_id INTEGER;
16 g_nbinds PLS_INTEGER;
17 g_bind_ttbl VARCHAR2_TBL_TYPE; -- bind types
18 g_bind_vtbl VARCHAR2_TBL_TYPE; -- varchar2 type bind values
19 g_bind_dtbl DATE_TBL_TYPE; -- date type bind values
20 g_bind_ntbl NUMBER_TBL_TYPE; -- number type bind values
21
22 chr_newline VARCHAR2(8);
23 g_package_name VARCHAR2(30) := 'fnd_dsql';
24
25 -- ======================================================================
26 -- Utility Functions
27 -- ======================================================================
28 PROCEDURE report_error(p_routine IN VARCHAR2,
29 p_reason IN VARCHAR2)
30 IS
31 BEGIN
32 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
33 fnd_message.set_token('ROUTINE', p_routine);
34 fnd_message.set_token('REASON', p_reason);
35 END report_error;
36
37 -- ======================================================================
38 -- Public Functions
39 -- ======================================================================
40 PROCEDURE init
41 IS
42 BEGIN
43 fnd_dsql.g_dsql_text := NULL;
44 fnd_dsql.g_cursor_id := NULL;
45 fnd_dsql.g_nbinds := 0;
46 EXCEPTION
47 WHEN OTHERS THEN
48 report_error(g_package_name || '.init' , Sqlerrm);
49 RAISE;
50 END init;
51
52
53 PROCEDURE add_text(p_text IN VARCHAR2) IS
54 BEGIN
55 fnd_dsql.g_dsql_text := fnd_dsql.g_dsql_text || p_text;
56 EXCEPTION
57 WHEN OTHERS THEN
58 report_error(g_package_name || '.add_text()', Sqlerrm);
59 RAISE;
60 END add_text;
61
62
63 PROCEDURE add_bind(p_value IN VARCHAR2)
64 IS
65 BEGIN
66 if (fnd_dsql.g_nbinds = g_maximum_varray_size) then
67 raise_application_error(-20001, 'Bind arrays are full. ' ||
68 'Maximum Number of Binds: ' || g_maximum_varray_size);
69 end if;
70 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
71
72 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'C';
73 fnd_dsql.g_bind_vtbl(fnd_dsql.g_nbinds) := p_value;
74
75 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
76 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
77 EXCEPTION
78 WHEN OTHERS THEN
79 report_error(g_package_name || '.add_bind(VARCHAR2)', Sqlerrm);
80 RAISE;
81 END add_bind; --VARCHAR2
82
83
84 PROCEDURE add_bind(p_value IN DATE)
85 IS
86 BEGIN
87 if (fnd_dsql.g_nbinds = g_maximum_varray_size) then
88 raise_application_error(-20001, 'Bind arrays are full. ' ||
89 'Maximum Number of Binds: ' || g_maximum_varray_size);
90 end if;
91 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
92
93 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'D';
94 fnd_dsql.g_bind_dtbl(fnd_dsql.g_nbinds) := p_value;
95
96 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
97 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
98 EXCEPTION
99 WHEN OTHERS THEN
100 report_error(g_package_name || '.add_bind(DATE)', Sqlerrm);
101 RAISE;
102 END add_bind; --DATE
103
104 PROCEDURE add_bind(p_value IN NUMBER)
105 IS
106 BEGIN
107 if (fnd_dsql.g_nbinds = g_maximum_varray_size) then
108 raise_application_error(-20001, 'Bind arrays are full. ' ||
109 'Maximum Number of Binds: ' || g_maximum_varray_size);
110 end if;
111 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
112
113 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'N';
114 fnd_dsql.g_bind_ntbl(fnd_dsql.g_nbinds) := p_value;
115
116 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
117 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
118 EXCEPTION
119 WHEN OTHERS THEN
120 report_error(g_package_name || '.add_bind(NUMBER)', Sqlerrm);
121 RAISE;
122 END add_bind; --NUMBER
123
124
125 PROCEDURE set_cursor(p_cursor_id IN INTEGER)
126 IS
127 BEGIN
128 fnd_dsql.g_cursor_id := p_cursor_id;
129 EXCEPTION
130 WHEN OTHERS THEN
131 report_error(g_package_name || '.set_cursor_id()', Sqlerrm);
132 RAISE;
133 END set_cursor;
134
135 PROCEDURE do_binds
136 IS
137 BEGIN
138 FOR i IN 1..fnd_dsql.g_nbinds LOOP
139 IF (fnd_dsql.g_bind_ttbl(i) = 'D') THEN
140 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
141 ':FND_BIND' || To_char(i),
142 fnd_dsql.g_bind_dtbl(i));
143 ELSIF (fnd_dsql.g_bind_ttbl(i) = 'N') THEN
144 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
145 ':FND_BIND' || To_char(i),
146 fnd_dsql.g_bind_ntbl(i));
147 ELSE
148 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
149 ':FND_BIND' || To_char(i),
150 fnd_dsql.g_bind_vtbl(i));
151 END IF;
152 END LOOP;
153 EXCEPTION
154 WHEN OTHERS THEN
155 report_error(g_package_name || '.do_binds()', Sqlerrm);
156 RAISE;
157 END do_binds;
158
159
160 FUNCTION get_text(p_with_debug IN BOOLEAN DEFAULT FALSE)
161 RETURN VARCHAR2
162 IS
163 l_return VARCHAR2(32000);
164 BEGIN
165 l_return := Rtrim(Ltrim(fnd_dsql.g_dsql_text));
166 IF (p_with_debug) THEN
167 l_return := l_return || chr_newline;
168 FOR i IN 1..fnd_dsql.g_nbinds LOOP
169 l_return := (l_return || fnd_dsql.g_bind_ttbl(i) ||
170 ':FND_BIND' || To_char(i) || '=' );
171 IF (fnd_dsql.g_bind_ttbl(i) = 'D') THEN
172 l_return := l_return || To_char(fnd_dsql.g_bind_dtbl(i),
173 'YYYY/MM/DD HH24:MI:SS');
174 ELSIF (fnd_dsql.g_bind_ttbl(i) = 'N') THEN
175 l_return := l_return || fnd_dsql.g_bind_ntbl(i);
176 ELSE
177 l_return := l_return || fnd_dsql.g_bind_vtbl(i);
178 END IF;
179 l_return := l_return || chr_newline;
180 END LOOP;
181 END IF;
182 RETURN (l_return);
183 EXCEPTION
184 WHEN OTHERS THEN
185 report_error(g_package_name || '.get_text()', Sqlerrm);
186 RAISE;
187 END get_text;
188
189 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
190 IS
191 i INTEGER;
192 m INTEGER;
193 c INTEGER := 75;
194 BEGIN
195 execute immediate ('begin dbms' ||
196 '_output' ||
197 '.enable(1000000); end;');
198 m := Ceil(Length(p_debug)/c);
199 FOR i IN 1..m LOOP
200 execute immediate ('begin dbms' ||
201 '_output' ||
202 '.put_line(''' ||
203 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
204 '''); end;');
205 END LOOP;
206 EXCEPTION
207 WHEN OTHERS THEN
208 NULL;
209 END dbms_debug;
210
211 PROCEDURE fnd_dsql_test
212 IS
213 i INTEGER;
214 m INTEGER;
215 l_dsql VARCHAR2(32000);
216 l_debug VARCHAR2(32000);
217 BEGIN
218 fnd_dsql.init;
219 fnd_dsql.add_text('Start:' || chr_newline);
220 i := 0;
221 LOOP
222 i := i + 1;
223 IF i > g_maximum_varray_size THEN
224 EXIT;
225 END IF;
226 m := MOD(i,3);
227 fnd_dsql.add_text(' i:' || To_char(i));
228 IF m = 0 THEN
229 fnd_dsql.add_text('varchar2=');
230 fnd_dsql.add_bind('test' || To_char(i));
231 ELSIF m = 1 THEN
232 fnd_dsql.add_text('date=');
233 fnd_dsql.add_bind(Sysdate);
234 ELSE
235 fnd_dsql.add_text('number=');
236 fnd_dsql.add_bind(i);
237 END IF;
238 END LOOP;
239 l_dsql := fnd_dsql.get_text;
240 l_debug := fnd_dsql.get_text(TRUE);
241 dbms_debug(l_dsql);
242 dbms_debug(l_debug);
243 EXCEPTION
244 WHEN OTHERS THEN
245 l_dsql := 'SQLERRM:' || Sqlerrm;
246 l_debug := 'i:' || To_char(i) || ' ' || fnd_message.get;
250
247 dbms_debug(l_dsql);
248 dbms_debug(l_debug);
249 END fnd_dsql_test;
251
252 --
253 -- Package Initialization.
254 --
255 BEGIN
256 fnd_dsql.chr_newline := fnd_global.newline;
257 fnd_dsql.g_dsql_text := NULL;
258 fnd_dsql.g_cursor_id := NULL;
259 fnd_dsql.g_nbinds := 0;
260
261 --
262 -- Call varray constructors. Otherwise it gives
263 -- ORA-06531: Reference to uninitialized collection
264 --
265 fnd_dsql.g_bind_ttbl := varchar2_tbl_type();
266 fnd_dsql.g_bind_vtbl := varchar2_tbl_type();
267 fnd_dsql.g_bind_dtbl := date_tbl_type();
268 fnd_dsql.g_bind_ntbl := number_tbl_type();
269
270 --
271 -- Extend the varrays. Otherwise it gives
272 -- ORA-06533: Subscript beyond count
273 --
274 fnd_dsql.g_bind_ttbl.EXTEND(g_maximum_varray_size);
275 fnd_dsql.g_bind_vtbl.EXTEND(g_maximum_varray_size);
276 fnd_dsql.g_bind_dtbl.EXTEND(g_maximum_varray_size);
277 fnd_dsql.g_bind_ntbl.EXTEND(g_maximum_varray_size);
278 EXCEPTION
279 WHEN OTHERS THEN
280 report_error(g_package_name || '.Package Initialization', Sqlerrm);
281 RAISE;
282 END fnd_dsql;