1 PACKAGE BODY dbms_xqueryint AS
2
3 FUNCTION prepare(xqry in varchar2,
4 nlssrt in varchar2, nlscmp in varchar2, dbchr in varchar2, flags in number) return number
5 as LANGUAGE JAVA NAME
6 'oracle.xquery.OXQServer.prepareQuery(java.lang.String,
7 java.lang.String, java.lang.String, java.lang.String, int) return int';
8
9 FUNCTION preparexclb(xqry in clob,
10 nlssrt in varchar2, nlscmp in varchar2, dbchr in varchar2, flags in number) return number
11 as LANGUAGE JAVA NAME
12 'oracle.xquery.OXQServer.prepareQuery(oracle.sql.CLOB,
13 java.lang.String, java.lang.String, java.lang.String, int) return int';
14
15 /*pass null for context binds. */
16 procedure bind(hdl in number, name in varchar2, flags in number,
17 xctx in clob, schema in varchar2)
18 as LANGUAGE JAVA NAME
19 'oracle.xquery.OXQServer.bind(int, java.lang.String, int, oracle.sql.CLOB,
20 java.lang.String)';
21
22 procedure bindWithType(hdl in number, name in varchar2, flags in number, xctx in clob, schema in varchar2 , xqtype in number)
23 as LANGUAGE JAVA NAME
24 'oracle.xquery.OXQServer.bindWithType(int, java.lang.String, int,
25 oracle.sql.CLOB,
26 java.lang.String, int)';
27
28 FUNCTION fetchOne(hdl in number, xctx in out clob, flags in out number,
29 str out varchar2, xqtype in out number) return number as LANGUAGE JAVA NAME
30 'oracle.xquery.OXQServer.fetchOne(int, oracle.sql.CLOB[], int[],
31 java.lang.String[], int[]) return int';
32
33 FUNCTION fetchAll(hdl in number, xctx in out clob, flags in out number)
34 return number
35 as LANGUAGE JAVA NAME
36 'oracle.xquery.OXQServer.fetchAll(int, oracle.sql.CLOB[], int[]) return int';
37
38 procedure execQuery(hdl in number) as LANGUAGE JAVA NAME
39 'oracle.xquery.OXQServer.execute(int)' ;
40
41 procedure closeHdl(hdl in number) as LANGUAGE JAVA NAME
42 'oracle.xquery.OXQServer.closeHdl(int)' ;
43
44 /* return xmltype(content) */
45 FUNCTION exec_cont(hdl in number) return sys.xmltype is
46 clb clob := ' ';
47 ret xmltype := null;
48 outflg number := 0;
49 fetch_ok number ;
50 begin
51
52 fetch_ok := fetchAll(hdl, clb, outflg);
53
54 if fetch_ok >= 1 then
55 if outflg = QMXQRS_JAVA_FRAGMENT then
56 ret :=
57 xmltype.createxml('<A>'|| clb || '</A>',null,1,1).extract('/A/node()');
58 else
59 ret := xmltype.createxml(clb, null, 1,1);
60 end if;
61 end if;
62
63 /* we cache the xquery plan in qmxqrs.c level, so we don't close the
64 * handle ourselves, instead, replying on qmxqrs.c to call plan close
65 */
66 /*closeHdl(hdl);*/
67 return ret;
68
69 end;
70
71 /* return a sequence */
72 FUNCTION exec_seq(hdl in number) return sys.xmltype is
73 fetch_ok number;
74 str varchar2(4000);
75 clb clob := ' ';
76 xval xmltype;
77 ret xmltype := null;
78 outflg number := 0;
79 xqtype number := QMTXT_INVALIDTYPE;
80 noDocWrap number := 0;
81 begin
82
83 loop
84
85 /* initialize loop variables */
86 outflg := 0;
87 xqtype := QMTXT_INVALIDTYPE;
88 noDocWrap := 0;
89
90 fetch_ok := fetchOne(hdl, clb, outflg, str, xqtype);
91 if fetch_ok = 0 then exit; end if;
92
93 if str is not null then
94 /* sync with OXQServerJava.getOutAtomicType() */
95 /*
96 dbms_output.put_line('xqtype = ' || to_char(xqtype));
97 dbms_output.put_line('outflg = ' || to_char(outflg));
98 */
99 if xqtype = QMTXT_STRING then
100 /*DTYCHR; QMTXT_STRING;*/
101 select SYS_XQ_PKSQL2XML(str, 1, 2) into xval from dual;
102 elsif xqtype = QMTXT_DECIMAL then
103 /*DTYNUM; QMTXT_DECIMAL;*/
104 select SYS_XQ_PKSQL2XML(to_number(str), 2, 4) into xval from dual;
105 elsif xqtype = QMTXT_INTEGER then
106 /*DTYNUM; QMTXT_INTEGER;*/
107 select SYS_XQ_PKSQL2XML(to_number(str), 2, 33) into xval from dual;
108 elsif xqtype = QMTXT_DATE then
109 /*DTYSTZ; QMTXT_DATE;*/
110 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,10,0,0,181), 181, 10)
111 into xval
112 from dual;
113 elsif xqtype = QMTXT_TIME then
114 /*DTYSTZ; QMTXT_TIME;*/
115 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,9,0,0,181), 181, 9)
116 into xval
117 from dual;
118 elsif xqtype = QMTXT_DATETIME then
119 /*DTYSTZ; QMTXT_DATETIME;*/
120 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,8,0,0,181), 181, 8)
121 into xval
122 from dual;
123 elsif xqtype = QMTXT_FLOAT then
124 /*DTYIBFLOAT; QMTXT_FLOAT;*/
125 select SYS_XQ_PKSQL2XML(to_binary_float(str), 100, 5) into xval from dual;
126 elsif xqtype = QMTXT_DOUBLE then
127 /*DTYIBDOUBLE; QMTXT_DOUBLE;*/
128 select SYS_XQ_PKSQL2XML(to_binary_double(str), 101, 6) into xval from dual;
129 elsif xqtype = QMTXT_XDT_YEARMONTHDURATION then
130 /*DTYEIYM; QMTXT_XDT_YEARMONTHDURATION;*/
131 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,52,0,0,189), 189, 52) into xval from dual;
132 elsif xqtype = QMTXT_XDT_DAYTIMEDURATION then
133 /*DTYEIDS; QMTXT_XDT_DAYTIMEDURATION;*/
134 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,51,0,0,190), 190, 51) into xval from dual;
135 elsif xqtype = QMTXT_BOOLEAN then
136 /*DTYBIN; QMTXT_BOOLEAN;*/
137 select SYS_XQ_PKSQL2XML(case SYS_XQ_ATOMCNVCHK(str,1,3) WHEN '0' THEN HEXTORAW('00') ELSE HEXTORAW('01') END, 23, 3) into xval from dual;
138 elsif xqtype = QMTXT_GDAY then
139 /*DTYSTZ; QMTXT_GDAY;*/
140 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,11,0,0,181), 181, 11) into xval from dual;
141 elsif xqtype = QMTXT_GMONTH then
142 /*DTYSTZ; QMTXT_GMONTH;*/
143 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,12,0,0,181), 181, 12) into xval from dual;
144 elsif xqtype = QMTXT_GYEAR then
145 /*DTYSTZ; QMTXT_GYEAR;*/
146 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,13,0,0,181), 181, 13) into xval from dual;
147 elsif xqtype = QMTXT_GYEARMONTH then
148 /*DTYSTZ; QMTXT_GYEARMONTH;*/
149 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,14,0,0,181), 181, 14) into xval from dual;
150 elsif xqtype = QMTXT_GMONTHDAY then
151 /*DTYSTZ; QMTXT_GMONTHDAY;*/
152 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,15,0,0,181), 181, 15) into xval from dual;
153 elsif xqtype = QMTXT_DURATION then
154 /*DTYCHR; QMTXT_DURATION;*/
155 select SYS_XQ_PKSQL2XML(str, 1,7) into xval from dual;
156 elsif xqtype = QMTXT_XDT_UNTYPEDATOMIC then
157 /*DTYCHR; QMTXT_XDT_UNTYPEDATOMIC;*/
158 select SYS_XQ_PKSQL2XML(str, 1,50) into xval from dual;
159 elsif xqtype = QMTXT_BASE64BINARY then
160 /*DTYBIN; QMTXT_BASE64BINARY ;*/
161 select SYS_XQ_PKSQL2XML(SYS_XMLCONV(str,3,17,0,0,23), 23, 17) into xval from dual;
162 elsif xqtype = QMTXT_HEXBINARY then
163 /*DTYBIN; QMTXT_HEXBINARY ;*/
164 select SYS_XQ_PKSQL2XML(HEXTORAW(str), 23, 16) into xval from dual;
165 elsif xqtype = QMTXT_NONPOSITIVEINTEGER then
166 /*DTYNUM; QMTXT_NONPOSITIVEINTEGER ;*/
167 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,34), 2, 34) into xval from dual;
168 elsif xqtype = QMTXT_NEGATIVEINTEGER then
169 /*DTYNUM; QMTXT_NEGATIVEINTEGER ;*/
170 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,35), 2, 35) into xval from dual;
171 elsif xqtype = QMTXT_LONG then
172 /*DTYNUM; QMTXT_LONG ;*/
173 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,36), 2, 36) into xval from dual;
174 elsif xqtype = QMTXT_INT then
175 /*DTYNUM; QMTXT_POSITIVEINTEGER ;*/
176 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,37), 2, 37) into xval from dual;
177 elsif xqtype = QMTXT_SHORT then
178 /*DTYNUM; QMTXT_SHORT ;*/
179 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,38), 2, 38) into xval from dual;
180 elsif xqtype = QMTXT_BYTE then
181 /*DTYNUM; QMTXT_BYTE ;*/
182 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,39), 2, 39) into xval from dual;
183 elsif xqtype = QMTXT_NONNEGATIVEINTEGER then
184 /*DTYNUM; QMTXT_NONNEGATIVEINTEGER ;*/
185 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,40), 2, 40) into xval from dual;
186 elsif xqtype = QMTXT_UNSIGNEDLONG then
187 /*DTYNUM; UNSIGNEDLONG ;*/
188 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,41), 2, 41) into xval from dual;
189 elsif xqtype = QMTXT_UNSIGNEDINT then
190 /*DTYNUM; UNSIGNEDINT ;*/
191 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,42), 2, 42) into xval from dual;
192 elsif xqtype = QMTXT_UNSIGNEDSHORT then
193 /*DTYNUM; UNSIGNEDSHORT ;*/
194 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,43), 2, 43) into xval from dual;
195 elsif xqtype = QMTXT_UNSIGNEDBYTE then
196 /*DTYNUM; UNSIGNEDBYTE ;*/
197 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,44), 2, 44) into xval from dual;
198 elsif xqtype = QMTXT_POSITIVEINTEGER then
199 /*DTYNUM; QMTXT_POSITIVEINTEGER ;*/
200 select SYS_XQ_PKSQL2XML(SYS_XQ_ATOMCNVCHK(to_number(str),2,45), 2, 45) into xval from dual;
201 else
202 /*DTYCHR; QMTXT_STRING;*/
203 select SYS_XQ_PKSQL2XML(str, 1, 2) into xval from dual;
204 end if;
205 else
206 if bitand(outflg, QMXQRS_JAVA_NO_DOCWRAP) = QMXQRS_JAVA_NO_DOCWRAP then
207 noDocWrap := 1;
208 end if;
209 if bitand(outflg, QMXQRS_JAVA_FRAGMENT) = QMXQRS_JAVA_FRAGMENT then
210 /* This make pi , comment, text, attribute node to go through this,
211 * however, attribute node can not survive from /A/node() as it
212 * becomes text node, we need standalone attribute node tran to
213 * handle this. For these case, however, noDocWrap = 1 and
214 * sys_xqcon2seq() call below will make it turn on NO_DOCWRAP flag.
215 * Also, for the case of xquery DM node constructed from
216 * document {} constructor, the xquery java engine uses
217 * DOM QMXQRS_JAVA_FRAGMENT node to represent this, however, the
218 * noDocWrap =0 in this case and xmltype.extract() returning content
219 * which has NO NO_DOCWRAP flag on, which is right for this case.
220 */
221 xval :=
222 xmltype.createxml('<A>'||clb ||'</A>',null,1,1).extract('/A/node()');
223 /*
224 select extract(xmltype.createxml('<A>'||clb ||'</A>',null,1,1),'/A/node()') into xval
225 from dual;
226 */
227 else
228 /* only document node or single element node goes to here */
229 xval := xmltype.createxml(clb,null,1,1);
230 end if;
231 clb := ' ';
232 end if;
233
234 /*dbms_output.put_line('noDocWrap = ' || to_char(noDocWrap));*/
235 if (noDocWrap = 1) then
236 /* turn on NO_DOCWRAP flag in the image for node without document node
237 * wrapper.
238 */
239 select sys_xqcon2seq(xval) into xval from dual;
240 end if;
241
242 select sys_xqconcat(ret, xval) into ret from dual;
243
244 end loop;
245
246 /* we cache the xquery plan in qmxqrs.c level, so we don't close the
247 * handle ourselves, instead, replying on qmxqrs.c to call plan close
248 */
249 /*closeHdl(hdl); */
250 return ret;
251 end;
252
253 /* for XMLEXISTS(), we just want to make sure result is NOT empty sequnce*/
254 FUNCTION exec_exists(hdl in number, retseq in number) return number is
255 fetch_ok number;
256 str varchar2(4000);
257 clb clob := ' ';
258 xval xmltype;
259 ret number := 0;
260 outflg number := QMXQRS_JAVA_CHK_EXSTS; /* pass on flag for XMLEXISTS check*/
261 xqtype number := 0;
262 begin
263
264
265 fetch_ok := fetchOne(hdl, clb, outflg, str, xqtype);
266 if fetch_ok = 0 then
267 ret := 0;
268 else
269 ret := 1;
270 end if;
271
272 /* we cache the xquery plan in qmxqrs.c level, so we don't close the
273 * handle ourselves, instead, replying on qmxqrs.c to call plan close
274 */
275 /*closeHdl(hdl);*/
276 return ret;
277 end;
278
279 FUNCTION exec(hdl in number, retseq in number)
280 return sys.xmltype is
281 begin
282 if retseq = 1 then
283 return exec_seq(hdl);
284 else
285 return exec_cont(hdl);
286 end if;
287 end;
288
289 FUNCTION getXQueryX(xqry in varchar2) return clob as LANGUAGE JAVA NAME
290 'oracle.xquery.OXQServer.getXQueryX(java.lang.String) return oracle.sql.CLOB';
291
292 FUNCTION getXQueryXxclb(xqry in clob) return clob as LANGUAGE JAVA NAME
293 'oracle.xquery.OXQServer.getXQueryX(oracle.sql.CLOB) return oracle.sql.CLOB';
294
295 FUNCTION execallCmn(xqry in varchar2, nlssrt in varchar2, nlscmp in varchar2,
296 dbchr in varchar2, retseq in number, flags in number, xqryclb in clob,
297 xqisclob in number, hdl in out number)
298 return sys.xmltype is
299 --hdl number;
300 begin
301 if (hdl = 0) then
302 /* xquery plan has not been built, so let's build it */
303 if xqisclob = 1 then
304 hdl := preparexclb(xqryclb, nlssrt, nlscmp, dbchr, flags);
305 else
306 hdl := prepare(xqry, nlssrt, nlscmp, dbchr, flags);
307 end if;
308 end if;
309
310 execQuery(hdl);
311 return exec(hdl, retseq);
312 end;
313
314 FUNCTION execallCmn_exists(xqry in varchar2, nlssrt in varchar2,
315 nlscmp in varchar2,
316 dbchr in varchar2, retseq in number, flags in number, xqryclb in clob,
317 xqisclob in number, hdl in out number)
318 return number is
319 --hdl number;
320 begin
321 if (hdl = 0) then
322 /* xquery plan has not been built, so let's build it */
323 if xqisclob = 1 then
324 hdl := preparexclb(xqryclb, nlssrt, nlscmp, dbchr, flags);
325 else
326 hdl := prepare(xqry, nlssrt, nlscmp, dbchr, flags);
327 end if;
328 end if;
329
330 execQuery(hdl);
331 return exec_exists(hdl, retseq);
332 end;
333
334 FUNCTION execall(xqry in varchar2, nlssrt in varchar2, nlscmp in varchar2,
335 dbchr in varchar2, retseq in number, flags in number, hdl in out number)
336 return sys.xmltype is
337 begin
338 return execallCmn(xqry, nlssrt, nlscmp, dbchr, retseq, flags, null, 0, hdl);
339 end;
340
341 FUNCTION execallxclb(xqryclb in clob, nlssrt in varchar2, nlscmp in varchar2,
342 dbchr in varchar2, retseq in number, flags in number, hdl in out number)
343 return sys.xmltype parallel_enable is
344 begin
345 return execallCmn(null, nlssrt, nlscmp, dbchr, retseq, flags, xqryclb, 1, hdl);
346 end;
347
348 FUNCTION execall_exists(xqry in varchar2, nlssrt in varchar2,
349 nlscmp in varchar2,
350 dbchr in varchar2, retseq in number, flags in number,
351 hdl in out number)
352 return number is
353 begin
354 return execallCmn_exists(xqry, nlssrt, nlscmp, dbchr, retseq, flags, null, 0, hdl);
355 end;
356
357 FUNCTION execallxclb_exists(xqryclb in clob, nlssrt in varchar2,
358 nlscmp in varchar2,
359 dbchr in varchar2, retseq in number, flags in number,
360 hdl in out number)
361 return number is
362 begin
363 return execallCmn_exists(null, nlssrt, nlscmp, dbchr, retseq, flags, xqryclb, 1, hdl);
364 end;
365
366 /* testing function */
367 FUNCTION executeCmn(xqry in varchar2, xctx in xmltype:=null, retseq in number := 0, xqryclb in clob, xqisclob in number)
368 return sys.xmltype parallel_enable is
369 a number := 0;
370 dbchr varchar2(30);
371 nlscmp varchar2(30);
372 nlssrt varchar2(30);
373 hdl number;
374 begin
375
376 select value into dbchr from v$nls_parameters where
377 parameter = 'NLS_CHARACTERSET';
378 select value into nlssrt from v$nls_parameters where
379 parameter = 'NLS_SORT';
380 select value into nlscmp from v$nls_parameters where
381 parameter = 'NLS_COMP';
382
383 if xqisclob = 1 then
384 hdl := preparexclb(xqryclb, nlssrt, nlscmp, dbchr, 0);
385 else
386 hdl := prepare(xqry, nlssrt, nlscmp, dbchr, 0);
387 end if;
388
389 if xctx is not null then
390 if xctx.isFragment() = 1 then
391 a := QMXQRS_JAVA_FRAGMENT;
392 end if;
393 bind(hdl, null, a, xctx.getclobval(), xctx.getSchemaURL());
394 end if;
395
396 execQuery(hdl);
397 return exec(hdl, retseq);
398
399 end;
400
401 FUNCTION execute(xqry in varchar2, xctx in xmltype:=null, retseq in number := 0)
402 return sys.xmltype parallel_enable is
403 begin
404 return executeCmn(xqry, xctx, retseq, null, 0);
405 end;
406
407 function executexclb(xqry in clob, xctx in xmltype := null,
408 retseq in number := 0)
409 return sys.xmltype parallel_enable is
410 begin
411 return executeCmn(null, xctx, retseq, xqry, 1);
412 end;
413
414 end;
[Home] [Help]