This discussion is archived
2 Replies Latest reply: Nov 21, 2012 10:02 AM by 975523 RSS

querying view definitions

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


ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:   
*Action:
Error at Line: 24 Column: 19

Thanks
vbwrangler

Edited by: user6406804 on Nov 21, 2012 7:44 AM
  • 1. Re: querying view definitions
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    This forum is NOT for sql or pl/sql questions. As the forum title says it is for sql developer questions only.

    Please mark the question ANSWERED and repost the question in the sql and pl/sql forum and provide your 4 digit Oracle version.
    SQL and PL/SQL
    >
    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.
  • 2. Re: querying view definitions
    975523 Newbie
    Currently Being Moderated
    Moved to the General Questions Area.

Legend

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