1 PACKAGE BODY inv_sql_binding_pvt AS
2 /* $Header: INVVSQBB.pls 120.1 2005/06/11 07:33:32 appldev $ */
3 --
4 -- File : INVVSQBB.pls
5 -- Content : INV_SQL_BINDING_PVT package body
6 -- Description : Procedures and functions used for sql binding.
7 -- This package gives you a way to manage your bind
8 -- variables by registering them with the package
9 -- and get a indentifier which you can use in
10 -- your dynamic sql, and call the procedure bindvars
11 -- to bind all variables to the cursor passed in.
12 --
13 -- Notes :
14 -- Modified : 10/22/1999 bitang created
15 --
16 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SQL_BINDING_PVT';
17 --
18 g_line_feed VARCHAR2(1) := '
19 ';
20 -- Necessary variables for dynamically bound input parameters
21 TYPE num_rec_type IS RECORD
22 ( identifier VARCHAR2(10)
23 ,value NUMBER
24 );
25 TYPE char_rec_type IS RECORD
26 ( identifier VARCHAR2(10)
27 ,value VARCHAR2(240)
28 );
29 TYPE date_rec_type IS RECORD
30 ( identifier VARCHAR2(10)
31 ,value DATE
32 );
33 TYPE num_tbl_type IS TABLE OF num_rec_type INDEX BY BINARY_INTEGER;
34 TYPE char_tbl_type IS TABLE OF char_rec_type INDEX BY BINARY_INTEGER;
35 TYPE date_tbl_type IS TABLE OF date_rec_type INDEX BY BINARY_INTEGER;
36 --
37 g_num_tbl num_tbl_type; -- bind varible table for numbers
38 g_char_tbl char_tbl_type; -- bind varible table for varchar2s
39 g_date_tbl date_tbl_type; -- bind varible table for dates
40 g_num_counter INTEGER; -- size of g_num_tbl
41 g_char_counter INTEGER; -- size of g_char_tbl
42 g_date_counter INTEGER; -- size of g_date_tbl
43 g_num_pointer INTEGER; -- pointer to a record in g_num_tbl
44 g_char_pointer INTEGER; -- pointer to a record in g_char_tbl
45 g_date_pointer INTEGER; -- pointer to a record in g_date_tbl
46 --
47 -- API name : InitBindTables
48 -- Type : Private
49 -- Function : Initializes internal tables of bind variable names and their
50 -- values. Needed for dynamic SQL.
51 -- Pre-reqs : none
52 -- Parameters : none
53 -- Version : not tracked
54 -- Notes :
55 PROCEDURE InitBindTables IS
56 BEGIN
57 g_num_tbl.DELETE;
58 g_char_tbl.DELETE;
59 g_date_tbl.DELETE;
60 g_num_counter := 0;
61 g_char_counter := 0;
62 g_date_counter := 0;
63 g_num_pointer := 0;
64 g_char_pointer := 0;
65 g_date_pointer := 0;
66 END InitBindTables;
67 --
68 -- API name : SaveBindPointers
69 -- Type : Private
70 -- Function : Saves pointers to current bind variable tables rows.
71 -- Needed to distinguish between 'static' and 'dynamic'
72 -- entry sections according to implemented algorithm??
73 -- Pre-reqs : none
74 -- Parameters : none
75 -- Version : not tracked
76 -- Notes :
77 --
78 PROCEDURE SaveBindPointers IS
79 BEGIN
80 g_num_pointer := g_num_counter;
81 g_char_pointer := g_char_counter;
82 g_date_pointer := g_date_counter;
83 END SaveBindPointers;
84 --
85 -- API name : RestoreBindPointers
86 -- Type : Private
87 -- Function : Restores pointers to specific bind variable tables rows.
88 -- Needed to reuse 'static' and overwrite 'dynamic'
89 -- entry sections according to implemented algorithm.
90 -- Pre-reqs : none
91 -- Parameters : none
92 -- Version : not tracked
93 -- Notes :
94 --
95 PROCEDURE RestoreBindPointers IS
96 BEGIN
97 g_num_counter := g_num_pointer;
98 g_char_counter := g_char_pointer;
99 g_date_counter := g_date_pointer;
100 END RestoreBindPointers;
101 --
102 -- API name : InitBindVar
103 -- Type : Private
104 -- Function : Adds an entry to the corresponding bind variable table
105 -- and stores created name as well as value, returns name.
106 -- Pre-reqs : none
107 -- Parameters :
108 -- p_value in number required
109 -- OR
110 -- p_value in varchar2 required
111 -- OR
112 -- p_value in date required
113 -- return value OUT NOCOPY /* file.sql.39 change */ varchar2(10)
114 -- Version : not tracked
115 -- Notes : Overlayed functions for each supported data type
116 --
117 FUNCTION InitBindVar ( p_value IN NUMBER ) RETURN VARCHAR2 IS
118 BEGIN
119 g_num_counter := g_num_counter + 1;
120 g_num_tbl(g_num_counter).identifier := ':n'||to_char(g_num_counter);
121 g_num_tbl(g_num_counter).value := p_value;
122 RETURN g_num_tbl(g_num_counter).identifier;
123 END InitBindVar;
124 --
125 FUNCTION InitBindVar ( p_value IN VARCHAR2 ) RETURN VARCHAR2 IS
126 BEGIN
127 g_char_counter := g_char_counter + 1;
128 g_char_tbl(g_char_counter).identifier := ':c'||to_char(g_char_counter);
129 g_char_tbl(g_char_counter).value := p_value;
130 RETURN g_char_tbl(g_char_counter).identifier;
131 END InitBindVar;
132 --
133 FUNCTION InitBindVar ( p_value IN DATE ) RETURN VARCHAR2 IS
134 BEGIN
135 g_date_counter := g_date_counter + 1;
136 g_date_tbl(g_date_counter).identifier := ':d'||to_char(g_date_counter);
137 g_date_tbl(g_date_counter).value := p_value;
138 RETURN g_date_tbl(g_date_counter).identifier;
139 END InitBindVar;
140 --
141 -- API name : BindVars
142 -- Type : Private
143 -- Function : Binds all variables stored in the internal bind variable
144 -- tables to the given dynamic SQL cursor.
145 -- Pre-reqs : refer to dbms_sql package spec
146 -- Parameters :
147 -- p_cursor in integer required
148 -- Version : not tracked
149 -- Notes :
150 PROCEDURE BindVars ( p_cursor IN INTEGER ) IS
151 n_i INTEGER;
152 BEGIN
153 -- Bind all numeric variables
154 IF g_num_counter > 0 THEN
155 FOR n_i IN 1..g_num_counter LOOP
156 dbms_sql.bind_variable( p_cursor
157 ,g_num_tbl(n_i).identifier
158 ,g_num_tbl(n_i).value );
159 END LOOP;
160 END IF;
161 -- Bind all character variables
162 IF g_char_counter > 0 THEN
163 FOR n_i IN 1..g_char_counter LOOP
164 dbms_sql.bind_variable( p_cursor
165 ,g_char_tbl(n_i).identifier
166 ,g_char_tbl(n_i).value );
167 END LOOP;
168 END IF;
169 -- Bind all date variables
170 IF g_date_counter > 0 THEN
171 FOR n_i IN 1..g_date_counter LOOP
172 dbms_sql.bind_variable( p_cursor
173 ,g_date_tbl(n_i).identifier
174 ,g_date_tbl(n_i).value );
175 END LOOP;
176 END IF;
177 END BindVars;
178 --
179 -- API name : GetConversionString
180 -- Type : Private
181 -- Function : Returns SQL conversion function syntax corresponding to the
182 -- given data types to convert.
183 -- Pre-reqs : none
184 -- Parameters :
185 -- p_data_type_code in number required
186 -- p_parent_data_type_code in number required
187 -- x_left_part_conv_fct OUT NOCOPY /* file.sql.39 change */ varchar2(20)
188 -- x_right_part_conv_fct OUT NOCOPY /* file.sql.39 change */ varchar2(20)
189 -- Version : not tracked
190 -- Notes : needed to be verified
191 PROCEDURE GetConversionString
192 ( p_data_type_code IN NUMBER
193 ,p_parent_data_type_code IN NUMBER
194 ,x_left_part_conv_fct OUT NOCOPY /* file.sql.39 change */ VARCHAR2
195 ,x_right_part_conv_fct OUT NOCOPY /* file.sql.39 change */ VARCHAR2
196 ) IS
197 BEGIN
198 IF p_data_type_code <> p_parent_data_type_code THEN
199 IF p_data_type_code = 1 THEN
200 IF p_parent_data_type_code = 2 THEN -- char -> number
201 x_left_part_conv_fct := 'fnd_number.canonical_to_number(';
202 x_right_part_conv_fct := ')';
203 ELSIF p_parent_data_type_code = 3 THEN -- date -> number
204 -- Julian date assumed
205 x_left_part_conv_fct := 'to_number(to_char(';
206 x_right_part_conv_fct := ',''J''))';
207 END IF;
208 ELSIF p_data_type_code = 2 THEN
209 IF p_parent_data_type_code = 1 THEN -- number -> char
210 x_left_part_conv_fct := 'fnd_number.number_to_canonical(';
211 x_right_part_conv_fct := ')';
212 ELSIF p_parent_data_type_code = 3 THEN -- date -> char
213 -- standard Apps date format assumed
214 x_left_part_conv_fct := 'fnd_date.date_to_canonical(';
215 x_right_part_conv_fct := ')';
216 END IF;
217 ELSIF p_data_type_code = 3 THEN
218 IF p_parent_data_type_code = 1 THEN -- number -> date
219 -- Julian date assumed
220 x_left_part_conv_fct := 'to_date(to_char(';
221 x_right_part_conv_fct := '),''J'')';
222 ELSIF p_parent_data_type_code = 2 THEN -- char -> date
223 -- standard Apps date format assumed
224 x_left_part_conv_fct := 'fnd_date.canonical_to_date(';
225 x_right_part_conv_fct := ')';
226 END IF;
227 END IF;
228 ELSE
229 x_left_part_conv_fct := NULL;
230 x_right_part_conv_fct := NULL;
231 END IF;
232 END GetConversionString;
233 --
234 -- API name : ShowSQL
235 -- Type : Private
236 -- Function : Shows the text of the dynamically built SQL statement using
237 -- dbms_output. Needed for debugging.
238 -- Pre-reqs : none
239 -- Parameters :
240 -- p_sql_text in long required
241 -- Version : not tracked
242 -- Notes :
243 PROCEDURE showsql
244 ( p_sql_text IN long) IS
245 p_n NUMBER;
246 p_v VARCHAR2(1);
247 l_line_size NUMBER;
248 BEGIN
249 NULL;
250 /* dbms_output.enable(5000000);
251 l_line_size := 0;
252 for p_n in 1..length(p_sql_text) loop
253 p_v := substr(p_sql_text,p_n,1);
254 if p_v = g_line_feed THEN
255 dbms_output.new_line; l_line_size := 0;
256 ELSIF l_line_size IN (70,120) AND p_v = ' ' THEN
257 dbms_output.new_line; l_line_size := 0;
258 ELSIF l_line_size > 120 THEN
259 dbms_output.new_line; l_line_size := 0;
260 ELSE
261 dbms_output.put(p_v); l_line_size := l_line_size +1;
262 END IF;
263 END LOOP;
264 dbms_output.new_line;*/
265 EXCEPTION WHEN OTHERS THEN
266 NULL;
267 END showsql;
268 --
269 -- API name : ShowBindVars
270 -- Type : Private
271 -- Function : Shows all entries of the internal bind variable tables using
272 -- dbms_output. Needed for debugging.
273 -- Pre-reqs : none
274 -- Parameters : none
275 -- Version : not tracked
276 -- Notes :
277 PROCEDURE ShowBindVars IS
278 n_i INTEGER;
279 BEGIN
280 /*
281 -- Show all numeric variables
282 if g_num_counter > 0 then
283 for n_i in 1..g_num_counter loop
284 dbms_output.put_line(g_num_tbl(n_i).identifier||' = '||
285 fnd_number.number_to_canonical(g_num_tbl(n_i).value));
286 end loop;
287 end if;
288 -- Show all character variables
289 if g_char_counter > 0 then
290 for n_i in 1..g_char_counter loop
291 dbms_output.put_line(g_char_tbl(n_i).identifier||' = '||
292 g_char_tbl(n_i).value);
293 end loop;
294 end if;
295 -- Show all date variables
296 if g_date_counter > 0 then
297 for n_i in 1..g_date_counter loop
298 dbms_output.put_line(g_date_tbl(n_i).identifier||' = '||
299 fnd_date.date_to_canonical(g_date_tbl(n_i).value));
300 end loop;
301 end if; */
302 NULL;
303 END showbindvars;
304 END inv_sql_binding_pvt;
[Home] [Help]