2 Replies Latest reply: Feb 26, 2013 8:14 PM by rp0428 RSS

    LIKE and LONG

    user3937182
      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
          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
            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.