Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Query View or Table definitions

975523Nov 21 2012 — edited Nov 21 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 19 2012
Added on Nov 21 2012
3 comments
2,768 views