This discussion is archived
3 Replies Latest reply: Nov 21, 2012 12:02 PM by Osama_Mustafa RSS

How to Query View or Table definitions

975523 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points