4 Replies Latest reply: Aug 27, 2014 6:05 AM by 2710660 RSS

    How to get schema name and assign to a column in target table

    2710660

      Hi Team,

       

      I am getting error while getting schema name and assign to  a target column.

       

      Implementation for the column:

       

      <%=odiRef.getSchemaName("W")%>

       

      ERROR:

      ===========

      ODI-1217: Session TEMP (342001) fails with return code 904.

      ODI-1226: Step TEMP fails after 1 attempt(s).

      ODI-1240: Flow TEMP fails while performing a Integration operation. This flow loads target table TEMP.

      ODI-1228: Task TEMP (Integration) fails on the target ORACLE connection ORACLE_MSR_SRC.

      Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "USERINROW": invalid identifier

       

       

        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)

      =============

      Implementation for the column:

       

      <%=odiRef.getSchemaName("ORACLE_MSR_SRC","DEVELOPMENT","W")%>

       

      com.sunopsis.tools.core.exception.SnpsSimpleMessageException: ODI-17517: Error during task interpretation.

      Task: 4

      java.lang.Exception: The application script threw an exception: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Exception getSchemaName("ORACLE_MSR_SRC", "DEVELOPMENT", "W") : SnpLSchema.getLSchemaByName(): SnpLschema does not exist BSF info: Insert new rows at line: 0 column: columnNo

        at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:489)

        at com.sunopsis.dwg.dbobj.SnpSessStep.createTaskLogs(SnpSessStep.java:737)

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:465)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)

        at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)

       

      Please help me how can get the name of the user ie... schema for that target and insert into the table.

       

      Thanks in Advance.. Suggestions or advice much appreciated.

       

      Regards,

      Kanthi

        • 1. Re: How to get schema name and assign to a column in target table
          2737251

          Use the Odi Subtstitution method getInfo()

           

          <%=odiRef.getInfo("pPropertyName")%>

           

          pPropertyName can be

          SRC_SCHEMA Name of the data schema in the source environment
          SRC_WORK_SCHEMA Name of the work schema in the source environment
          DEST_SCHEMA Name of the data schema in the target environment
          DEST_WORK_SCHEMA Name of the work schema in the target environment

           

          Regards,

          Bishwamilan Sahoo

          • 2. Re: How to get schema name and assign to a column in target table
            2710660

            Thanks Sahoo for your update.

             

            I have updated my code to get the info of the Dest_schema, I am able to see it in our step with my user. However, Its not updating the target table with the schema owner.

             

            Any idea what is causing the issue in updating, updating old records. The session completed successfully without errors.

             

            Regards,

            Kanthi

            • 3. Re: How to get schema name and assign to a column in target table
              2737251

              I believe you are using an Incremental Update IKM in the interface.

               

              And please provide some more information

               

              Regards,

              Bishwamilan Sahoo

              • 4. Re: How to get schema name and assign to a column in target table
                2710660

                Sahoo,

                 

                I am using control append as I am facing issue with oracle incremental update with the sequence.

                 

                It is throwing error as sequence not found.

                 

                ==========

                Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "S"."SEQUENCE_NO": invalid identifier

                =========

                Generated code while inserting:

                 

                insert /*+ append */ intoDMUSERMMMDW.I$_FBAC

                (

                BCCHRQ,
                BCCHRD,
                IND_UPDATE

                )

                select

                BCCHRQ,

                BCCHRD,
                IND_UPDATE

                from (

                 

                 

                 

                 

                select
                C1_C1 BCCHRQ,
                C2_C2 BCCHRD,

                 

                 

                'I' IND_UPDATE

                 

                 

                fromWMUSERGSMDW.C$_0FBAC
                where(1=1)

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                ) S

                where NOT EXISTS

                ( select 1 from SCI2_ARCH.FBAC T
                whereT.SEQUENCE_NO= S.SEQUENCE_NO
                and ((T.ACCHRQ = S.ACCHRQ) or (T.ACCHRQ IS NULL and S.ACCHRQ IS NULL)) and
                ((T.ACCHRD = S.ACCHRD) or (T.ACCHRD IS NULL and S.ACCHRD IS NULL))

                        )