[Home] [Help]
PACKAGE BODY: APPS.FND_DSQL
Source
1 PACKAGE BODY fnd_dsql AS
2 /* $Header: AFUTSQLB.pls 120.1.12010000.1 2008/07/25 14:23:37 appldev ship $ */
3
4
5 TYPE VARCHAR2_TBL_TYPE IS VARRAY(100) OF VARCHAR2(2000);
6 TYPE DATE_TBL_TYPE IS VARRAY(100) OF DATE;
7 TYPE NUMBER_TBL_TYPE IS VARRAY(100) OF NUMBER;
8
9 g_maximum_varray_size CONSTANT pls_integer := 100;
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 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
67
68 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'C';
69 fnd_dsql.g_bind_vtbl(fnd_dsql.g_nbinds) := p_value;
70
71 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
72 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
73 EXCEPTION
74 WHEN OTHERS THEN
75 report_error(g_package_name || '.add_bind(VARCHAR2)', Sqlerrm);
76 RAISE;
77 END add_bind; --VARCHAR2
78
79
80 PROCEDURE add_bind(p_value IN DATE)
81 IS
82 BEGIN
83 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
84
85 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'D';
86 fnd_dsql.g_bind_dtbl(fnd_dsql.g_nbinds) := p_value;
87
88 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
89 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
90 EXCEPTION
91 WHEN OTHERS THEN
92 report_error(g_package_name || '.add_bind(DATE)', Sqlerrm);
93 RAISE;
94 END add_bind; --DATE
95
96 PROCEDURE add_bind(p_value IN NUMBER)
97 IS
98 BEGIN
99 fnd_dsql.g_nbinds := fnd_dsql.g_nbinds + 1;
100
101 fnd_dsql.g_bind_ttbl(fnd_dsql.g_nbinds) := 'N';
102 fnd_dsql.g_bind_ntbl(fnd_dsql.g_nbinds) := p_value;
103
104 fnd_dsql.g_dsql_text := (fnd_dsql.g_dsql_text ||
105 ':FND_BIND' || To_char(fnd_dsql.g_nbinds));
106 EXCEPTION
107 WHEN OTHERS THEN
108 report_error(g_package_name || '.add_bind(NUMBER)', Sqlerrm);
109 RAISE;
110 END add_bind; --NUMBER
111
112
113 PROCEDURE set_cursor(p_cursor_id IN INTEGER)
114 IS
115 BEGIN
116 fnd_dsql.g_cursor_id := p_cursor_id;
117 EXCEPTION
118 WHEN OTHERS THEN
119 report_error(g_package_name || '.set_cursor_id()', Sqlerrm);
120 RAISE;
121 END set_cursor;
122
123 PROCEDURE do_binds
124 IS
125 BEGIN
126 FOR i IN 1..fnd_dsql.g_nbinds LOOP
127 IF (fnd_dsql.g_bind_ttbl(i) = 'D') THEN
128 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
129 ':FND_BIND' || To_char(i),
130 fnd_dsql.g_bind_dtbl(i));
131 ELSIF (fnd_dsql.g_bind_ttbl(i) = 'N') THEN
132 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
133 ':FND_BIND' || To_char(i),
134 fnd_dsql.g_bind_ntbl(i));
135 ELSE
136 dbms_sql.bind_variable(fnd_dsql.g_cursor_id,
137 ':FND_BIND' || To_char(i),
138 fnd_dsql.g_bind_vtbl(i));
139 END IF;
140 END LOOP;
141 EXCEPTION
142 WHEN OTHERS THEN
143 report_error(g_package_name || '.do_binds()', Sqlerrm);
144 RAISE;
145 END do_binds;
146
147
148 FUNCTION get_text(p_with_debug IN BOOLEAN DEFAULT FALSE)
149 RETURN VARCHAR2
150 IS
151 l_return VARCHAR2(32000);
152 BEGIN
153 l_return := Rtrim(Ltrim(fnd_dsql.g_dsql_text));
154 IF (p_with_debug) THEN
155 l_return := l_return || chr_newline;
156 FOR i IN 1..fnd_dsql.g_nbinds LOOP
157 l_return := (l_return || fnd_dsql.g_bind_ttbl(i) ||
158 ':FND_BIND' || To_char(i) || '=' );
159 IF (fnd_dsql.g_bind_ttbl(i) = 'D') THEN
160 l_return := l_return || To_char(fnd_dsql.g_bind_dtbl(i),
161 'YYYY/MM/DD HH24:MI:SS');
162 ELSIF (fnd_dsql.g_bind_ttbl(i) = 'N') THEN
163 l_return := l_return || fnd_dsql.g_bind_ntbl(i);
164 ELSE
165 l_return := l_return || fnd_dsql.g_bind_vtbl(i);
166 END IF;
167 l_return := l_return || chr_newline;
168 END LOOP;
169 END IF;
170 RETURN (l_return);
171 EXCEPTION
172 WHEN OTHERS THEN
173 report_error(g_package_name || '.get_text()', Sqlerrm);
174 RAISE;
175 END get_text;
176
177 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
178 IS
179 i INTEGER;
180 m INTEGER;
181 c INTEGER := 75;
182 BEGIN
183 execute immediate ('begin dbms' ||
184 '_output' ||
185 '.enable(1000000); end;');
186 m := Ceil(Length(p_debug)/c);
187 FOR i IN 1..m LOOP
188 execute immediate ('begin dbms' ||
189 '_output' ||
190 '.put_line(''' ||
191 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
192 '''); end;');
193 END LOOP;
194 EXCEPTION
195 WHEN OTHERS THEN
196 NULL;
197 END dbms_debug;
198
199 PROCEDURE fnd_dsql_test
200 IS
201 i INTEGER;
202 m INTEGER;
203 l_dsql VARCHAR2(32000);
204 l_debug VARCHAR2(32000);
205 BEGIN
206 fnd_dsql.init;
207 fnd_dsql.add_text('Start:' || chr_newline);
208 i := 0;
209 LOOP
210 i := i + 1;
211 IF i > g_maximum_varray_size THEN
212 EXIT;
213 END IF;
214 m := MOD(i,3);
215 fnd_dsql.add_text(' i:' || To_char(i));
216 IF m = 0 THEN
217 fnd_dsql.add_text('varchar2=');
218 fnd_dsql.add_bind('test' || To_char(i));
219 ELSIF m = 1 THEN
220 fnd_dsql.add_text('date=');
221 fnd_dsql.add_bind(Sysdate);
222 ELSE
223 fnd_dsql.add_text('number=');
224 fnd_dsql.add_bind(i);
225 END IF;
226 END LOOP;
227 l_dsql := fnd_dsql.get_text;
228 l_debug := fnd_dsql.get_text(TRUE);
229 dbms_debug(l_dsql);
230 dbms_debug(l_debug);
231 EXCEPTION
232 WHEN OTHERS THEN
233 l_dsql := 'SQLERRM:' || Sqlerrm;
234 l_debug := 'i:' || To_char(i) || ' ' || fnd_message.get;
235 dbms_debug(l_dsql);
236 dbms_debug(l_debug);
237 END fnd_dsql_test;
238
239
240 --
241 -- Package Initialization.
242 --
243 BEGIN
244 fnd_dsql.chr_newline := fnd_global.newline;
245 fnd_dsql.g_dsql_text := NULL;
246 fnd_dsql.g_cursor_id := NULL;
247 fnd_dsql.g_nbinds := 0;
248
249 --
250 -- Call varray constructors. Otherwise it gives
251 -- ORA-06531: Reference to uninitialized collection
252 --
253 fnd_dsql.g_bind_ttbl := varchar2_tbl_type();
254 fnd_dsql.g_bind_vtbl := varchar2_tbl_type();
255 fnd_dsql.g_bind_dtbl := date_tbl_type();
256 fnd_dsql.g_bind_ntbl := number_tbl_type();
257
258 --
259 -- Extend the varrays. Otherwise it gives
260 -- ORA-06533: Subscript beyond count
261 --
262 fnd_dsql.g_bind_ttbl.EXTEND(g_maximum_varray_size);
263 fnd_dsql.g_bind_vtbl.EXTEND(g_maximum_varray_size);
264 fnd_dsql.g_bind_dtbl.EXTEND(g_maximum_varray_size);
265 fnd_dsql.g_bind_ntbl.EXTEND(g_maximum_varray_size);
266 EXCEPTION
267 WHEN OTHERS THEN
268 report_error(g_package_name || '.Package Initialization', Sqlerrm);
269 RAISE;
270 END fnd_dsql;