7 Replies Latest reply on May 20, 2008 11:41 AM by Aman....

    ORA-900 Invalid Sql Statement.

    AlokKumar
    AlokKumar May 20, 2008 9:53 AM
      Hi,

      I've got the strange problem, please go through with my findings.


      BANNER
      ----------------------------------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
      PL/SQL Release 9.2.0.8.0 - Production
      CORE 9.2.0.8.0 Production
      TNS for Solaris: Version 9.2.0.8.0 - Production
      NLSRTL Version 9.2.0.8.0 - Production


      chry_pm_stg@PUBINT> SELECT data_utils.column_list('CHRY_PM_STG.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL;

      DATA_UTILS.COLUMN_LIST('CHRY_PM_STG.PARTS@EPC_TARGET',0,0,NULL,1,NULL,NULL,0)
      -----------------------------------------------------------------------------
      PARTID,PARTNO,PARTNAME,PARTLANGID


      BANNER
      ------------------------------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
      PL/SQL Release 9.2.0.5.0 - Production
      CORE 9.2.0.6.0 Production
      TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
      NLSRTL Version 9.2.0.5.0 - Production

      chry_pm_aj@W9ITEST> SELECT data_utils.column_list('CHRY_PM_AJ.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL;
      SELECT data_utils.column_list('CHRY_PM_AJ.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL
      *
      ERROR at line 1:
      ORA-00900: invalid SQL statement
      ORA-06512: at "CHRY_PM_AJ.DATA_UTILS", line 492
      ORA-06512: at line 1

      In the above code, Data Utils is a package and column_list is a function.As you can clearly see, the above code works well in first case, but in the second case, the same code does return an error, howerve the database version is not same.

      hare krishna
      Alok
      • 6256 Views
      • Tags:
        • 1. Re: ORA-900 Invalid Sql Statement.
          Lukasz Mastalerz
          Lukasz Mastalerz May 20, 2008 10:19 AM (in response to AlokKumar)
          Could you paste here line 492 of data_utils package? Maybye there are some different parameters settings on those databases?
          • 2. Re: ORA-900 Invalid Sql Statement.
            AlokKumar
            AlokKumar May 20, 2008 10:26 AM (in response to Lukasz Mastalerz)
            Thanks Lukas, but I've copied the same package to the schema, where I am getting the errors.However, I'll paste the those lines.

            CHRY_PM_AJ
            OPEN c_RefCur FOR -- line 492
            'SELECT CASE ' ||
            'WHEN ' || p_IncExcStyle || ' = ' || INCLUDE_BUT_NULL || ' AND ' ||
            'l = 1 THEN ' ||

            CHRY_PM_STAGE

            OPEN c_RefCur FOR
            'SELECT CASE ' ||
            'WHEN ' || p_IncExcStyle || ' = ' || INCLUDE_BUT_NULL || ' AND ' ||
            'l = 1 THEN ' ||
            '''NULL'' || DECODE(' || p_AS || ', 1, '' AS '' || c)' ||
            'ELSE ' ||
            'DECODE(' || p_NVL || ', 1,' ||

            hare krishna
            Alok
            • 3. Re: ORA-900 Invalid Sql Statement.
              Aman....
              Aman.... May 20, 2008 10:42 AM (in response to AlokKumar)
              Alok,
              2 things I would say,
              1)I hope you are familiar that there is a code tag that you can use to format your code.Please use it otherwise it becomes harder to read.
              2)I amnot sure that by looking at a snippet of a code, me atleast wil lbe able to point on some thing.The data versions are different.Did this package got compiled successfully in the first place or not?
              I shall suggest try running offending sql manualy on sqlplus and see if it works or not.
              Aman....
              • 4. Re: ORA-900 Invalid Sql Statement.
                AlokKumar
                AlokKumar May 20, 2008 10:45 AM (in response to Aman....)
                These are just a few lines and not the complete code. I've also tried it to run from SQLPLUS, but it's coming out with the same error. Also, I've copied the same code to the CHRY_PM_AJ schema, where we are facing problems. According
                to me, the problem is caused by different database versions, As you can clearly see in BANNER section. But I am not sure, I'vle aslo tried to execute the same
                lines of code on 9.2.0.8.0 and it's works fine. what's you say ?

                hare krishna
                Alok
                • 5. Re: ORA-900 Invalid Sql Statement.
                  Aman....
                  Aman.... May 20, 2008 10:49 AM (in response to AlokKumar)
                  Well I asked to check the offending query only.If that isnot working even and you think that version is the issue than I guess raise an SR and let Support handle it.
                  Aman....
                  • 6. Re: ORA-900 Invalid Sql Statement.
                    AlokKumar
                    AlokKumar May 20, 2008 11:11 AM (in response to Aman....)
                    I was discussing the same thing. I meant, I did try to run the offending query on the different database version and it wored fine! Below are my findings.

                    ELECT data_utils.column_list('COMPARE_PM.PARTS@TEST1', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL;

                    DATA_UTILS.COLUMN_LIST('CHRY_PM_STG.PARTS@EPC_TARGET',0,0,NULL,1,NULL,NULL,0)
                    -----------------------------------------------------------------------------
                    PARTID,PARTNO,PARTNAME,PARTLANGID

                    hare krishna
                    Alok
                    • 7. Re: ORA-900 Invalid Sql Statement.
                      Aman....
                      Aman.... May 20, 2008 11:41 AM (in response to AlokKumar)
                      Alok,
                      Than support is the best resource to talk with for this issue.
                      Aman....