This discussion is archived
2 Replies Latest reply: Feb 26, 2013 6:14 PM by rp0428 RSS

LIKE and LONG

750713 Newbie
Currently Being Moderated
We need to scan View code using queries like:

select view_name
from user_views
where text like '%bad_code%';

but, because of the LONG datatype of the TEXT column, we can't.

Any alternatives ?
  • 1. Re: LIKE and LONG
    sb92075 Guru
    Currently Being Moderated
    user3937182 wrote:
    We need to scan View code using queries like:

    select view_name
    from user_views
    where text like '%bad_code%';

    but, because of the LONG datatype of the TEXT column, we can't.

    Any alternatives ?
    I would simply extract application source code from my Source Code Repository; then do as below

    grep -i bad_code application.txt
  • 2. Re: LIKE and LONG
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    We need to scan View code using queries like:

    select view_name
    from user_views
    where text like '%bad_code%';

    but, because of the LONG datatype of the TEXT column, we can't.

    Any alternatives ?
    >
    Sure - the simplest way is to just create your own table that uses a CLOB and insert the data from the view. Then search the CLOB.
    create view emp_vw as select * from emp
    
    desc user_views
    
    create table my_views (view_name varchar2(30), text clob)
    
    insert into my_views 
    select view_name, to_lob(text)
    from user_views
    The TO_LOB function can convert the LONG to a CLOB but only when used in an INSERT statement.

    See the SQL Language doc.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions193.htm
    >
    Purpose

    TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement.

    Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONG values, create a CLOB column. To convert LONG RAW values, create a BLOB column.

    You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points