Database Software

Hi All,

I have the following SP that i am trying to run:

CREATE OR REPLACE PROCEDURE TestProcXML is

xmlStore DBMS_XMLGEN.ctxHandle;
pxml xmlType;

BEGIN

xmlStore := DBMS_XMLGEN.newcontextFromHierarchy(
'select XMLElement(METAANALYSIS, XMLAgg(XMLElement(RESULTSET, XMLElement(PKPDDOSINGFACT, XMLForest(pkpd.DRUG_ID , pkpd.PKPD_TIMEPOINT , pkpd.TIME_1 , pkpd.TIME_1_UNITS , pkpd.TIME_2 ,pkpd.TIME_2_UNITS , pkpd.CONCENTRATION , pkpd.CONCENTRATION_UNITS , pkpd.CONCENTRATION_DESCRIPTION , pkpd.CONCENTRATION_VARIANCE , pkpd.CONCENTRATION_VAR_UNITS , pkpd.BASELINE_RESPONSE_CONC , pkpd.BASELINE_RESPONSE_CONC_UNITS , pkpd.BASELINE_RESPONSE_CONC_DESCRIP , pkpd.BASELINE_RESPONSE_CONC_VARIANC , pkpd.BASELINE_RESPONS_CONC_VAR_UNIT , pkpd.ENDPOINT_SUBSCALE , pkpd.RESPONSE , pkpd.RESPONSE_UNITS , pkpd.RESPONSE_DESCRIPTION , pkpd.RESPONSE_VARIANCE , pkpd.RESPONSE_VARIANCE_UNITS , pkpd.CHANGE_FROM_BASELINE , pkpd.CHANGE_FROM_BASELINE_UNITS , pkpd.CHANGE_FROM_BASELINE_DESCRIPTI , pkpd.CHANGE_FROM_BASELINE_VARIANCE , pkpd.CHANGE_FROM_BASELINE_VAR_UNITS , pkpd.MODELLING_DONE_INDICATOR , pkpd.MODELLING_REFERENCES , pkpd.DOSE_MEAN , pkpd.DOSE_UNITS , pkpd.DOSE_VARIANCE , pkpd.DOSE_VARIANCE_UNITS , pkpd.LOADING_DOSE , pkpd.LOADING_DOSE_UNITS , pkpd.FEDFAST , pkpd.DOSE_FREQUENCY , pkpd.ROUTE_OF_ADMINISTRATION , pkpd.FORMULATION , pkpd.DOSING_METHOD , pkpd.ADD_ON_THERAPY , pkpd.WASHOUT_PERIOD_INDICATOR , pkpd.WASHOUT_PERIOD_DURATION , pkpd.WHEN_LAST_UPDATED , pkpd.LAST_UPDATED_BY , pkpd.LS_ID , pkpd.ARTIFACT_VERSION_ID , pkpd.EXTENSION_POINT , pkpd.DELETION_STATUS_CODE , pkpd.VERSION_NUMBER , pkpd.LITERATURE_ID , pkpd.LOCATION_IN_LITERATURE , pkpd.DRUG_TRADE_NAME , pkpd.GENERAL_COMMENTS , pkpd.DEVICE_NAME , pkpd.STUDY_ID , pkpd.STUDY_GROUP_ID , pkpd.DOSING_PERIOD , pkpd.ENDPOINT_ID)), XMLElement(CLINICALSTUDY, XMLForest(cs.STUDY_ID , cs.STUDY_IDENTIFIER , cs.ORGANIZATION_NAME , cs.STUDY_NAME , cs.STUDY_OBJECTIVES , cs.STUDY_ACRONYMS , cs.STUDY_BLINDED_INDICATOR , cs.STUDY_BLINDED_DESCRIPTION , cs.STUDY_RANDOMIZED_INDICATOR , cs.STUDY_STRATIFIED_INDICATOR , cs.STUDY_STRATIFICATION_VARIABLES , cs.STUDY_CONTROL_TYPE , cs.STUDY_DESIGN , cs.STUDY_DESIGN_DETAILS , cs.STUDY_ADAPTIVE_DESIGN_INDICATO , cs.STUDY_ADAPTIVE_DESIGN_DETAILS , cs.STUDY_INCLUSION_CRITERIA , cs.STUDY_EXCLUSION_CRITERIA , cs.DISEASE_CLASS_TEXT , cs.DISEASE_DURATION , cs.PLANNED_TREATMENT_DURATION , cs.LS_ID , cs.ARTIFACT_VERSION_ID , cs.VERSION_NUMBER)), XMLElement(DEMOGRAPHICS,XMLForest(dm.STUDY_ID , dm.STUDY_GROUP_ID , dm.POPULATION_GROUP_TEXT , dm.PATIENT_POPULATION_TEXT , dm.AGE_MEAN , dm.WEIGHT_MEAN , dm.HEIGHT_MEAN , dm.MALE_PERCENTAGE , dm.PATIENT_TYPE , dm.NAIVE_PERCENTAGE , dm.CAUCASIAN_PERCENTAGE , dm.SMOKERS_PERCENTAGE , dm.DRINKERS_PERCENTAGE , dm.BODY_MASS_INDEX_MEAN , dm.HEART_RATE_MEAN , dm.SYSTOLLIC_BLOOD_PRESSURE_MEAN , dm.DIASTOLLIC_BLOOD_PRESSURE_MEAN , dm.GENOTYPE_INDICATOR , dm.GENOTYPE_DESCRIPTION , dm.TOTAL_NUMBER_OF_SUBJECTS , dm.NUMBER_OF_SUBJECTS_PER_TREATME , dm.OBSERVED_NUMBER_OF_SUBJECTS , dm.NUMBER_OF_LOCF , dm.SUBJECT_LEVEL_DATA_INDICATOR , dm.STATISTICAL_ANALYSIS_METHOD , dm.COMPLETE_PERCENTAGE , dm.ADVERSE_EVENT_PERCENTAGE , dm.ADVERSE_EVENT_DROP_OUT_PERCENT , dm.ADVERSE_EVENTS_COMMENTS , dm.SUMMARY_TEXT , dm.COUNTRY_CODE , dm.LS_ID , dm.ARTIFACT_VERSION_ID , dm.VERSION_NUMBER)), XMLElement(DRUG, XMLForest(dr.GENERIC_NAME)),XMLElement(ENDPOINT, XMLForest(ep.ENDPOINT_NAME,ep.ENDPOINT_ACRONYM, ep.ENDPOINT_DEFINITION)), XMLElement(CATEGORICALVARIABLE, ( select XMLAgg(XMLElement(SUBCATEGORICALVARIABLE,XMLForest(cvs.CATEGORICAL_VAR_NAME,cvs.CATEGORICAL_VAR_VALUE))) from CATEGORICAL_VARIABLE cvs where cvs.STUDY_ID = pkpd.STUDY_ID and cvs.STUDY_GROUP_ID = pkpd.STUDY_GROUP_ID and cvs.PERIOD = pkpd.DOSING_PERIOD)), XMLElement(CONCOMITANTMEDICATION, (select XMLAgg(XMLElement(SUBCONCOMITANTMEDICATION, XMLForest(cm.DOSE_MEAN, cm.DOSE_MEAN_UNITS))) from CONCOMITANT_MEDICATION cm where cm.STUDY_ID = pkpd.STUDY_ID and cm.STUDY_GROUP_ID = pkpd.STUDY_GROUP_ID and cm.PERIOD = pkpd.DOSING_PERIOD)), XMLElement(MEDICALHISTORYFACT, (select XMLAGG(XMLELEMENT(SUBMEDICALHISTORY, XMLForest(mt.MEDICAL_TERM_DESCRIPTION, mhf.OCCURRING_IN_POPULATION_PERCEN))) from MEDICAL_HISTORY_FACT mhf, MEDICAL_TERM mt where mt.MEDICAL_TERM_ID = mhf.MEDICAL_TERM_ID and mhf.STUDY_ID = pkpd.STUDY_ID and mhf.STUDY_GROUP_ID = pkpd.STUDY_GROUP_ID and mhf.DOSING_PERIOD = pkpd.DOSING_PERIOD))))) from clinical_study cs, PKPD_DOSING_FACT pkpd, demographics dm , drug dr, endpoint ep where cs.STUDY_ID = pkpd.STUDY_ID and pkpd.DRUG_ID = dr.DRUG_ID and pkpd.ENDPOINT_ID = ep.ENDPOINT_ID and dm.STUDY_GROUP_ID = pkpd.STUDY_GROUP_ID and dm.STUDY_ID = pkpd.STUDY_ID and pkpd.STUDY_GROUP_ID in (select pkpd.STUDY_GROUP_ID from PKPD_dosing_fact pkpd, clinical_study_indication csi, indication_therapeutic_area ita where pkpd.STUDY_ID = csi.STUDY_ID and csi.INDICATION_ID = ita.INDICATION_ID and ita.TA_ID = ''4'' )');

DBMS_XMLGEN.setRowSetTag(xmlStore, null);
DBMS_XMLGEN.SETROWTAG(xmlStore, null);

pxml := DBMS_XMLGEN.GETXMLTYPE(xmlStore, DBMS_XMLGEN.DTD);

DBMS_OUTPUT.put_line(to_char(DBMS_XMLGEN.getNumRowsProcessed(xmlStore)));

insert into xml_tbl values(pxml);

Commit;

DBMS_XMLGEN.CLOSECONTEXT(xmlStore);

END TestProcXML;
/

There are couple of problems:
--------------------------------------------------------------------------
1. When you run this procedure in TOAD then it throws the following exception:
ORA-19038: Invalid operation on query context
ORA-06512: at "SYS.DBMS_XMLGEN", line 70
ORA-06512: at
"LIKE_OWNER.TESTPROCXML", line 12
ORA-06512: at line 2

2. If i replace newcontextfromhierarchy with newcontext above then the procedure executes properly. The XML that is produced contains a very weird tag at the top:

<XMLELEMENT_x0028_METAANALYSIS_x002C_XMLAGG_x0028_XMLELEMENT_x0028_RESULTSET_x002C_XMLELEMENT_x0028_PKPDDOSINGFACT_x002C_XMLFOREST_x0028_PKPD.DRUG_ID_x002C_PKPD.PKPD_TIMEPOINT_x002C_PKPD.TIME_1_x002C_PKPD.TIME_1_UNITS_x002C_PKPD.TIME_2_x002C_PKPD.TIME_2_UNITS_x002C_PKPD.CONCENTRATION_x002C_PKPD.CONCENTRATION_UNITS_x002C_PKPD.CONCENTRATION_DESCRIP>

How do you get rid of this tag?

Any help will be appreciated.

Thanks,
Madhav