2 Replies Latest reply: Nov 21, 2012 12:02 PM by 975523 RSS

    querying view definitions

    975523
      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
          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
            Moved to the General Questions Area.