3 Replies Latest reply: Apr 4, 2014 5:38 AM by Sowjanya Rayala RSS

    ODI-1228. ORA-00936: missing expression

    Sowjanya Rayala

      Hi All,

       

      I have an existing mapping where I will be loading flatfile data to Hyperion Essbase 11.

      This is achieved in 2 interfaces.

      1.      1 Flat file, 1 Oracle table to Oracle(staging)

      2.       Oracle(Staging) to Hyperion Essbase

       

      I am currently working on interface 1 which has 2 sources: a flat file and a oracle table with a join condition and a oracle target . The change is addition of 2 new columns. I have added 2 new columns to the flat file. I have added these 2 columns to the target oracle table also. I did not changed any of the KMS which are :

       

      1.       Oracle File to SQL

      2.       LKM SQL to Oracle

      3.       IKM SQL Control Append – Mindstream – DataQuality – Testing

       

            Staging Area Diff From Target is not checked.

      Data is not getting populated to target oracle table. I get the following error.

       

                Error: ODI-1228: Task TXT_ORA Load 7B data_V2 (Integration) fails on the target ORACLE connection ORA_HYPTEST.

                Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

       

      I tested the connection in the physical schema and is fine. I am able to see the table structure also in the mapping tab with which I think I am connected to target.

      Kindly advice.

       

      Thanks

       

        • 1. Re: ODI-1228. ORA-00936: missing expression
          $anty

          Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

          This is runtime exception/error which is occurring when the SQL Code is actually getting executed.

           

                I tested the connection in the physical schema and is fine. I am able to see the table structure also in the mapping tab with which I think I am connected to target

          It may be issue with the mapping/connection, but could be with the SQL code/syntax.

           

          Can you test the code generated by ODI, with SQL Developer or similar client?

           

          Regards,

          Santy.

          • 2. Re: ODI-1228. ORA-00936: missing expression
            Phanikanth

            Hi

             

            Can you please open the failed step from ODi operator and goto code tab and copy and paste the code in SQl prompt and execute manually and see the same error message is coming or not, if yesy there is mistake happen on interface mapping.

            hope this will help you to track the issue

             

            Regards,
            Phanikanth

            • 3. Re: ODI-1228. ORA-00936: missing expression
              Sowjanya Rayala

              Thank you for the response.

               

              Now, I am getting following error :

              ODI-1228: Task Copy of TXT_ORA Load test 1-1 done (Integration) fails on the target ORACLE connection ORA_TEST.

              Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

               

              I have opened and looked at

               

              the code.

              Production is executing fine and Test is failing.So, I have comapred the code of Prod and Test.I didnot find any difference in the code except for the temp table names.

              I interpret following could be reasons, Please advice what i am thinking is correct or not

               

              1. There are some I$ and SRC_TABLE temporary tables created in both codes.

                   I think Test is not able to generate those temporary tables or is generating in a different schema.

               

              2.      Staging Area Different From Target is not checked.

                   ORA_ODI_STAGE is a staging schema created for staging purpose and same is used for staging.

                   I think, the staging schema is related to Prod and I have to select schema realted to Test. I am not sure how do I change this.

                    Though, I am changing the context from Prod to Test or Dev, I am not able to view the list of schemas available in respective Contexts.

               

              Thank you