This is environment for my client is 32 bit Sqlplus on Windows:
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 22 10:31:59 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
My database is on Solaris, 64 bit:
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I am having a simple, I hope, problem in that I cannot seem to find a way to incorporate passing a parameter to an xmlexists clause within a plsql block. Normally I would just use plain sql, but in this particular situation I have a corrupt xml document that kills my Sql statement, so I am looping through the source xmltable (Caml_Measure_Doc_Tab is a schema register xml table within Xdb) to process each document. Here is a simplified test case:
DECLARE
vCamlDoc_Id VARCHAR2(30) := 'RN20050000108';
TYPE CamlDoc_Rec IS RECORD
(
Caml_Doc_Id VARCHAR2(30),
Request_Nr VARCHAR2(30),
Version NUMBER(3,0),
Session_Year NUMBER(4,0)
);
Caml_Doc_Typ CamlDoc_Rec;
CURSOR C1_Rc (pCamlDocId IN VARCHAR2) IS
SELECT Caml.Caml_Doc_Id,
Caml.Request_Num,
Caml.Version_Num,
Caml.Session_Year
FROM
Legalservices.Caml_Measure_Doc_Tab MTab,
XmlTable
(
XMLNAMESPACES('http://lc.ca.gov/legalservices/schemas/caml.1#' AS "caml",
'http://xmlns.oracle.com/xdb/XDBResource.xsd' AS "res",
'http://www.w3.org/2001/XMLSchema' AS "rdf",
'http://xmlns.oracle.com/xdb' AS "xdb"),
'for $i in /caml:MeasureDoc/caml:Description
return $i'
PASSING OBJECT_VALUE
COLUMNS
Caml_Doc_Id VARCHAR2(30) PATH 'caml:Id',
Request_Num VARCHAR2(25) PATH 'caml:RequestNum',
Version_Num NUMBER(3,0) PATH 'caml:VersionNum',
Session_Year NUMBER(4) PATH 'substring(caml:Id, 3, 4)'
) Caml
WHERE XMLEXISTS ('declare namespace res="http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
declare namespace caml="http://lc.ca.gov/legalservices/schemas/caml.1#"; (: :)
declare namespace rdf="http://www.w3.org/2001/XMLSchema"; (: :)
declare namespace xdb="http://xmlns.oracle.com/xdb"; (: :)
/caml:MeasureDoc/caml:Description[caml:Id = "RN20050000108"]'
PASSING OBJECT_VALUE);
BEGIN
OPEN C1_Rc (vCamlDoc_Id);
FETCH C1_Rc INTO Caml_Doc_Typ;
CLOSE C1_Rc;
DBMS_OUTPUT.Put_Line (CHR(10));
DBMS_OUTPUT.PUT_LINE ('****************************************');
DBMS_OUTPUT.PUT_LINE ('*** Doc Id: '||Caml_Doc_Typ.Caml_Doc_Id);
DBMS_OUTPUT.PUT_LINE ('*** Request Nr: '||Caml_Doc_Typ.Request_Nr);
DBMS_OUTPUT.PUT_LINE ('*** Version: '||TO_CHAR(Caml_Doc_Typ.Version, 'FM99'));
DBMS_OUTPUT.PUT_LINE ('*** Session Year: '||TO_CHAR(Caml_Doc_Typ.Session_Year, 'FM9999'));
DBMS_OUTPUT.PUT_LINE ('****************************************');
END;
/
Above I hard coded the identifying element (caml_Doc_id) within the XmlExists, but I would really like to make that a parameter for my ref cursor. Is that possible? I have seem references where a DEFINE works, but I need either set a VARIABLE or just pass my parameter so that I can loop on my xml schema registered table.