HCM Data Loader: HDL Data Set Status Diagnostic Error Execution of the SQL Query Failed (Doc ID 2136063.1)

Last updated on MAY 10, 2016

Applies to:

Oracle Fusion Global Human Resources Cloud Service - Version 11.1.10.0.0 and later
Oracle Fusion Global Human Resources - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Symptoms

When user is running HDL Data Set Status diagnostic for an HDL batch, the following error is shown:

Critical: Error occured while carrying out post-execution steps of the PL/SQL Step: Data Loader Support Diagnostics
oracle.apps. fnd.appltes t.diagfwk.common.DiagfwkException: Error occured wh ile carrying out post-execution steps of the PL/SQL Step: Data Loader Support Diagnostics at oracle.apps.fnd.appltest.diag fwk.engine.impl.StepExecutorPLSQL.executeStep(StepExecutorPLSQL.java:198) at oracle.apps .fnd.app ltest.diagfwk.en gine.impl.StepExecutorBase.execute(StepExecutorBase.java:75) at oracle.apps.fnd.appltest.diagfwk.engine.impl.TestDef Impl.executeTest(Tes tDefImpl.java:982) at oracle.apps.fnd.appltest.diag fwk.engine.impl.TestDef Imp l.execute(Tes tDefImpl.java:937) at oracle.apps. fnd.appltest.diagfwk.engine.impl.TestDef Impl. run(Tes tDefImpl.java:761) at oracle.apps.fnd.appltest.diagfwk.engine.impl.TestRunner.run(TestRunner.java:441) at oracle.apps.fnd.appltest.diagfwk.engine.impl.TestRunner.ru n(TestRunner. java:241) at oracle.apps.fnd.appltest.diagfwk.engine.impl.TestRunner.doRun(Tes tRunner. java:152) at
oracle.apps. fnd.appltes t.diagfwk.engine.DiagRunnable.run(DiagRunnable. java:68) at java.util.concurrent.Execu tors$Run nableAdapter.call(Executors.java:439) at java.util. concu rrent.FutureTas k$Sync. innerRu n(FutureTas k. java:303) at java.util.concurrent.FutureTask. run(FutureTask.java:138) at java.util. concurrent.ThreadPoolExecutor$Worker.runTask(Thread PoolExecutor.j ava:895) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor. java:918) at java.lang.Thread. run(Thread.java:682) Caused by: oracle.apps.fnd.appltest.diagfwk.common.DiagfwkException: Execution of the SQL query failed. SQL query was: 'SELECT fl.seq_num "S equ ence Number" ,B.MR "Mes sage Recorded" , lpad(B.OD,len gth(B.OD)+level, '-') "Object Discriminator" ,B.RT "Record Type" ,B.ID ,B.IM "Import Status" ,B.LS "Load Status" ,B.MT "Mes sage Text" ,pl.P_EFFECTIVE_START_DATE "Effective Start Date" ,pl.P_EFFE CTIVE_END_DATE "Ef fective End Date"
,pl.P_EFFECTIVE _SEQUENCE "E ffective Sequence" ,pl.P_EFFECTIVE _LATEST_CHANGE "Ef fective Latest Change" ,ro.KEY_SOURCE_OWNER "Sou rce Sys tem Owner" ,ro.KEY_SOURCE_ID "Source Sys tem ID" ,ro.KEY_SURROGATE _ID "S urrogate ID" ,ro.KEY_GUID "GUID" ,fh.PARENT_RESOLUTION_TYPE "Parent Resolution Type" ,ro.KEY_PARENT_SOURCE_OWNER "Parent Source System Owner" ,ro.KE Y_PARENT_SOURCE_ID "Parent Source System ID" ,ro.KEY_PARENT_SURROGATE _ID "P arent Surrogate ID" , ro.KEY_PARENT_GUID "Parent GUID" ,uk.UKVAL001 "User Key 01" ,uk.UKVAL002 "User Key 02" ,uk.UKVAL003 "User Key 03" ,uk.UKVAL004 "User Key 04" ,u k.UKVAL005 "User Key 05" ,uk.UKVAL006 "User Key 06" ,uk.UKVAL007 "User Key 07" ,uk.UKVAL008 "User Key 08" ,uk.UKVAL009 "User Key 09" ,uk.UKVAL010 "User Key 010" ,uk.UKVAL011 "User Key 011" ,uk.UKVAL012 "User Key 012" ,uk.UKVAL013 "User Key 013" ,uk.UKVAL014 "User Key 014"
,uk.UKVAL015 "User Key 015" ,uk.UKVAL016 "User Key 016" ,uk.UKDATE 001 "User Key Date 01" ,uk.UKDATE002 "User Key Date 02" ,uk.UKTIMESTAMP001 "User Key Timestamp 01" , ro.ROW_ID ,ro.LOGICAL_LINE_ID "Row LOGICAL_LINE_ID" ,ro.PARENT_LOGICAL_LINE _ID "Row PARENT_LOGICAL_LINE_ID" ,hrc_dl_diag_utils .get_distinct_messages (ro.ROW_ID) "File Row Mes sage Text" ,fh.SOURCE_REF_TABLE_NAME "Source Ref Table" ,ro.SOURCE_REFERENCE001 "Source Ref 01" ,ro.SOURCE_REFERENCE002 "Source Ref 02" ,ro.SOURCE_REFERENCE003 "Source Ref 03" ,ro.SOURCE_REFERENCE004 "S ou rce Ref 04" ,ro.SOURCE_REFERENCE 005 "Source Ref 05" ,ro.SOURCE_REFERENCE006 "Source Ref 06" ,ro.SOURCE_REFERENCE007 "Source Ref 07" ,ro.SOURCE_RE FERENCE008 "Source Ref 08" ,ro.SOURCE_REFERENCE009 "Source Ref 09" , ro.SOURCE _REFERENCE010 "Source Ref 10" FROM(select (SELECT 'X' FROM hrc_dl_message_lines WHERE
message_source_lin e_id in(pl.physical_line_id,pl. row_id) and rownum<2) MR ,'PHYSICAL' RT ,PHYSICAL_LINE_ID ID ,pl.IMPORTED_STATUS IM ,pl.VALIDATED_LOADED_STATUS LS ,hrc_dl_diag_utils.get_dis tinct_messages(pl.PHYSICAL_LINE_ID) MT ,BUS_OBJ _FILE_DIS CRIMINATOR OD ,pl. logical_line_id plogical from hrc_dl_physical_lines pl, h rc_dl_vo_maps vm, hrc_dl_busines s_objects bo where pl. vo_mapping_id=vm.vo_mappin g_id an d vm.business_object_id=bo.bus ines s_object_id and pl.DATA_SET_BUS_OBJ_ID=30000000XXXXXXXUNION select (SELECT 'X' FROM hrc_dl_message_lines WHERE mes sage_source_line_id = ll.logical_line_id an d rownum<2) MR ,'LOGICAL' RT ,LOGICAL_LINE_ID ID ,ll. IMPORTED_STATUS IM ,ll.VALIDATED_LOADED_STATUS LS ,hrc_dl_diag_utils .get_distinct_messages (ll.LOGICAL_LINE_ID) MT ,bo.BUS_OBJ_FILE_DISCRIMINATOR||'('||UI_USER_KEY||')' OD , ll.paren t_logical_line_id plogical from h rc_dl_logical_lines ll,
hrc_dl_vo_maps vm, hrc_dl_business_objects bo where ll. vo_mapping_id=vm.vo_mapping_id and vm.business_object_id=bo.bus iness_object_id and ll.DATA_SET_BUS_OBJ_ID=30000000XXXXXXX and (ll.paren t_logical_line_id<>0 OR (ll.p arent_logical_line_id=0 AND (ll.validated_loaded_status like '%ERROR%' OR ll.validated_loaded_status like '%WARNING%' OR ll. imported _statu s IN ('ERROR', 'WARNING')))))B ,hrc_dl_physical_lines pl ,hrc_dl_f ile_rows ro ,h rc_dl_f ile_headers fh ,h rc_dl_f ile_uk_rows uk ,hrc_dl_file_lines f l where B.ID = pl.physical_line_id(+) an d ro. row_id(+)=pl. row_id and pl.row_id=uk.row_id(+) and ro.line_id = fl.line_id(+) and ro.header_id=fh.header_id(+) and rown um<=500 connect by prior B.ID = B.plogical start with B.plogical=0 order siblin gs by nvl(ro.LOGICAL_LINE_ID,ID), RT' at oracle.apps.fnd.appltest.diagfwk.engine.impl. report.plsql.PLSQLReportProcessor.d isp laySQLResults (PLSQLReportP roces sor. java:622) at
oracle.apps. fnd.appltes t.diagfwk.engine.impl.report.plsql.PLSQLReportProces sor.displaySQLResults(PLS QLReportProcessor.java:558) at oracle.apps. fnd.appltest.diagfwk.engine.impl.report.plsql.P LSQLReportP roces sor.process(PLSQLReportProcessor.java:315) at oracle.apps. fnd.appltest.diagfwk.engine.impl.StepExecutorPLSQL.dumpFromPLSQLContext(Step Execu torPLSQL.java:484) at oracle.apps .fnd.appltes t.diagfwk.eng ine.impl.Step Execu torPLSQL.executeStep(StepExecutorPLSQL.java:194) at oracle.apps.fnd.appltest.diagfwk.engine.impl.StepExecutorBase.execute(StepExecutorBase.java:75) at oracle.apps .fnd.appltes t.diagfwk.en gine.impl.TestDefImpl.executeTest(TestDef Impl.java:985) at oracle.apps .fnd.appltes t.diagfwk.eng ine.impl.TestDefImpl.execute(TestDef Impl.j ava:937) at oracle.ap ps.fnd.appltest.diagfwk.engine.impl.Tes tDefImpl.run(TestDef Impl.java:761) at oracle.apps .fnd.appltes t.diagfwk.en gine.impl.TestRu nner.run(TestRunn er.java:441) at
oracle.apps. fnd.appltes t.diagfwk.engine.impl.TestRunner.run(TestRunner.java:241) at oracle.apps. fnd.appltest.diagfwk.engine.impl.TestRunner.doRun(TestRunner.java:152) at oracle.apps.fnd.appltest.diagfwk.engine.DiagRunnable.run (DiagRun nable.java:68) at java.u til. concurrent.Executors$RunnableAdapter.call(Execu tors.java:440) at java.util.concurrent.FutureTask$Sync.innerRun(Futu reTask.java:303) at java.u til. concurren t.FutureTask.run(FutureTask.java:139) ... 2 more Caused by: j ava.sql.SQLException: ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer. java:462) at oracle. jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405) at oracle. jdbc.driver.T4C8Oall.processError(T4C8Oall. java:931) at oracle. jdbc.driver.T4CTTIfun .receive(T4CTTIfun.java:481) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun. java:205) at oracle. jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548) at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CP reparedStatement.java:217) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947) at oracle.jdbc.driver.OracleStatement.executeMaybeDescrib e(OracleStatement.java:1283) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleS tatement.java:1441) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep aredStatement.java:3769) at oracle.jd bc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execu teQuery(OraclePreparedS tatementWrapper.j ava:1671) at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement. java:144) at oracle.apps.fnd .appltest.diagfwk.engine. impl.report.plsql.PLSQLReportProcessor.displaySQLResults(PLSQLReportProcessor.java:584) at
oracle.apps. fnd.appltes t.diagfwk.engine.impl.report.plsql.PLSQLReportProces sor.displaySQLResults(PLS QLReportProcessor.java:555) at oracle.apps. fnd.appltest.diagfwk.engine.impl.report.plsql.P LSQLReportP roces sor.process(PLSQLReportProcessor.java:315) at oracle.apps. fnd.appltest.diagfwk.engine.impl.StepExecutorPLSQL.dumpFromPLSQLContext(Step Execu torPLSQL.java:481) at oracle.apps .fnd.appltes t.diagfwk.eng ine.impl.Step Execu torPLSQL.executeStep(StepExecutorPLSQL.java:194) at oracle.apps.fnd.appltest.diagfwk.engine.impl.StepExecutorBase.execute(StepExecutorBase.java:75) at oracle.apps .fnd.appltes t.diagfwk.en gine.impl.TestDefImpl.executeTest(TestDef Impl.java:982) at oracle.apps .fnd.appltes t.diagfwk.eng ine.impl.TestDefImpl.execute(TestDef Impl.j ava:937) at oracle.ap ps.fnd.appltest.diagfwk.engine.impl.Tes tDefImpl.run(TestDef Impl.java:761) at oracle.apps .fnd.appltes t.diagfwk.en gine.impl.TestRu nner.run(TestRunn er.java:441) at
oracle.apps. fnd.appltes t.diagfwk.engine.impl.TestRunner.run(TestRunner.java:241) at oracle.apps. fnd.appltest.diagfwk.engine.impl.TestRunner.doRun(TestRunner.java:152) at oracle.apps.fnd.appltest.diagfwk.engine.DiagRunnable.run (DiagRun nable.java:68) at java.u til. concurrent.Executors$RunnableAdapter.call(Execu tors.java:439) at java.util.concurrent.FutureTask$Sync.innerRun(Futu reTask.java:303) at java.u til. concurren t.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) .. . 2 more
Fix Information: An unexpected error was encountered. Please contact your system administrator.

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms