3 Replies Latest reply: Feb 27, 2008 9:35 AM by 580180 RSS

    ORA-00933: SQL command not properly ended

    133792
      I am getting following error message when I refresh the cube,
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      We have applied OLAP A patch..
      =========================================

      java.sql.SQLException: ORA-00933: SQL command not properly ended


      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)

      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:671)

      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:597)

      at oracle.jdbc.driver.T2CStatement.executeForDescribe(T2CStatement.java:777)

      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1030)

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1123)

      at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1678)

      at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1644)

      at oracle.olap.awm.dataobject.dialog.olapcatalog.TableDialog.populateTableDataModel(TableDialog.java:164)

      at oracle.olap.awm.dataobject.dialog.olapcatalog.TableDialog.initialiseAndPopulate(TableDialog.java:68)

      at oracle.olap.awm.dataobject.dialog.olapcatalog.TableDialog.<init>(TableDialog.java:57)

      at oracle.olap.awm.wizard.awbuild.BuildWizardHelper.runBuild(BuildWizardHelper.java:252)

      at oracle.olap.awm.navigator.node.modeler.cube.ModelerCubeNode.actionPerformed(ModelerCubeNode.java:480)

      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)

      at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)

      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)

      at javax.swing.AbstractButton.doClick(Unknown Source)

      at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)

      at javax.swing.plaf.basic.BasicMenuItemUI$MouseInputHandler.mouseReleased(Unknown Source)

      at java.awt.Component.processMouseEvent(Unknown Source)

      at java.awt.Component.processEvent(Unknown Source)

      at java.awt.Container.processEvent(Unknown Source)

      at java.awt.Component.dispatchEventImpl(Unknown Source)

      at java.awt.Container.dispatchEventImpl(Unknown Source)

      at java.awt.Component.dispatchEvent(Unknown Source)

      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)

      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)

      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)

      at java.awt.Container.dispatchEventImpl(Unknown Source)

      at java.awt.Window.dispatchEventImpl(Unknown Source)

      at java.awt.Component.dispatchEvent(Unknown Source)

      at java.awt.EventQueue.dispatchEvent(Unknown Source)

      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

      at java.awt.EventDispatchThread.run(Unknown Source)
        • 1. Re: ORA-00933: SQL command not properly ended
          20399
          It might be related to published bug.


          Symptoms
          =========

          STEPS TO REPRODUCE;
          ====================

          1. In AWM 10.2.0.3, attach your AW
          2. Run maintain wizard
          3. Choose to run the maintain procedure immediately
          4. You receive the following error:

          ORA 00933: SQL COMMAND NOT PROPERLY ENDED.

          Cause
          ======

          The reason is because the OLAPSYS.XML_LOAD_LOG has over 1000 rows Or XML_LOADID_SEQUENCE has too many values.

          NOTE: If you check the XML_LOAD_LOG table, the procedure completes ok. It only fail
          s to display the usual messages at the end of the procedure that you normally se
          e in AWM.

          Solution
          =======

          In Sql plus, log in as sys as sysdba:
          You might want to save the contents of the table before doing the first step:

          1. delete * from olapsys.xml_load_log;
          2. drop SEQUENCE "OLAPSYS"."XML_LOADID_SEQUENCE" ;

          – This is how the sequence was defnied in OH/olap/admin/cmwinst.sql originally
          --create sequence XML_LOADID_SEQUENCE;

          – In testing, we used the following explicit statement instead of the above one:
          create SEQUENCE "OLAPSYS"."XML_LOADID_SEQUENCE" MINVALUE 1 MAXVALUE
          999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

          3. The following line is VERY important
          grant select on olapsys.XML_LOADID_SEQUENCE to public;

          4. Now maintain a dimension, cube or AW.



          Hope this helps,
          Swapan.
          • 2. Re: ORA-00933: SQL command not properly ended
            133792
            Swapan,
            This bug is fixed in Olap patch A(5746153), we have applied Patch A.

            # Bug 5753198 Load fails when XML_LOAD_LOG has > 1000 rows


            Thanks;
            Ajaz
            • 3. Re: ORA-00933: SQL command not properly ended
              580180
              Hi Ajaz,

              I would like to share some of our experiences here - the problem that you have reported is something that we get every now and then - and the solution described by Swapan is what works for us! And all this happens when we have not only the OLAP 'A' patch - but numerous others that cater to a variety of bugs.

              What we have experienced is that patch or no patch - if you get the problem that you have then apply the solution as mentioned by Swapan.

              One more thing - one strange thing that I have noticed is that if for some reason this problem is not resolved even after applying the suggested solution, then the best way to get over it is to uninstall AWM client and re-install it again.

              This may sound weird - but this is what we have seen happening!!

              Hope this helps!

              Thanks and Regards,
              Piyush