Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: xml / xsql possible setup problem

Re: xml / xsql possible setup problem

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Mon, 30 Aug 2004 08:22:32 -0700 (PDT)
Message-ID: <20040830152232.63933.qmail@web42003.mail.yahoo.com>


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_at_vrijuit.nl> 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_at_freelists.org 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_at_freelists.org
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
-----------------------------------------------------------------
Received on Mon Aug 30 2004 - 10:20:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US