3 Replies Latest reply: Nov 21, 2012 2:02 PM by Osama_Mustafa RSS

    How to Query View or Table definitions

    975523
      I haven't used Oracle for a while and I am struggling with this one. I am doing some reporting on an Oracle Proprietary database there is no data diagram and I have to come up to speed as soon as possible. So I have two questions:
      1.     What is the best Reference Manual for Oracle?
      2.     I was trying to build a tool that I often use in SQL but running into a weird error. I pretty sure it has to do with the data type of the “Text” column but it is not cooperating with String functions on this column. The intent was to search the definitions of views or tables to find where a tables or columns are referenced. This is not the dependencies; this will go well beyond dependencies. The error happens with either of the commented out where clauses operating on the Text column but works fine on the Name or other columns. Any Ideas?

      SELECT b.owner, b.view_name, b.view_type, b.text, a.column_name, a.column_id
      from ALL_VIEWS B,all_tab_columns a
      -- WHERE b.text LIKE '%PO%'           -- generates the error
      -- WHERE INSTR((b.Text ), 'PO', 1,1) > 0          -- generates the error
      Where INSTR((b.view_name ), 'PO', 1,1) > 0          -- no error


      ORA-00932: inconsistent datatypes: expected NUMBER got LONG
      00932. 00000 - "inconsistent datatypes: expected %s got %s"
      *Cause:   
      *Action:
      Error at Line: 24 Column: 19
        • 1. Re: How to Query View or Table definitions
          sb92075
          user6406804 wrote:
          I haven't used Oracle for a while and I am struggling with this one. I am doing some reporting on an Oracle Proprietary database there is no data diagram and I have to come up to speed as soon as possible. So I have two questions:
          1.     What is the best Reference Manual for Oracle?
          2.     I was trying to build a tool that I often use in SQL but running into a weird error. I pretty sure it has to do with the data type of the “Text” column but it is not cooperating with String functions on this column. The intent was to search the definitions of views or tables to find where a tables or columns are referenced. This is not the dependencies; this will go well beyond dependencies. The error happens with either of the commented out where clauses operating on the Text column but works fine on the Name or other columns. Any Ideas?

          SELECT b.owner, b.view_name, b.view_type, b.text, a.column_name, a.column_id
          from ALL_VIEWS B,all_tab_columns a
          -- WHERE b.text LIKE '%PO%'           -- generates the error
          -- WHERE INSTR((b.Text ), 'PO', 1,1) > 0          -- generates the error
          Where INSTR((b.view_name ), 'PO', 1,1) > 0          -- no error


          ORA-00932: inconsistent datatypes: expected NUMBER got LONG
          00932. 00000 - "inconsistent datatypes: expected %s got %s"
          *Cause:   
          *Action:
          Error at Line: 24 Column: 19
          SQL> desc all_views
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           OWNER                                     NOT NULL VARCHAR2(30)
           VIEW_NAME                                 NOT NULL VARCHAR2(30)
           TEXT_LENGTH                                        NUMBER
           TEXT                                               LONG
           TYPE_TEXT_LENGTH                                   NUMBER
           TYPE_TEXT                                          VARCHAR2(4000)
           OID_TEXT_LENGTH                                    NUMBER
           OID_TEXT                                           VARCHAR2(4000)
           VIEW_TYPE_OWNER                                    VARCHAR2(30)
           VIEW_TYPE                                          VARCHAR2(30)
           SUPERVIEW_NAME                                     VARCHAR2(30)
           EDITIONING_VIEW                                    VARCHAR2(1)
           READ_ONLY                                          VARCHAR2(1)
          
          SQL> 
          as you can see TEXT is datatype LONG; which does not play nice with standard VARCHAR2 functions
          • 2. Re: How to Query View or Table definitions
            rp0428
            And I'll make the same reply I made in the original thread you posted in the sql developer forum since you didn't correct your code at all.
            querying view definitions
            >
            1. What is the best Reference Manual for Oracle?
            >
            Based only on the posted code you need to start with a SQL tutorial and learn the basics. Your code is using a CARTESIAN join and that is pretty much a rookie type of mistake.

            The Oracle docs can be found at: http://www.oracle.com/pls/db112/portal.all_books and the SQL Language covers all aspects of SQL including the INSTR function.
            >
            2. I was trying to build a tool that I often use in SQL Server but running into a weird error. I pretty sure it has to do with the data type of the “Text” column but it is not cooperating with String functions on this column. The intent was to search the definitions of views or tables to find where a tables or columns are referenced. This is not the dependencies; this will go well beyond dependencies.
            >
            That is not a weird error but is to be expected when a function is not used appropriately. If you use sql*plus and 'desc all_views' you will see that the datatype of the TEXT column is LONG and that datatype is very difficult to work with and only has limited support in SQL. Generally you need to use PL/SQL and write a function or procedure to work with LONGs effectively.
            >
            The error happens with either of the commented out where clauses operating on the Text column but works fine on the Name or other columns. Any Ideas?

            SELECT b.owner, b.view_name, b.view_type, b.text, a.column_name, a.column_id
            from ALL_VIEWS B,all_tab_columns a
            -- WHERE A.column_name LIKE '%PO%' -- generates the error
            -- WHERE INSTR((b.Text ), 'PO', 1,1) > 0 -- generates the error
            Where INSTR((b.view_name ), 'PO', 1,1) > 0 -- no error
            >
            As mentioned that query has several beginner level mistakes
            1. you are doing a CARTESIAN join of the two tables (ALL_VIEWS and ALL_TAB_COLUMNS) since you do not join them together.

            2. you filter on view_name but the CARTESIAN JOIN will join ALL records FOR EVERY TABLE from the ALL_TAB_COLUMNS view resulting in very large numbers of totally useless records.

            3. the first WHERE clause does not generate the error on 11gr2 but is also likely will not produce any useful results. The LIKE expression '%PO%' is on the COLUMN_NAME but in the 3rd WHERE clause you are using 'PO' as sthe view name. It is likely that one of those two references is wrong.

            4. the second where clause attempts to use the INSTR function on a LONG column and that function does not support that datatype.
            See the INSTR function in the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions073.htm
            >
            Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.
            You can find plenty of links for how to work with LONGs on this forum or on the web but working with them is not for beginners.
            • 3. Re: How to Query View or Table definitions
              Osama_Mustafa
              1.     What is the best Reference Manual for Oracle?
              Oracle Documentation that related to your Subject .
              2.     I was trying to build a tool that I often use in SQL but running into a weird error. I pretty sure it has to do with the data type of the “Text” column but it is not cooperating with String functions on this column. The intent was to search the definitions of views or tables to find where a tables or columns are referenced. This is not the dependencies; this will go well beyond dependencies. The error happens with either of the commented out where clauses operating on the Text column but works fine on the Name or other columns. Any Ideas?

              SELECT b.owner, b.view_name, b.view_type, b.text, a.column_name, a.column_id
              from ALL_VIEWS B,all_tab_columns a
              -- WHERE b.text LIKE '%PO%'           -- generates the error
              -- WHERE INSTR((b.Text ), 'PO', 1,1) > 0          -- generates the error
              Where INSTR((b.view_name ), 'PO', 1,1) > 0          -- no error


              ORA-00932: inconsistent datatypes: expected NUMBER got LONG
              00932. 00000 - "inconsistent datatypes: expected %s got %s"
              *Cause:   
              *Action:
              Error at Line: 24 Column: 19
              Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
              an object not intended for normal use, then do not access the restricted object.