Yes we tried ofcourse to do select from the output table manually and this
works fine.
Forgot to do a trace
And I just did with remarkable result that
I See a dump in the trace file in the case of rownum < 62 which gives me
more indication
that we pass some kind of limit.Any idea what this means?
ksedmp: internal or fatal error
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [qmxsqLoadNS], [], [], [], [],
[], [], []
Current SQL statement for this session:
insert into testxml (msgtext,version) values (:1,:2)
-- -- PL/SQL Call Stack -- --
object line object
handle number name
c000000014ca4080 0 SYS.XMLTYPE
c000000014cc8580 17 VRIJ_UIT.TESTXMLTRIG
-- -- Call Stack Trace -- --
calling call entry argument values in hex
-- --Oorspronkelijk bericht-- --
Van: M.Godlewski [mailto:mcgodlewski@(protected)]
Verzonden: Monday, August 30, 2004 17:23
Aan: oracle-l@(protected)
Onderwerp: Re: xml / xsql possible setup problem
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
