Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Issue while scheduling BI Publisher report - 12.2.1.3.0

Received Response
182
Views
3
Comments
Manoj0408
Manoj0408 Rank 2 - Community Beginner
in Oracle Business Intelligence Applications

Hi all,

I have a BI Publisher report, which I want to burst and FTP the output. The FTP is working fine.

When the report is run standalone it gets executed and gives output, but when it is being scheduled through BI Publisher scheduler, getting the below error.

Can someone who has faced/not faced the same issue provide the resolution/inputs.

Thanks in advance.

I believe the below table would be handled by OBI itself, and column JOB_ID (primary key of table) on which the unique constraint is getting violated would be a sequence, then why its trying to insert the same value in this column?

Exact error:

Job submission failed:

oracle.xdo.servlet.scheduler.processingexception: exception [eclipselink-4002] (Eclipse Persistence Services - 2.6.5.v20170607 -

b3d05bd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BI_BIPLATFORM.SYS_C0054217) violated

Error : 1

Call: INSERT INTO XMLP_SCHED_JOB (JOB_ID, BURSTING, BURSTING_PARAMETERS, CREATED, DATA_LOCATOR, DELETED, DELIVERY_DESCRIPTION, DELIVERY_PARAMETERS,

END_DATE, INSTANCE_ID, ISSUER, JOB_GROUP, JOB_SET_ID, JOB_TYPE, LAST_UPDATED, NOTIFICATION_PARAMETERS, OWNER, PARENT_JOB_ID, REPORT_PARAMETERS, REPORT_URL, RUN_TYPE, SCHEDULE_CONTEXT, SCHEDULE_DESCRIPTION, SCHEDULE_PARAMETERS, SCHEDULE_SOURCE, SHARED_OPTION, START_DATE, STATUS, STATUS_DETAIL, STORAGE_TYPE, USER_DESCRIPTION, USER_JOB_NAME, XML_DATA_AVAILABLE, XML_DATA_COMPRESSED, XML_DATA_CONTENT_TYPE, XSCHURL) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

bind => [36 parameters bound]

Query: InsertObjectQuery(oracle.xdo.servlet.scheduler.toplink.XmlpSchedJob@6177463)

Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BI_BIPLATFORM.SYS_C0054217) violated

Answers

  • User_YLLMV
    User_YLLMV Rank 1 - Community Starter

    Facing same issue on Oracle BI Publisher 12.2.1.4.0 Standalone - any one got a solution for this ?

    Error Code: 1

    Call: INSERT INTO XMLP_SCHED_JOB (JOB_ID, BURSTING, BURSTING_PARAMETERS, CREATED, DATA_LOCATOR, DELETED, DELIVERY_DESCRIPTION, DELIVERY_PARAMETERS, END_DATE, INSTANCE_ID, ISSUER, JOB_GROUP, JOB_SET_ID, JOB_TYPE, LAST_UPDATED, NOTIFICATION_PARAMETERS, OWNER, PARENT_JOB_ID, REPORT_PARAMETERS, REPORT_URL, RUN_TYPE, SCHEDULE_CONTEXT, SCHEDULE_DESCRIPTION, SCHEDULE_PARAMETERS, SCHEDULE_SOURCE, SHARED_OPTION, START_DATE, STATUS, STATUS_DETAIL, STORAGE_TYPE, USER_DESCRIPTION, USER_JOB_NAME, XML_DATA_AVAILABLE, XML_DATA_COMPRESSED, XML_DATA_CONTENT_TYPE, XSCHURL) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

           bind => [36 parameters bound]

    Query: InsertObjectQuery(oracle.xdo.servlet.scheduler.toplink.XmlpSchedJob@7b579edb):

    Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BIP_BIPLATFORM.SYS_C00315220) violated


    Error Code: 1

    Call: INSERT INTO XMLP_SCHED_JOB (JOB_ID, BURSTING, BURSTING_PARAMETERS, CREATED, DATA_LOCATOR, DELETED, DELIVERY_DESCRIPTION, DELIVERY_PARAMETERS, END_DATE, INSTANCE_ID, ISSUER, JOB_GROUP, JOB_SET_ID, JOB_TYPE, LAST_UPDATED, NOTIFICATION_PARAMETERS, OWNER, PARENT_JOB_ID, REPORT_PARAMETERS, REPORT_URL, RUN_TYPE, SCHEDULE_CONTEXT, SCHEDULE_DESCRIPTION, SCHEDULE_PARAMETERS, SCHEDULE_SOURCE, SHARED_OPTION, START_DATE, STATUS, STATUS_DETAIL, STORAGE_TYPE, USER_DESCRIPTION, USER_JOB_NAME, XML_DATA_AVAILABLE, XML_DATA_COMPRESSED, XML_DATA_CONTENT_TYPE, XSCHURL) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

           bind => [36 parameters bound]


    Thanks,

    Srikar

  • User_9I1SW
    User_9I1SW Rank 1 - Community Starter

    Please share the steps to fix this issue. Below are column 'desc XMLP_SCHED_JOB;'


    Name          Null?  Type      

    ----------------------- -------- -------------- 

    JOB_ID         NOT NULL NUMBER(12)   

    JOB_TYPE        NOT NULL CHAR(1)     

    DELETED             CHAR(1)     

    STATUS              CHAR(1)     

    STATUS_DETAIL          VARCHAR2(1000) 

    INSTANCE_ID           NUMBER(12)   

    LAST_UPDATED           TIMESTAMP(6)  

    CREATED             TIMESTAMP(6)  

    BURSTING_PARAMETERS       BLOB      

    DELIVERY_PARAMETERS       BLOB      

    SCHEDULE_PARAMETERS       BLOB      

    REPORT_PARAMETERS        BLOB      

    NOTIFICATION_PARAMETERS     BLOB      

    XSCHURL             VARCHAR2(4000) 

    DELIVERY_DESCRIPTION       VARCHAR2(4000) 

    SCHEDULE_DESCRIPTION       VARCHAR2(100)  

    END_DATE             TIMESTAMP(6)  

    START_DATE            TIMESTAMP(6)  

    BURSTING             CHAR(1)     

    SHARED_OPTION          CHAR(1)     

    RUN_TYPE             CHAR(1)     

    USER_DESCRIPTION         VARCHAR2(4000) 

    USER_JOB_NAME          VARCHAR2(1000) 

    ISSUER         NOT NULL VARCHAR2(100)  

    OWNER          NOT NULL VARCHAR2(100)  

    REPORT_URL       NOT NULL VARCHAR2(1000) 

    JOB_GROUP        NOT NULL VARCHAR2(1000) 

    JOB_SET_ID            NUMBER(12)   

    PARENT_JOB_ID          NUMBER(12)   

    SCHEDULE_SOURCE         VARCHAR2(100)  

    SCHEDULE_CONTEXT         VARCHAR2(1000) 

    XML_DATA_AVAILABLE        CHAR(1)     

    XML_DATA_COMPRESSED       CHAR(1)     

    XML_DATA_CONTENT_TYPE      VARCHAR2(100)  

    STORAGE_TYPE           VARCHAR2(100)  

    DATA_LOCATOR           VARCHAR2(1000) 

  • ChrisMarais
    ChrisMarais Rank 1 - Community Starter

    For those who get this issue. This is related to the SEQUENCE table being out of sync with the the XMLP_SCHED_JOB table.

    If you look at the SEQUENCE table you will see that there are 4 sequences which link back to 4 different tables

    • JOB_ID_SEQ - This is used to find the next JOB_ID for XMLP_SCHED_JOB table.
    • OUTPUT_ID_SEQ - This is used to find the next OUTPUT_ID for XMLP_SCHED_OUTPUT
    • DELIVERY_ID_SEQ - This is used to find the next DELIVERY_ID for XMLP_SCHED_DELIVERY
    • DATA_ID_SEQ - This is used to find the next available DATA_ID for XMLP_SCHED_DATA

    So when the sequence is less than the max value in the table it is trying to write a record to it will fail as it will try and insert a value that is already in the table. On failure the sequence seems to increment by 2 so if your sequence is not far out a couple of submissions should get you back on track. If all of the sequences are out of alignment then it will take a bit longer. e.g.

    The order that tables get populated

    1. XMLP_SCHED_JOB - This is all the job details
    2. XMLP_SCHED_DATA - This contains information around the size of the files that are going to be created links back to XMLP_SCHED_JOB
    3. XMLP_SCHED_OUTPUT - This contains the status information around your delivery output links back to XMLP_SCHED_DATA
    4. XMLP_SCHED_DELIVERY - This contains the status around the actual delivery of the output links back to XMLP_SCHED_OUTPUT