5 Replies Latest reply on Apr 27, 2010 7:09 PM by 667719

    Generating target table primary key using Oracle sequences

    user121437
      Hi,

      I am new to ODI and struggling with a simple scenario.

      I have two source tables in an Oracle DB, and I am trying to use them to populate a target table in another Oracle DB.

      The target table has a primary key that is not derived from the source, instead, should be generated in the target db using an Oracle sequence.


      I am getting the errors below when I don't make the primary key (SIM.SIM_SAMPLE_ID) map to anything.


      936 : 42000 : java.sql.SQLException: ORA-00936: missing expression

      java.sql.SQLException: ORA-00936: missing expression

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
           at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
           at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)


      and the generated statement is this:

      /* DETECTION_STRATEGY = NOT_EXISTS */
      insert /*+ APPEND */ into SIM.I$_SIM_SAMPLE
      (
           OPERATIONAL_SAMPLE_ID,
           SMF_SAMPLE_NAME,
           SMF_CONCEPT_NAME,
           VENDOR_SAMPLE_ID,
           VENDOR_GENE_ID,
           VENDOR_GENE_SYMBOL,
           VENDOR_GENE_DESCRIPTION,
           VENDOR_TRANSCRIPT_NM,
           SPECIES_NAME,
           IND_UPDATE
      )


      select      
           C1_OPERATIONAL_SAMPLE_ID     OPERATIONAL_SAMPLE_ID,
           C2_SMF_SAMPLE_NAME     SMF_SAMPLE_NAME,
           C3_SMF_CONCEPT_NAME     SMF_CONCEPT_NAME,
           C4_VENDOR_SAMPLE_ID     VENDOR_SAMPLE_ID,
           C5_VENDOR_GENE_ID     VENDOR_GENE_ID,
           C6_VENDOR_GENE_SYMBOL     VENDOR_GENE_SYMBOL,
           C7_VENDOR_GENE_DESCRIPTION     VENDOR_GENE_DESCRIPTION,
           C8_VENDOR_TRANSCRIPT_NM     VENDOR_TRANSCRIPT_NM,
           C9_SPECIES_NAME     SPECIES_NAME,

           'I' IND_UPDATE

      from     SIM.C$_0SIM_SAMPLE
      where     (1=1)




      and not exists (
           select     'X'
           from     SIM.SIM_SAMPLE T
           where     
                and     ((C1_OPERATIONAL_SAMPLE_ID = T.OPERATIONAL_SAMPLE_ID) or (C1_OPERATIONAL_SAMPLE_ID IS NULL and T.OPERATIONAL_SAMPLE_ID IS NULL))
                and     ((C2_SMF_SAMPLE_NAME = T.SMF_SAMPLE_NAME) or (C2_SMF_SAMPLE_NAME IS NULL and T.SMF_SAMPLE_NAME IS NULL))
                and     ((C3_SMF_CONCEPT_NAME = T.SMF_CONCEPT_NAME) or (C3_SMF_CONCEPT_NAME IS NULL and T.SMF_CONCEPT_NAME IS NULL))
                and     ((C4_VENDOR_SAMPLE_ID = T.VENDOR_SAMPLE_ID) or (C4_VENDOR_SAMPLE_ID IS NULL and T.VENDOR_SAMPLE_ID IS NULL))
                and     ((C5_VENDOR_GENE_ID = T.VENDOR_GENE_ID) or (C5_VENDOR_GENE_ID IS NULL and T.VENDOR_GENE_ID IS NULL))
                and     ((C6_VENDOR_GENE_SYMBOL = T.VENDOR_GENE_SYMBOL) or (C6_VENDOR_GENE_SYMBOL IS NULL and T.VENDOR_GENE_SYMBOL IS NULL))
                and     ((C7_VENDOR_GENE_DESCRIPTION = T.VENDOR_GENE_DESCRIPTION) or (C7_VENDOR_GENE_DESCRIPTION IS NULL and T.VENDOR_GENE_DESCRIPTION IS NULL))
                and     ((C8_VENDOR_TRANSCRIPT_NM = T.VENDOR_TRANSCRIPT_NM) or (C8_VENDOR_TRANSCRIPT_NM IS NULL and T.VENDOR_TRANSCRIPT_NM IS NULL))
                and     ((C9_SPECIES_NAME = T.SPECIES_NAME) or (C9_SPECIES_NAME IS NULL and T.SPECIES_NAME IS NULL))
           )


      The error has to do with the fact that ODI generated an "AND" directly after a "WHERE" statement, as seen above.
      I guess is due to no primary key mapping



      However, if I try to map the primary key (SIM.SIM_SAMPLE_ID) to the Oracle sequence I defined in the target DB (SIM_SAMPLE_ID_SEQ),
      using this mapping I see suggested on forums:
      <%=odiRef.getObjectName( "L" , "SIM_SAMPLE_ID_SEQ" , "D" )%>.nextval

      i get another error:

      2019 : 42000 : java.sql.SQLException: ORA-02019: connection description for remote database not found

      java.sql.SQLException: ORA-02019: connection description for remote database not found

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
           at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
           at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)

      and now the generated statement seems correct although it generates the error above and comparing the primary key with the SEQ.NEXTVAL doesn't make sense (T.SIM_SAMPLE_ID     = SIM.SIM_SAMPLE_ID_SEQ.nextval below):


      /* DETECTION_STRATEGY = NOT_EXISTS */
      insert /*+ APPEND */ into SIM.I$_SIM_SAMPLE
      (
           OPERATIONAL_SAMPLE_ID,
           SMF_SAMPLE_NAME,
           SMF_CONCEPT_NAME,
           VENDOR_SAMPLE_ID,
           VENDOR_GENE_ID,
           VENDOR_GENE_SYMBOL,
           VENDOR_GENE_DESCRIPTION,
           VENDOR_TRANSCRIPT_NM,
           SPECIES_NAME,
           IND_UPDATE
      )


      select      
           C1_OPERATIONAL_SAMPLE_ID     OPERATIONAL_SAMPLE_ID,
           C2_SMF_SAMPLE_NAME     SMF_SAMPLE_NAME,
           C3_SMF_CONCEPT_NAME     SMF_CONCEPT_NAME,
           C4_VENDOR_SAMPLE_ID     VENDOR_SAMPLE_ID,
           C5_VENDOR_GENE_ID     VENDOR_GENE_ID,
           C6_VENDOR_GENE_SYMBOL     VENDOR_GENE_SYMBOL,
           C7_VENDOR_GENE_DESCRIPTION     VENDOR_GENE_DESCRIPTION,
           C8_VENDOR_TRANSCRIPT_NM     VENDOR_TRANSCRIPT_NM,
           C9_SPECIES_NAME     SPECIES_NAME,

           'I' IND_UPDATE

      from     SIM.C$_0SIM_SAMPLE
      where     (1=1)




      and not exists (
           select     'X'
           from     SIM.SIM_SAMPLE T
           where     T.SIM_SAMPLE_ID     = SIM.SIM_SAMPLE_ID_SEQ.nextval
                and     ((C1_OPERATIONAL_SAMPLE_ID = T.OPERATIONAL_SAMPLE_ID) or (C1_OPERATIONAL_SAMPLE_ID IS NULL and T.OPERATIONAL_SAMPLE_ID IS NULL))
                and     ((C2_SMF_SAMPLE_NAME = T.SMF_SAMPLE_NAME) or (C2_SMF_SAMPLE_NAME IS NULL and T.SMF_SAMPLE_NAME IS NULL))
                and     ((C3_SMF_CONCEPT_NAME = T.SMF_CONCEPT_NAME) or (C3_SMF_CONCEPT_NAME IS NULL and T.SMF_CONCEPT_NAME IS NULL))
                and     ((C4_VENDOR_SAMPLE_ID = T.VENDOR_SAMPLE_ID) or (C4_VENDOR_SAMPLE_ID IS NULL and T.VENDOR_SAMPLE_ID IS NULL))
                and     ((C5_VENDOR_GENE_ID = T.VENDOR_GENE_ID) or (C5_VENDOR_GENE_ID IS NULL and T.VENDOR_GENE_ID IS NULL))
                and     ((C6_VENDOR_GENE_SYMBOL = T.VENDOR_GENE_SYMBOL) or (C6_VENDOR_GENE_SYMBOL IS NULL and T.VENDOR_GENE_SYMBOL IS NULL))
                and     ((C7_VENDOR_GENE_DESCRIPTION = T.VENDOR_GENE_DESCRIPTION) or (C7_VENDOR_GENE_DESCRIPTION IS NULL and T.VENDOR_GENE_DESCRIPTION IS NULL))
                and     ((C8_VENDOR_TRANSCRIPT_NM = T.VENDOR_TRANSCRIPT_NM) or (C8_VENDOR_TRANSCRIPT_NM IS NULL and T.VENDOR_TRANSCRIPT_NM IS NULL))
                and     ((C9_SPECIES_NAME = T.SPECIES_NAME) or (C9_SPECIES_NAME IS NULL and T.SPECIES_NAME IS NULL))
           )



      I am really struggling here as the ODI examples don't seem to have a target table with primary keys generated by target db sequences.

      Any help will be greatly appreciated
        • 1. Re: Generating target table primary key using Oracle sequences
          667719
          In the IKM Options list, find the option called DETECTION_STRATEGY and set it to NONE (if you are doing full load on target) or POST_FLOW (if you want a separate step be executed to eliminate the rows that already exists). Hope that helps!!!
          1 person found this helpful
          • 2. Re: Generating target table primary key using Oracle sequences
            user424872 - oracle
            What i understand is that you are having a column in your target table that will be the primary key of the target table and will be be filled by a sequence created at the database.

            create a sequence at the database level. say the sequence name is SEQ123
            then while the creation of the interface give the following at the target column.

            *<%=snpRef.getObjectName( "L" , "SEQ123" , "D" )%>.nextval*

            The execution should be done at the target side.
            The sequence should be created in the physical schema which is linked to the logical schema of the target side.
            • 3. Re: Generating target table primary key using Oracle sequences
              user121437
              Thanks for the answer.

              I have two questions.

              1. What is the difference between odiRef.getObjectName and snpRef.getObjectName, since I've been using <%=odiRef.getObjectName( "L" , "SIM_SAMPLE_ID_SEQ" , "D" )%>.nextval based on other examples?

              2. Should I put the <%=snpRef.getObjectName( "L" , "SIM_SAMPLE_ID_SEQ" , "D" )%>.nextval in the Implementation section of the target table primary key?
              Or some place else? I am asking, since per my previous message, the SQL generated for the "Insert flow" section of the Interface execution will end up with a statement like this:

              ...
              and not exists (
                   select     'X'
                   from     SIM.SIM_SAMPLE T
              *     where     T.SIM_SAMPLE_ID     = SIM.SIM_SAMPLE_ID_SEQ.nextval*
                        and     ((C1_OPERATIONAL_SAMPLE_ID = T.OPERATIONAL_SAMPLE_ID) or
              ...

              which I think doesn't make sense as the it increments the sequence

              I know that using the DETECTION_STRATEGY = NONE per the other response to the thread I could get rid of this step, but I need to update not just load the target table.


              Please advise
              • 4. Re: Generating target table primary key using Oracle sequences
                user121437
                To follow up with the initial posting, I got rid of the "java.sql.SQLException: ORA-02019: connection description for remote database not found" which had to do with a missing dblink.

                However, I am back to the original problem.

                If I map the target table primary key to the Oracle sequence via <%=odiRef.getObjectName( "L" , "SIM_SAMPLE_ID_SEQ" , "D" )%>.nextval

                i get the statement:

                ...
                and not exists (
                select 'X'
                from SIM.SIM_SAMPLE T
                * where T.SIM_SAMPLE_ID = SIM.SIM_SAMPLE_ID_SEQ.nextval*
                and ((C1_OPERATIONAL_SAMPLE_ID = T.OPERATIONAL_SAMPLE_ID) or
                ...

                hence the error:
                2287 : 42000 : java.sql.SQLException: ORA-02287: sequence number not allowed here


                If I don't map the target table primary key to anything, i get

                ...
                and not exists (
                select 'X'
                from SIM.SIM_SAMPLE T
                * where *
                and ((C1_OPERATIONAL_SAMPLE_ID = T.OPERATIONAL_SAMPLE_ID) or
                ...

                hence the error:
                936 : 42000 : java.sql.SQLException: ORA-00936: missing expression

                Looks like I can't use the Oracle sequence for the primary key.

                Please help
                • 5. Re: Generating target table primary key using Oracle sequences
                  667719
                  Perform all of these:

                  1. The sequence based column should not be the part of update key used.
                  2. Mark the sequence based mapping as insert-only or mark it as executable in target only.

                  Your problem should go away. NOT_EXISTS in my case creates performance constraints that is why I would like POST_FLOW option where rows will be marked as useless in a separate stage. That behavior is consistent with some of the KMs delivered with earlier ODI.