xml / xsql possible setup problem 2004-08-30 - By M.Godlewski
Jeroen,
Have you run a trace on your session to see what is happening in the rownum < 61 query that is successful and what is different in the rownum < 62?
Secondly,
Have you tried to connecting directly to sqlplus to run the procedure and direct the output to the screen?
Jeroen van Sluisdam <jeroen.van.sluisdam@(protected) > wrote:
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
xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance "
xsi:noNamespaceSchemaLocation= "http://www.oracle.com/CBSrequest2.xsd " >
1
CBS
IRIS
6BC096BB-0CE6-11D1-BAAE-00C04FC2E20D
RequestContractList
26
6
Leads to a
OUTPUT WHEN THERE is too much data
-
-
oracle.xml.sql.OracleXMLSQLException: End tag does not match start
tag 'ObjectRegion '.
................................
XSQL
table= "testxml " column= "msg " transform= "jeroeninsform.xsl "/ >
select msg from dummy
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- -----
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
