I have a basic question on XMLAgg usage on an Oracle Database 10g Release 10.2.0.4.0 - 64bit Production database. We are trying to run SQL scripts which have similar XML built-ins but these do not seem to work whereas the same is working on the vendor's database with a setting. We have checked internally our setting is also matching the vendor's Database setting of _optim_peek_user_binds = false
Is there anything else we need to try ?
CREATE OR REPLACE VIEW "VD_INSP_INSPECTION_INFO" ("INSPECTION_ID", "FACILITY_ID", "TYPE_CD", "FIELD_ID", "FIELD_VALUE", "FIELD_TAG", "TASK_TEMPLATE_ID", "WRK_TASK_ID", "ID", "SECTION_TITLE", "CTRL_LABEL", "TASK_TEMPLATE", "FORM_NAME") AS
select P."INSPECTION_ID", P."FACILITY_ID", P."TYPE_CD", P."FIELD_ID"
, case when "FIELD_ID" like 'chk%' then
case when upper("FIELD_VALUE") = 'TRUE' then 'Yes' when upper("FIELD_VALUE") = 'FALSE' then 'No' else "FIELD_VALUE" end
else case when length("FIELD_VALUE") =1 then case when "FIELD_VALUE" = 'Y' then 'Yes' when "FIELD_VALUE" = 'N' then 'No' else "FIELD_VALUE" end
else "FIELD_VALUE" END END "FIELD_VALUE"
, P."FIELD_TAG"
, P."TASK_TEMPLATE_ID", P."WRK_TASK_ID"
,r.DYNAMIC_FORM_CONTROLID_ID
, r.section_title SECTION_TITLE, r.control_tag CTRL_LABE, r.TASK_TEMPLATE, r.FORM_NAME
from (
SELECT DISTINCT i.inspection_id, i.FACILITY_ID,
i.insp_type_cd AS type_cd ,
EXTRACTVALUE(VALUE(XMLTABLE), '/dataField/id') field_id ,
EXTRACTVALUE(VALUE(XMLTABLE), '/dataField/value') field_value ,
EXTRACTVALUE(VALUE(XMLTABLE), '/dataField/tag') field_tag ,
wt.task_template_id, wt.wrk_task_id
FROM insp_inspection i
inner join wrk_task wt on i.wrk_task_id = wt.wrk_task_id,
TABLE(XMLSEQUENCE(EXTRACT(xmltype(I.dynamic_result), '//DynamicFormValue/*/dataField'))) XMLTABLE
WHERE I.dynamic_result IS NOT NULL and BIZID_TABLE_NAME = 'INSP_INSPECTION' and BIZID_FIeLD_NAME = 'INSPECTION_ID'
--and INSPECTION_ID = 66736
) P
left join REF_DYNAMIC_FORM_CONTROLID r on cast(P.task_template_id as varchar2(100)) = task_template and r.CONTROLID = P.field_id;
/