Ok, so I'm trying to scan the source code of compiled views. so I start off with a simple:
set long 1000000
select text from dba_views where view_name = 'DBA_VIEWS';
I'll use DBA_VIEWS just to keep the results short, because everyone has it .. and it's mildly amusing to select from itself
In reality, I'll have a broader where clause.
So, now I want to search the text for certain schema and table references. So let's just look for "sys".
select text
from dba_views
where view_name = 'DBA_VIEWS'
and instr(text, 'sys') > 0;
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Great.
Ok, so I do a search to try to figure out to get "convert" a long to varchar, and every solution I find involves "migrating" the data. In other words, it assumes you created your own TABLE with a LONG data type, and need to MOVE it into another TABLE. That's not even close to what I'm doing here. I'm just querying the data.
So I come across this little gem:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:839298816582
Sweet!!
So I give that a try:
(I renamed his function, but did not change any other logic, nor any other renaming).
CREATE OR REPLACE FUNCTION f_long_to_varchar
( p_tname IN VARCHAR2,
p_cname IN VARCHAR2,
p_rowid IN ROWID
)
RETURN VARCHAR2
AS
l_cursor INTEGER DEFAULT dbms_sql.open_cursor;
l_n NUMBER;
l_long_val VARCHAR2(4000);
l_long_len NUMBER;
l_buflen NUMBER := 4000;
l_curpos NUMBER := 0;
BEGIN
dbms_sql.parse ( l_cursor,
'SELECT ' || p_cname || ' FROM ' || p_tname ||
' WHERE rowid = :x',
dbms_sql.native );
dbms_sql.bind_variable ( l_cursor, ':x', p_rowid );
dbms_sql.define_column_long ( l_cursor, 1 );
l_n := dbms_sql.execute ( l_cursor );
IF ( dbms_sql.fetch_rows ( l_cursor ) > 0 ) THEN
dbms_sql.column_value_long ( l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
END IF;
dbms_sql.close_cursor ( l_cursor );
RETURN l_long_val;
END f_long_to_varchar;
/
select f_long_to_varchar(text) from dba_views where view_name = 'DBA_VIEWS';
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Great. So, anyone have any other ideas?
How can I query the dba_views and scan the text field as a varchar ?
Can it be done without moving the data to a temp table? Either case - how to do it?
I'm coming up absolutely blank on this.
Thanks!