0

I have a request to schedule some reports one after another in oracle BI Publisher. The reports scheduler would start run at 7:00 PM and finish with all the reports nearly at 7:00 AM of the next day. What I thought was to create a trigger that would check BI Publisher database if one other specific report was run and then run the report. The trigger query is as below:

select   "XMLP_SCHED_JOB"."STATUS" as "STATUS",
   "XMLP_SCHED_JOB"."CREATED" as "CREATED",
   "XMLP_SCHED_JOB"."USER_JOB_NAME" as "USER_JOB_NAME",
   "XMLP_SCHED_JOB"."JOB_TYPE" as "JOB_TYPE" 
 from  "DEV1_BIPLATFORM"."XMLP_SCHED_JOB" "XMLP_SCHED_JOB" 
 where    "XMLP_SCHED_JOB"."STATUS" !='R' 
   and   "XMLP_SCHED_JOB"."CREATED" BETWEEN (SELECT CASE WHEN  TRUNC( SYSDATE, 'HH24' ) < TRUNC(SYSDATE) + 7/24 THEN TRUNC(SYSDATE-1) + 7/24 
                    ELSE TRUNC(SYSDATE) + 7/24 END FROM DUAL
                     ) 
                     AND  SYSDATE
   and   "XMLP_SCHED_JOB"."USER_JOB_NAME" ='test' 
   and   "XMLP_SCHED_JOB"."JOB_TYPE" ='I'

When I run it in oracle database I get results normally, but when I enter it in BI Publisher trigger query I get the error in logs:

oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xml.parser.v2.XMLParseException: Expected name instead of .

I get the error only when I place the TRUNC( SYSDATE, 'HH24' ) < TRUNC(SYSDATE) + 7/24 in the query

assylias
  • 321,522
  • 82
  • 660
  • 783
amarilda
  • 1
  • 1

1 Answers1

0

You can create a standard request set, and then schedule that. It will run them one after another in order. One problem with this approach, however, is that request sets only allow you to have one report layout per request/data definition.

EdHayes3
  • 1,777
  • 2
  • 16
  • 31