Skip to Main Content

SQL & PL/SQL

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!

Long to Varchar conversion?

Greg SpallAug 13 2013 — edited Aug 14 2013

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!

This post has been answered by odie_63 on Aug 13 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 11 2013
Added on Aug 13 2013
12 comments
5,292 views