6 Replies Latest reply: Sep 14, 2006 11:10 AM by SueHarper RSS

    ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf

    336881
      When I'm connected to some databases and try to open the 'Functions' leaf (or 'Procedures', 'Indexes'... but it works fine with 'Tables') I get an ORA-00904: "PURGE_OBJECT" error. It happens also when I try 'Create FUNCTION'. This does not occur with all connected databases, but can happen even when the DB has version 10.2. I have the freshly updated sqldeveloper on Windows.
      The exception stack trace is:

      oracle.jdeveloper.cm.dt.browser.CBBaseFolder$ExceptionWrapper
           at oracle.jdeveloper.cm.dt.browser.CBBaseFolder._loadChildren(CBBaseFolder.java:288)
           at oracle.jdeveloper.cm.dt.browser.CBBaseFolder.refresh(CBBaseFolder.java:205)
           at oracle.jdevimpl.cm.dt.ConnectionController._refreshFolderCmd(ConnectionController.java:678)
           at oracle.jdevimpl.cm.dt.ConnectionController.refreshNode(ConnectionController.java:302)
           at oracle.jdevimpl.cm.dt.ConnectionController.handleEvent(ConnectionController.java:269)
           at oracle.ide.controller.IdeAction.performAction(IdeAction.java:530)
           at oracle.ide.controller.IdeAction$1.run(IdeAction.java:785)
           at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:804)
           at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:499)
           at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
           at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
           at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
           at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
           at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
           at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1000)
           at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1041)
           at java.awt.Component.processMouseEvent(Component.java:5488)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
      Caused by: oracle.jdeveloper.cm.CMException: ORA-00904: "PURGE_OBJECT": ungültiger Bezeichner

           at oracle.jdeveloper.cm.dt.browser.jdbc.ObjectFolder.getObjects(ObjectFolder.java:100)
           at oracle.jdeveloper.cm.dt.browser.jdbc.ObjectFolder.getChildrenList(ObjectFolder.java:147)
           at oracle.jdeveloper.cm.dt.browser.CBBaseFolder$1.doWork(CBBaseFolder.java:241)
           at oracle.ide.dialogs.ProgressRunnable.run(ProgressRunnable.java:159)
           at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:553)
           at java.lang.Thread.run(Thread.java:595)


      Thanks for any help!
        • 1. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
          484267
          904 error is for an invalid identifier and is coming directly from the database.

          Eric

          /////Doc
          string: invalid identifier

          Cause: The column name entered is either missing or invalid.

          Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.
          • 2. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
            336881
            I get the error by clicking on the plus sign in front of the 'Functions' leaf in the Connections tree. This means that SQLDeveloper is sending the database a query with an invalid identifier. I never entered 'PURGE_OBJECT'.
            I can't identify why I get this error only when being connected to some databases, and why it works for others.
            • 3. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
              336881
              Meanwhile I found that when I connect with user sys as sysdba, I can easily browse 'Other Users' and then all the functions of my user. The error appears only when I connect as a specific user (whose schema was imported from a 9.2 DB).
              • 4. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
                Laurent Schneider
                I suppose your schema owner does contain a regular TABLE which is called RECYCLEBIN but does not contain a PURGE_OBJECT column

                TEST CASE
                SQL> grant dba to JOHNY identified by JOHNY;
                 
                Grant succeeded.
                 
                SQL> create table JOHNY.RECYCLEBIN as select * from dual;
                 
                Table created.
                try to login as johny/johny in sql/developer

                than expand your VIEWS and you will get that error

                An error was encountered when performing the requested operation:
                ORA-00904: "PURGE_OBJECT" invalid identifier
                Vendor Code 904


                this is because sql/developer relies on the public synonym not on the user_recyclebin sys view
                SQL> connect johny/johny
                Connected.
                SQL> desc recyclebin
                Name                                      Null?    Type
                ----------------------------------------- -------- ----------------------------
                DUMMY                                              VARCHAR2(1)

                SQL> select * from dba_synonyms where synonym_name='RECYCLEBIN';

                OWNER                          SYNONYM_NAME
                ------------------------------ ------------------------------
                TABLE_OWNER                    TABLE_NAME
                ------------------------------ ------------------------------
                DB_LINK
                --------------------------------------------------------------------------------
                PUBLIC                         RECYCLEBIN
                SYS                            USER_RECYCLEBIN



                SQL> desc sys.user_recyclebin
                Name                                      Null?    Type
                ----------------------------------------- -------- ----------------------------
                OBJECT_NAME                               NOT NULL VARCHAR2(30)
                ORIGINAL_NAME                                      VARCHAR2(32)
                OPERATION                                          VARCHAR2(9)
                TYPE                                               VARCHAR2(25)
                TS_NAME                                            VARCHAR2(30)
                CREATETIME                                         VARCHAR2(19)
                DROPTIME                                           VARCHAR2(19)
                DROPSCN                                            NUMBER
                PARTITION_NAME                                     VARCHAR2(32)
                CAN_UNDROP                                         VARCHAR2(3)
                CAN_PURGE                                          VARCHAR2(3)
                RELATED                                   NOT NULL NUMBER
                BASE_OBJECT                               NOT NULL NUMBER
                PURGE_OBJECT                              NOT NULL NUMBER
                SPACE                                              NUMBER
                Until this bug is fixed, I would rather chose another user to connect
                HTH
                Laurent

                Message was edited by:
                Laurent Schneider
                SQL> select KEYWORD,RESERVED from v$reserved_words where keyword='RECYCLEBIN';
                 
                KEYWORD    R
                ---------- -
                RECYCLEBIN N
                since RECYCLEBIN is not a reserved, it is a legal name for your table, so the bug should be fixed one day...
                • 5. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
                  336881
                  Thanks a lot! This is exactly the case. So "recyclebin" will be an Oracle reserved word from now on.
                  • 6. Re: ORA-00904: "PURGE_OBJECT" when opening 'Functions' leaf
                    SueHarper
                    Thanks for the help and responses on this thread, I consider this now closed.

                    Sue