xml / xsql possible setup problem 2004-08-30 - By Jeroen van Sluisdam
We encounter a strange problem in development of a new xml-based
application.
This might have something to do with setup so hopefully some of you can help
me a bit further on
the road:
I post a XML message as a request to query some data . The server reponses
well if the amount of data to respond is not too much. If amount the data
exceeds a certain limit we get an error.
Flow we use:
for development we use an html form,
request goes to the apache webserver,
calls an xsql page,
page does an insert into a table with a clob column
trigger on this table calls a procedure to process
the request - > output is prepared in a new table in xml-format
page does a query on the output table to prepare the answer
Details: Oracle 9.2.0.4 HP-UX 11.11 XDK 9.2.0.6
................
The PL-sql to prepare the response:
procedure ResponseContractsList
is
dum clob;
CURSOR cResponseContractsList
IS
select XMLELEMENT( "CBSMessage ",
XMLATTRIBUTES( 'http://www.w3.org/2001/XMLSchema ' as "xmlns:xsd " ,
'http://www.w3.org/2001/XMLSchema-instance ' as "xmlns:xsi "
, 'http://www.oracle.com/CBSResponse.xsd ' as "xsi:noNamespaceSchemaLocation "
),
XMLELEMENT( "Header ",
XMLELEMENT( "Version ", '1 '),
XMLELEMENT( "Guid ", '6BC096BB-0CE6-11D1-BAAE-00C04FC2E20D '),
XMLELEMENT( "Error ", 0)
),
XMLELEMENT( "Response ",
XMLELEMENT( "Type ", 'ResponseContractList '),
XMLELEMENT( "RequestID ", 1),
XMLELEMENT( "Error ", 0),
(select XMLAGG(
XMLELEMENT( "ResponseContractList ",
(select XMLAGG(
XMLELEMENT( "Object ",
XMLELEMENT( "ObjectID ", OBJ_CD),
XMLELEMENT( "ObjectName ", OBJ_NM),
XMLELEMENT( "ObjectRegion ", rtrim(REG_NM)),
(select XMLAGG(
XMLELEMENT( "Contract ",
XMLELEMENT( "ContractID ", CNTR_CD),
XMLELEMENT( "ContractName ", nvl(OBJ_ECD, ' ') || ' ' || OBJ_NM),
XMLELEMENT( "ContractCode ", nvl(CNTR_ECD, ' ')),
XMLELEMENT( "StartDate ", to_char(min(RSTR_DTM_TOT), 'yyyy-mm-dd ')),
XMLELEMENT( "EndDate ", to_char(max(RSTR_DTM_TOT), 'yyyy-mm-dd ')),
XMLELEMENT( "SupplierCode ", OBJ_TARA_NR)
))
from CNTR,
CNTR_OBJ,
RSTR,
MOD
where CNTR_OBJ_OBJ = OBJ_CD
and CNTR_OBJ_CNTR = CNTR_CD
and CNTR.STATUS_RIJ = '1 '
and MOD_CNTR = CNTR_MOD
and MOD_TP in (2,4)
and RSTR_MOD = MOD_CD
and MOD_ORIG_CD = CNTR_OBJ_MOD
and RSTR_DTM_TOT >= sysdate
and cntr_med=231
group by CNTR_CD, OBJ_ECD, CNTR_ECD, OBJ_TARA_NR
)
))
from contracten.OBJ,
ACC_ADR,
NAW,
REG
where OBJ_TARA_NR is not null
and ACC_ADR_OBJ = OBJ_CD
and NAW_CD = ACC_ADR_NAW
and REG_LAND = NAW_LAND
and REG_STRK = NAW_STRK
and REG_CD = NAW_REG
and rownum <61
)))
from dual
))
) as "CBSResponse "
from dual;
BEGIN
OPEN cResponseContractsList;
FETCH cResponseContractsList into waarde;
CLOSE cResponseContractsList;
delete dummy;
insert into dummy (MSG) values (waarde);
END
..........................
Will work , but if you take rownum <62 then
we get the following error
Posting the xml
<?xml version= "1.0 " encoding= "utf-8 "? >
<CBSMessage xmlns:xsd= "http://www.w3.org/2001/XMLSchema "
xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance "
xsi:noNamespaceSchemaLocation= "http://www.oracle.com/CBSrequest2.xsd " >
<Header >
<Version >1 </Version >
<Sender >CBS </Sender >
<Receiver >IRIS </Receiver >
<Guid >6BC096BB-0CE6-11D1-BAAE-00C04FC2E20D </Guid >
</Header >
<Request >
<Type >RequestContractList </Type >
<RequestID >26 </RequestID >
<Order >6 </Order >
<RequestContractList/ >
</Request >
</CBSMessage >
Leads to a
OUTPUT WHEN THERE is too much data
- <page >
<xsql-status action= "xsql:insert-request " rows= "1 " / >
- <data >
<ERROR >oracle.xml.sql.OracleXMLSQLException: End tag does not match start
tag 'ObjectRegion '. </ERROR >
</data >
</page >
................................
XSQL
<?xml version= "1.0 "? >
<page connection= "demo " xmlns:xsql= "urn:oracle-xsql " > <xsql:insert-request
table= "testxml " column= "msg " transform= "jeroeninsform.xsl "/ > <data >
<xsql:query null-indicator= "yes " > select msg from dummy
</xsql:query >
</data >
</page >
Hope you can help because we are getting desperate
Tnx,
Jeroen
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
