11 Replies Latest reply on Sep 22, 2009 10:16 PM by Frank Kulash

    "select * from cat" does not return anything

    723604
      Hello All,

      I have a 8i DB and SQL Plus. in SQL Plus, when I do "desc cat", it gave me this:
      SQL> desc cat;
      Name Null? Type
      ------------------------------- -------- ----
      TABLE_NAME NOT NULL VARCHAR2(30)
      TABLE_TYPE VARCHAR2(11)

      However when I do "select * from cat;", I though I would get a list of tables, but instead I got "no rows selected". Why is that?

      And the second question is very likely related to the first. I can "desc user_constraints" but when I tried to select some constraints, I got error like this:
      SQL> select constraint_name, constraint_type from user_constraints where tname = 'county_of_use';
      select constraint_name, constraint_type from user_constraints where tname = 'county_of_use'
      *
      ERROR at line 1:
      ORA-00904: invalid column name

      And I am NOT a dba. Any idea?

      Thanks,

      Jason
        • 1. Re: "select * from cat" does not return anything
          Frank Kulash
          Hi, Jason,
          user11925071 wrote:
          Hello All,

          I have a 8i DB and SQL Plus. in SQL Plus, when I do "desc cat", it gave me this:
          SQL> desc cat;
          Name Null? Type
          ------------------------------- -------- ----
          TABLE_NAME NOT NULL VARCHAR2(30)
          TABLE_TYPE VARCHAR2(11)

          However when I do "select * from cat;", I though I would get a list of tables, but instead I got "no rows selected". Why is that?
          The most likely reason is that the table has no data.
          Try:
          SELECT  COUNT (*)
          FROM    cat;
          to check.
          And the second question is very likely related to the first. I can "desc user_constraints" but when I tried to select some constraints, I got error like this:
          SQL> select constraint_name, constraint_type from user_constraints where tname = 'county_of_use';
          select constraint_name, constraint_type from user_constraints where tname = 'county_of_use'
          *
          ERROR at line 1:
          ORA-00904: invalid column name
          tname is not a column in user_constraints. Perhaps you meant table_name.
          Text inside quotes is case-sensitive. Most names are all upper-case, so you proabably want to say
          SELECT  constraint_name
          ,     constraint_type 
          FROM     user_constraints
          WHERE     table_name       = 'COUNTY_OF_USE';
          Edited by: Frank Kulash on Sep 22, 2009 5:12 PM
          Originally said "you probably want to say constraint_name'. I agree with the next 2 replies; table_name is more likely.
          1 person found this helpful
          • 2. Re: "select * from cat" does not return anything
            MaximDemenko
            Cat is the synonym to user_catalog view which can be used to query a couple of object_types owned by user ( such things like tables,cluster etc.). You can see the definition of that view even if you doesn't have any such objects in your schema, but in that case you get no rows returned from this view.

            The user_constraints view doesn't have a column tname, instead it has a table_name column.

            Best regards

            Maxim
            • 3. Re: "select * from cat" does not return anything
              thomaso
              Jason,
              1. View CAT shows only objects you own.
              In other words: if select * from cat returns no rows, you don't own any objects.

              2.select constraint_name, constraint_type from user_constraints where tname = 'county_of_use';
              should be
              select constraint_name, constraint_type from user_constraints where table_name = 'county_of_use';

              HTH
              Thomas
              1 person found this helpful
              • 4. Re: "select * from cat" does not return anything
                723604
                Thank you both. When I try "select count (*) from cat;" or "select count (*) from user_constraints", the results are 0.

                I don't quite understand this "view" thing. My guess is this is a predefined query. If that's the case, should it return something to me like examples on this website?
                http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#SECTION00056000000000000000
                • 5. Re: "select * from cat" does not return anything
                  MaximDemenko
                  To better understand views, it is probably the best approach to read about it in oracle documentation http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref787.
                  A stored query ( equally any other query) doesn't necessarily returns any rows. Regarding the examples on the mentioned by you website - could you elaborate, which examples you mean ( there are a bunch of them)?

                  Best regards

                  Maxim
                  • 6. Re: "select * from cat" does not return anything
                    Frank Kulash
                    Hi,
                    user11925071 wrote:
                    Thank you both. When I try "select count (*) from cat;" or "select count (*) from user_constraints", the results are 0.

                    I don't quite understand this "view" thing. My guess is this is a predefined query.
                    Yes, that's a good way to think of it. The results of that predefined query (like the results of any query) can be used as if they were a table.
                    Sometimes queries return 0 rows.
                    Sometimes tables have 0 rows.
                    If that's the case, should it return something to me like examples on this website?
                    http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#SECTION00056000000000000000
                    Could you copy a specific example, and paste it here? That's a huge page, and the link doesn't seem to go to a part relevant to this question.
                    • 7. Re: "select * from cat" does not return anything
                      723604
                      I missed Thomas' post. He is right that I don't own any of the tables in that DB. In fact I just got this old thing from others and we are thinging to upgrade it. Since there is no document, I guess first I need to find the relationship between these tables. Assume I have DBA previlidge, how can I find out the primary key, foreign key of all tables?
                      • 8. Re: "select * from cat" does not return anything
                        723604
                        Here is an axample from the webpage I mentioned:
                        SQL> COLUMN search_condition FORMAT A21
                        SQL> SELECT constraint_name, constraint_type,
                        search_condition, delete_rule
                        FROM user_constraints
                        WHERE table_name = 'EMPLOYEE';

                        CONSTRAINT_N CONSTRAINT_T SEARCH_CONDITION DELETE_RULE
                        ------------ ------------ --------------------- -----------
                        FK_DNO R CASCADE
                        SYS_C00886 C EMPID IS NOT NULL
                        SYS_C00887 C SSN IS NOT NULL
                        SYS_C00888 C SALARY IS NOT NULL
                        SYS_C00889 C DNO IS NOT NULL
                        CK_SEX C sex IN ('M', 'F')
                        CK_SALARY C salary > 10000
                        PK_EMP P

                        I didn't get anything like that. Perhpa because I am not a "own"?
                        • 9. Re: "select * from cat" does not return anything
                          MaximDemenko
                          You would have to query a view dba_constraints, the constraint_type column will show 'P' for primary keys and 'R' for referential integrity constraints ( foreign keys).

                          Best regards

                          Maxim
                          • 10. Re: "select * from cat" does not return anything
                            thomaso
                            Jason,
                            In fact I just got this old thing from others and we are thinking to upgrade it.<<
                            Meaning an old system with tables, views, packages and everything else....
                            Find out what is the name of the "owner" of all these objects - in Oracle terms is also called "schema".
                            Connect as this "owner" and all USER_... views will work ( like CAT, USER_TABLES, USER_VIEWS...)
                            or if you have DBA privs, use DBA_... views ( DBA_TABLES, .... )

                            I would also try to use some development tools like SQL Developer (free Oracle tool) , JDeveloper (free Oracle tool)
                            to reverse engineer old database.
                            Good luck.
                            And one more.
                            All Oracle Docs are in tahiti.oracle.com
                            It is a good idea to start "new upgrade" of old system with good understanding what is it running on.
                            Start with this one:
                            http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

                            HTH
                            Thomas
                            • 11. Re: "select * from cat" does not return anything
                              Frank Kulash
                              Hi,
                              user11925071 wrote:
                              SQL> SELECT constraint_name, constraint_type,
                              search_condition, delete_rule
                              FROM user_constraints
                              WHERE table_name = 'EMPLOYEE';
                              ...
                              I didn't get anything like that. Perhpa because I am not a "own"?
                              Did you mean to say "Perhaps because I am not an owner?"
                              Exactly. The user_x views only deal with objects that you own.
                              Most of the user_x views have corresponding all_x views, which deal with all the objects that you are allowed to use.

                              For example: most databases have a scott.emp table, that everyone is allowed to query.
                              You can see what constraints are on that table by querying all_constraints.
                              To see what columns are involved in those constraints, you have to join all_cons_columns, like this:
                              SELECT    l.column_name
                              ,       n.constraint_name
                              ,       n.constraint_type
                              FROM       all_cons_columns     l
                              JOIN       all_constraints     n     ON     n.constraint_name     = l.constraint_name
                              WHERE       n.owner          = 'SCOTT'
                              AND       n.table_name          = 'EMP'
                              AND       n.constraint_type     IN ('P', 'R')
                              ORDER BY  n.constraint_name
                              ,            l.position;