SQL & PL/SQL

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!