11-21-12, 13:16 #1Registered User
- 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
11-21-12, 13:21 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.