IBM Sterling Transformation Extender

Sterling Transformation Extender

Come for answers, stay for best practices. All we're missing is you.

 View Only
Back to discussions
Expand all | Collapse all

Oracle 11g error Error returned by OCIStmtExecute, errcode=917 ORA-00917: missing comma

  • 1.  Oracle 11g error Error returned by OCIStmtExecute, errcode=917 ORA-00917: missing comma

    Posted Fri July 14, 2017 02:14 PM

    Originally posted by: IBMSri


    experts 

    trying to Insert rows into table , able to insert in windows machine if I use MDQ as separate card with table fields and command line -U , But the same is not working in Linux box ( ITXA with sterling B2B) , in Linux box job completed successfully But no rows inserted / updated , Trace is not giving info , 

    my next approach is : using below command 

    =VALID(DBLOOKUP("INSERT INTO SCHEMA.TABLE1(PRIMARY_KEY, F1, F2,F3,F4,F5,F6,F7,F8,F9, F10) ,VALUES(PRIM_KEY Column:In1,F1 Column:In1,F2 Column:In1,F3 Column:In1,F4 Column:In1,F5 Column:In1,F6 Column:In1,F7 Column:In1,F8 Column:In1,F9 Column:In1,F10 Column:In1)", ,"-MDQ MDQ_DEV.mdq -DBNAME MyDB -T+") , FAIL(" FAILED"))

     

    same error I am getting on Both windows and linux : 

     
     
    8444-8304-07/14/17 13:40:37>: Data being retrieved for DBLOOKUP function.
    <8444-8304-07/14/17 13:40:37>: Database adapter: Oracle11g Version 9.0.0.0(172)
    <8444-8304-07/14/17 13:40:37>: Starting a database unload...
    <8444-8304-07/14/17 13:40:37>: Query      : INSERT INTO SCHEMA.TABLE1(PRIMARY_KEY, F1, F2,F3,F4,F5,F6,F7,F8,F9, F10) 
    VALUES(PRIM_KEY Column:In1,F1 Column:In1,F2 Column:In1,F3 Column:In1,F4 Column:In1,F5 Column:In1,F6 Column:In1,F7 Column:In1,F8 Column:In1,F9 Column:In1,F10 Column:In1)
    DB trace:
    <8444-8304-07/14/17 13:40:37>: DBLOOKUP Function, Transaction scope: Map
    <8444-8304-07/14/17 13:40:37>: TRACE command specified, append mode, file: C:\PRH_Root\WTX_Clean\Source\REF_PROM_ORD_REASON\REF_PROM_ORD_REASON_ETL.dbl
    <8444-8304-07/14/17 13:40:37>: Error returned by OCIStmtExecute, errcode=917
    ORA-00917: missing comma
     
    <8444-8304-07/14/17 13:40:37>: Returned status: (-1009) Failed_to_execute_the_SQL_statement
    <8444-8304-07/14/17 13:40:37>: Cleaning up and rolling back the transaction...
    <8444-8304-07/14/17 13:40:37>: Transaction rollback was successful.
    <8444-8304-07/14/17 13:40:37>: Returned status: (0) Success

    above command I dont find syntactical issue , 

     

    Next approcah is with out MDQ : to check bare logic testing I mocked up insert statement with data , as below 

    =VALID(DBLOOKUP("INSERT INTO SCHEMA.TABLE1(PRIMARY_KEY, F1, F2,F3,F4,F5,F6,F7,F8,F9, F10)  
     VALUES('Test4', 'f1', '', 'f', 0, 'ZK', 'KK', 'KKK', 'KKK', 'KK', '')" 
     , " -DBTYPE ORACLE -CONNECT " + BLOB Items[1]:Rec_1[1]:DB_Info + " -USER " +BLOB Items[2]:Rec_1[1]:DB_Info+ " -PASSWORD " +BLOB Items[3]:Rec_1[1]:DB_Info+ " -T"),  FAIL("Unable to INSERT spe_codelist_item Data for PRIMARY_KEY"//+PRIM_KEY Column:In1
     ))

     

    Trace : 

     
     <22490-2741036800-Fri Jul 14 12:42:17 2017>: Validating the adapter command...
    <22490-2741036800-Fri Jul 14 12:42:17 2017>: Database type is Oracle
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Participating in active transaction.
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Interface library version 9.0.0.1(42)
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Map: /opt/IBM/StandardsProcessingEngine9.0.0/REF_PROM_ORD_REASON_ETL,  Timestamp: Fri Jul 14 12:42:17 2017.
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Data being retrieved for DBLOOKUP function.
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Database adapter: Oracle11g Version 9.0.0.1(42)
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Starting a database unload...
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Query      : INSERT INTO SCHEMA.TABLE1(RIMARY_KEY, F1, F2,F3,F4,F5,F6,F7,F8,F9, F10) VALUES('Test4', 'f1', '', 'f', 0, 'SK', 'KK', 'KKK', 'KKK', 'KK', '')
    <22490-605933952-Fri Jul 14 12:42:17 2017>: DBLOOKUP Function, Transaction scope: Map
    <22490-605933952-Fri Jul 14 12:42:17 2017>: TRACE command specified, file: /opt/IBM/StandardsProcessingEngine9.0.dbl
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Error returned by OCIStmtExecute, errcode=1
    ORA-00001: unique constraint (PCC_ITXA.SYS_C0024350) violated
     
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Adapter Time = 0.0014 sec, Database Time = 0.0013 sec, Describe Time = 0.0000 sec
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Connection 32292: Connect Time = 0.0000 sec, Connect Database Time = 0.0006 sec, Connect CPU Time = 0.0000 sec
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Returned status: (-1009) Failed_to_execute_the_SQL_statement
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Cleaning up and rolling back the transaction...
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Transaction rollback was successful.
    <22490-605933952-Fri Jul 14 12:42:17 2017>: Returned status: (0) Success
    <22490-157426048-Fri Jul 14 12:42:17 2017>: Cleaning up and committing the transaction...
    <22490-157426048-Fri Jul 14 12:42:17 2017>: The transaction was successfully committed.
    <22490-157426048-Fri Jul 14 12:42:17 2017>: Returned status: (0) Success

     

    if I execute below query .in TOAD no rows are returning

    SELECT * from  SCEHMA.TABLE1 where PRIM_KEY Like '%Test%'

     

    Out of 3 success story will be card with command line -U , But that also not working on linux . 

    Not sure what I am missing here any inputs on this Guys 


    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender


  • 2.  Re: Oracle 11g error Error returned by OCIStmtExecute, errcode=917 ORA-00917: missing comma

    Posted Thu July 20, 2017 06:39 AM

    Originally posted by: PaulBrettIBM


    There should be no functional differences between platforms.  So if a map using a card with -U works on Windows, it should work on Linux.

    I suggest you raise a Service Request (PMR) with IBM for this.

    I have seen similar issues, and it has been because of the way the database is defined as UTF8 (after being migrated from an earlier version) and the client being defined as AL32UTF8. 

    You could try setting the following variable:

    export TX_DB_PAD_CHAR=1

    Thank you.

    Paul

    Follow me on Twitter


    #DataExchange
    #IBMSterlingTransformationExtender
    #IBM-Websphere-Transformation-Extender