1 2 Previous Next 18 Replies Latest reply: Feb 11, 2013 6:57 AM by EdStevens RSS

    Dual table not working

    988014
      i am using datbase "hr" and passwrod: ****. I am querying dual table it displays error

      while querying select * from sys.dual its working

      user: hr

      select * from dual ------------------- not working

      select * from sys.dual ------------------- working

      can drop table also
        • 1. Re: Dual table not working
          Osama_Mustafa
          where is the question ?

          Also DB Version and OS version
          • 2. Re: Dual table not working
            A.Ebenezer
            985011 wrote:
            i am using datbase "hr" and passwrod: ****. I am querying dual table it displays error

            while querying select * from sys.dual its working

            user: hr

            select * from dual ------------------- not working
            what's that "not working"....does it show any error message?
            • 3. Re: Dual table not working
              sb92075
              985011 wrote:
              i am using datbase "hr" and passwrod: ****. I am querying dual table it displays error

              while querying select * from sys.dual its working

              user: hr

              select * from dual ------------------- not working

              select * from sys.dual ------------------- working

              can drop table also
              see a picture of my car that is not working.

              How do I ask a question on the forums?
              SQL and PL/SQL FAQ
              • 4. Re: Dual table not working
                988014
                yes i am getting error

                ORA-02201: sequence not allowed here

                os using windows 7 and oracle version is 10g

                Edited by: 985011 on Feb 10, 2013 8:01 AM

                [http://blessingmch.org/dual.png]

                Edited by: 985011 on Feb 10, 2013 8:03 AM
                • 5. Re: Dual table not working
                  sb92075
                  985011 wrote:
                  yes i am getting error

                  ORA-02201: sequence not allowed here
                  since we can not see what you are actually doing, we can not say what you are doing wrong.

                  COPY & PASTE the whole session; what you do & how Oracle responds
                  • 6. Re: Dual table not working
                    rgoold
                    Have you then created a sequence called DUAL in the HR schema? If you have then you should drop it.

                    It's a bad idea to create an object called DUAL because applications the world over, and the database itself, rely on the single row SYS.DUAL table to function properly - if you have created a DUAL object in the HR schema then it will take precedence over the public synonym when logged in as that user.
                    • 7. Re: Dual table not working
                      988014
                      [http://blessingmch.org/dual.png]
                      • 8. Re: Dual table not working
                        sb92075
                        post results from following SQL
                        SQL> select owner, object_type from all_objects where object_name = 'DUAL';
                        
                        OWNER                          OBJECT_TYPE
                        ------------------------------ -------------------
                        SYS                            TABLE
                        PUBLIC                         SYNONYM
                        
                        SQL> DESC DUAL
                         Name                                      Null?    Type
                         ----------------------------------------- -------- ----------------------------
                         DUMMY                                              VARCHAR2(1)
                        
                        SQL> 
                        • 9. Re: Dual table not working
                          988014
                          These are the values i am getting

                          OWNER     OBJECT_TYPE
                          ------------------------------------

                          SYS     TABLE
                          PUBLIC     SYNONYM
                          HR     SEQUENCE

                          when querying

                          desc dual

                          result:

                          Object Type      SEQUENCE     Object      DUAL

                          Edited by: 985011 on Feb 10, 2013 8:46 AM
                          • 10. Re: Dual table not working
                            sb92075
                            [oracle@localhost ~]$ sqlplus hr/hr
                            
                            SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 10 08:50:55 2013
                            
                            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                            
                            
                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            
                            SQL> select object_type, count(*) from user_objects group by object_type;
                            
                            OBJECT_TYPE           COUNT(*)
                            ------------------- ----------
                            SEQUENCE                     3
                            PROCEDURE                    2
                            TRIGGER                      2
                            INDEX                       19
                            TABLE                        7
                            VIEW                         1
                            
                            6 rows selected.
                            
                            SQL> select object_name from user_objects where object_type = 'SEQUENCE';
                            
                            OBJECT_NAME
                            --------------------------------------------------------------------------------
                            LOCATIONS_SEQ
                            DEPARTMENTS_SEQ
                            EMPLOYEES_SEQ
                            
                            SQL> SELECT EMPLOYEES_SEQ FROM DUAL;
                            SELECT EMPLOYEES_SEQ FROM DUAL
                                   *
                            ERROR at line 1:
                            ORA-00904: "EMPLOYEES_SEQ": invalid identifier
                            
                            
                            SQL> SELECT EMPLOYEES_SEQ.NEXTVAL FROM DUAL;
                            
                               NEXTVAL
                            ----------
                                   207
                            • 11. Re: Dual table not working
                              ***Anuj***
                              SQL> select count(*) from dual;
                              select count(*) from dual
                              *
                              ERROR at line 1:
                              ORA-00942: table or view does not exist


                              SQL> select count(*) from sys.dual;

                              COUNT(*)
                              ----------
                              1

                              SQL> select synonym_name from dba_synonyms where synonym_name='DUAL';

                              no rows selected

                              SQL> create public synonym dual for sys.dual;

                              Synonym created.

                              SQL> select count(*) from dual;

                              COUNT(*)
                              ----------
                              1

                              HTH
                              Anuj
                              http://www.oracle-12c.com/
                              • 12. Re: Dual table not working
                                Mark Williams-Oracle
                                ***Anuj*** wrote:
                                SQL> select count(*) from dual;
                                select count(*) from dual
                                *
                                ERROR at line 1:
                                ORA-00942: table or view does not exist


                                SQL> select count(*) from sys.dual;

                                COUNT(*)
                                ----------
                                1

                                SQL> select synonym_name from dba_synonyms where synonym_name='DUAL';

                                no rows selected

                                SQL> create public synonym dual for sys.dual;

                                Synonym created.

                                SQL> select count(*) from dual;

                                COUNT(*)
                                ----------
                                1

                                HTH
                                Anuj
                                http://www.oracle-12c.com/
                                That's all well and good, but how is it relevant to the OP?

                                The output previously provided by OP shows that a sequence called "dual" was created in HR:
                                985011 wrote:
                                These are the values i am getting

                                OWNER     OBJECT_TYPE
                                ------------------------------------

                                SYS     TABLE
                                PUBLIC     SYNONYM
                                HR     SEQUENCE

                                when querying

                                desc dual

                                result:

                                Object Type      SEQUENCE     Object      DUAL

                                Edited by: 985011 on Feb 10, 2013 8:46 AM
                                That seems more relevant to me. Especially given the error is "ORA-02201: sequence not allowed here".

                                @OP see rgoold's post earlier in the thread.
                                • 13. Re: Dual table not working
                                  rp0428
                                  >
                                  desc dual

                                  result:

                                  Object Type SEQUENCE Object DUAL
                                  >
                                  As already stated that means you have a SEQUENCE named 'DUAL'.

                                  Never give an object the same name as a system object or use a name that is a reserved word.

                                  Consider yourself lucky that you can easily fix your error by dropping your sequence named 'DUAL'. If you had instead created your own TABLE named DUAL or, worse, altered the standard DUAL table or modified the data in it (e.g. added more rows) you could have caused serious problems.

                                  Drop the sequence named 'DUAL' and create a new one with a more appropriated name. Then modify any code that used the old sequence to use the new one.
                                  • 14. Re: Dual table not working
                                    ***Anuj***
                                    Agreed Mark :)

                                    SQL> select count(*) from dual;

                                    COUNT(*)
                                    ----------
                                    1

                                    SQL> CREATE SEQUENCE DUAL;

                                    Sequence created.

                                    SQL> select count(*) from dual;
                                    select count(*) from dual
                                    *
                                    ERROR at line 1:
                                    ORA-02201: sequence not allowed here


                                    SQL> drop sequence dual;

                                    Sequence dropped.

                                    SQL> select count(*) from dual;

                                    COUNT(*)
                                    ----------
                                    1

                                    HTH
                                    Anuj
                                    http://www.oracle-12c.com/
                                    1 2 Previous Next