When I execute this function in my 9.2.0.7.0 database:
create or replace function get_study_xml (p_study_id in number)
return xmltype is xml_doc xmltype;
begin
execute immediate '
select xmlelement(
...lots of code...
from XML_STUDY_VIEW where STUDY_ID = '||p_study_id||'' into xml_doc;
return xml_doc;
end get_study_xml;
I get the following error with a 9.2.0.1.0 client:
ORA-03118: two-task coroutine has invalid state
And the following error with a 9.2.0.3.0 client:
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
And finally the following error with a 9.2.0.7.0 client:
ORA-24909: call in progress. Current operation cancelled
I've done lots of investigation, and so far I've deduced the following:
* It works fine on a PC with a 10g client
* The function returns the XMLType successfully (it seems)
* On all clients, the error occurs not on returning the XMLType, but on trying to read the XML within.
I am trying to do this via Delphi components from Allround Automations (makers of PL/SQL Developer). I don't know how to output the XML to DBMS Output, so could do with some help there. If I could test it without using Allround's components, I could isolate the problem.
I suspect the problem is not with the components though but with the OCI/client, because the same components work fine on 10g client.
If someone could help give some suggestions, including how I could try to output the XML in SQL*Plus, it would be greatly appreciated.
Best regards,
Gary
I don't know if it's relevent, but it may be worth noting that the SQL statement I've commented out is fairly massive, but even if I query data that produces a tiny XML object (i.e. hardly any data), it still fails. The full function is below, so you can see what I'm doing:
create or replace function get_study_xml (p_study_id in number)
return xmltype is xml_doc xmltype;
begin
execute immediate '
select xmlelement(
"STUDY",
xmlforest(STUDY_ID, '||columnsforest('XML_STUDY_VIEW')||')
,(
select xmlagg(
xmlelement(
"STUDYHEADING",
xmlforest('||columnsforest('XML_STUDYHEADING_VIEW')||')
)
)
from XML_STUDYHEADING_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlelement(
"TESTSUBSTANCE",
xmlforest('||columnsforest('XML_TESTSUBSTANCE_VIEW')||')
)
from XML_TESTSUBSTANCE_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlagg(
xmlelement(
"STRAIN",
xmlforest('||columnsforest('XML_STRAIN_VIEW')||')
)
)
from XML_STRAIN_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlagg(
xmlelement(
"POSITIVECONTROL",
xmlforest('||columnsforest('XML_POSCTRL_VIEW')||')
)
)
from XML_POSCTRL_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlagg(
xmlelement(
"SOLVENT",
xmlforest('||columnsforest('XML_SOLVENT_VIEW')||')
)
)
from XML_SOLVENT_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlagg(
xmlelement(
"POSTFIX",
xmlforest('||columnsforest('XML_POSTFIX_VIEW')||')
)
)
from XML_POSTFIX_VIEW where STUDY_ID = '||p_study_id||'
),(
select xmlagg(
xmlelement(
"EXPERIMENTFOLDER",
xmlforest(FOLDER_ID,'||columnsforest('XML_EXPTFOLDER_VIEW')||'),(
select xmlagg(
xmlelement(
"EXPERIMENT",
xmlforest(EXPT_ID,'||columnsforest('XML_EXPERIMENT_VIEW')||'),(
select xmlagg(
xmlelement(
"EXPERIMENTHEADING",
xmlforest('||columnsforest('XML_EXPTHEADING_VIEW')||')
)
)
from XML_EXPTHEADING_VIEW h where h.EXPT_ID = e.EXPT_ID
),(
select xmlagg(
xmlelement(
"EXPERIMENTSTRAIN",
xmlforest('||columnsforest('XML_EXPTSTRAIN_VIEW')||'),(
select xmlagg(
xmlelement(
"PLATE",
xmlforest('||columnsforest('XML_EXPTPLATE_VIEW')||'),(
select xmlagg(
xmlelement("POSTFIX",CODE_ID)
)
from XML_PLATEPOSTFIX_VIEW x where x.PLATE_ID = p.PLATE_ID
and x.FOLDER_ID = f.FOLDER_ID
)
)
)
from XML_EXPTPLATE_VIEW p where p.EXPT_ID = e.EXPT_ID
and p.STRAIN_CODE = s.STRAIN_CODE
and p.PLATE_TYPE = 0
)
)
)
from XML_EXPTSTRAIN_VIEW s where s.EXPT_ID = e.EXPT_ID
)
)
)
from XML_EXPERIMENT_VIEW e where e.STUDY_ID = '||p_study_id||'
and f.folder_id = e.folder_id
),(
select xmlagg(
xmlelement(
"CONTROL",
xmlforest('||columnsforest('XML_CONTROL_VIEW')||'),(
select xmlagg(
xmlelement(
"CONTROLPLATE",
xmlforest('||columnsforest('XML_CONTROLPLATE_VIEW')||'),(
select xmlagg(
xmlelement("POSTFIX",CODE_ID)
)
from XML_PLATEPOSTFIX_VIEW x where x.PLATE_ID = p.PLATE_ID
and x.FOLDER_ID = f.FOLDER_ID
)
)
)
from XML_CONTROLPLATE_VIEW p where p.CONTROL_ID = c.CONTROL_ID
and p.PLATE_TYPE > 0
)
)
)
from XML_CONTROL_VIEW c where c.STUDY_ID = '||p_study_id||'
and f.folder_id = c.folder_id
)
)
)
from XML_EXPTFOLDER_VIEW f where f.STUDY_ID = '||p_study_id||'
)
)
from XML_STUDY_VIEW where STUDY_ID = '||p_study_id||'' into xml_doc;
return xml_doc;
end get_study_xml;