| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Xml query in Oracle: stored proc (apparently) killing the Oracle server
Hello everyone,
We are using Oracle as a database, and have started to develop a website in .net designed to present data leveraged from Oracle to users.
At one point, we have to get a bunch of indicators regarding the current status of a user advancement. We decided, since that data may be used on many platforms (user interface, website, project leader) to expose that data as xml coming from a webservice. So far so good. We wrote the first draft of a stored proc in Oracle that would bring back the data as a hierarchical xml, neatly organized in relevant sections. Here is the first draft of the stored proc. We bring back the result in a CLOB, as you can see:
PROCEDURE P_XML_INDICATEUR (
ARG_NUMSEQ_AYD_N IN AYD.NUMSEQ_AYD_N%TYPE,
XML_CLOB OUT CLOB
) IS
BEGIN
select TO_CLOB(XMLELEMENT("INDICATEURS",
XMLELEMENT( "CLIENTS",
(select SYS_XMLAGG(
XMLELEMENT( "CLIENT",XMLAttributes('GLOBAL' "CODE"),
XMLELEMENT( "PHONOGRAMMES",
(select SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE NUMSEQ_AYD_N is null
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'PHO%'
)
),
XMLELEMENT( "VENTES",
(select
SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE IDS2.NUMSEQ_AYD_N =
IDS1.NUMSEQ_AYD_N
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'VTE%'
)
),
XMLELEMENT( "REPARTITIONS",
(select
SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE IDS2.NUMSEQ_AYD_N =
IDS1.NUMSEQ_AYD_N
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'VTE%'
)
)
)
)
FROM INDICATEUR_SUIVI IDS1
WHERE IDS1.NUMSEQ_AYD_N is null
),
(select SYS_XMLAGG(
XMLELEMENT( "CLIENT",XMLAttributes(NUMSEQ_AYD_N "CODE"),
XMLELEMENT( "PHONOGRAMMES",
(select SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE IDS2.NUMSEQ_AYD_N =
IDS1.NUMSEQ_AYD_N
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'PHO%'
) ),
XMLELEMENT( "VENTES",
(select
SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE IDS2.NUMSEQ_AYD_N =
IDS1.NUMSEQ_AYD_N
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'VTE%'
)
),
XMLELEMENT( "REPARTITIONS",
(select
SYS_XMLAGG(
XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
)
)
FROM INDICATEUR_SUIVI IDS2,
INDICATEUR_META IDM2
WHERE IDS2.NUMSEQ_AYD_N =
IDS1.NUMSEQ_AYD_N
AND
IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
AND IDM2.NOM_METADONNEE_C
like 'VTE%'
)
)
)
)
FROM INDICATEUR_SUIVI IDS1
WHERE IDS1.NUMSEQ_AYD_N =
ARG_NUMSEQ_AYD_N
)
)
) INTO XML_CLOB
-- WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20002, 'Erreur dans la procedure P_XML_INDICATEUR' || TO_CHAR(SQLCODE) || ' ' || SQLERRM); -- END P_XML_INDICATEUR; This function worked correctly the first time around. The xml came back to our webservice, and we consumed it without any problem. However, one of us noticed the presence of a copy-paste bug in the procedure and changed the second occurence of AND IDM2.NOM_METADONNEE_C like 'VTE%' into AND IDM2.NOM_METADONNEE_C like 'REP%' What seemed like a harmless change at the time turned out to never return at all. We first realized the webservice timed out, then launched the proc in toad to see where the culprit was hiding. It turned out that the process was running on the oracle server, unkillable, doing something (actually, to be precise, the procedure wasn't even fetching the results yet, but was being analysed/run) , and never returned while we were waiting to see what was happenning. The memory consumed by Oracle was slowly growing, the cpu was less and less responsive, until eventually the machine was rebooted manually since nothing was running on it anymore. We tried replicating the problem by making sure the data was correct (i suggested perhaps an empty recordset in one of the inner select was causing the block) but encountered the same scenario so far. I don't have access yet to the error logs, and will go through them as soon as i can, but i wanted if this problem was already encountered by someone else. For information, we are using Oracle 9.2, and both tables mentioned in the queries have a primary key, a not null constraint, and INDICATEUR_META has a foreign key reference to the primary key of INDICATEUR_SUIVI Thank you for reading this lenghty text. Thank you even more if you are a light at the end of the tunnel :) SamyReceived on Thu Feb 22 2007 - 08:12:28 CST
 |
 |