Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: querying view and table definitions

    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"
    Error at Line: 24 Column: 19

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    ALL_VIEWS.TEXT is datatype LONG & can not be used with VARCHAR2 functions

    You need to implement a different solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts