5 Replies Latest reply: May 16, 2012 6:54 AM by Billy~Verreynne RSS

    How to perform a LIKE on a long datatype

    Mark1970
      I need to search in all the triggers which ones contain in their own body the string MYSTRING.
      Unlike the USER_SOURCE table for procedures and packages where it's possible to execute a

      select distinct name from user_source where upper(text) like '%MYSTRING%';

      how can I do the same for trigger where the trigger body is in a LONG type? I can't execute a upper(trigger_body) like '%MYSTRING%'; on USER_TRIGGERS, so how can I do?

      Thanks!
        • 1. Re: How to perform a LIKE on a long datatype
          Centinul
          I've had to do this a few times and typically what I do is create a temporary table using CREATE TABLE AS ... SELECT and select from the source table. I'll convert the LONG column to a CLOB column in the process so you can do your string searching on it. Once I'm done I'll drop my table.

          Hope this helps!
          • 2. Re: How to perform a LIKE on a long datatype
            John Spencer
            Unless you are on a really old version of Oracle, the trigger body is also stored in user_source. It has been since 9 something.

            John
            • 3. Re: How to perform a LIKE on a long datatype
              Igor.M
              select object_type, object_name, dbms_metadata.get_ddl(object_type, object_name)
              from  user_objects
              where (object_type, object_name) in
                  (
                  select distinct type, name
                  from user_source
                  where type = 'TRIGGER'
                          and upper(text) like '%BEFORE%INSERT%'            
                  );
              with tab as
              (
              select /*+ materialize*/ dbms_lob.substr(dbms_metadata.GET_SXML('TRIGGER', trigger_name),4000) text
              from USER_TRIGGERS
              )
              select *
              from tab
              where upper(text) like '%BEFORE%INSERT%' ;
              
              
              with tab as
              (
              select /*+ materialize*/ dbms_lob.substr(dbms_metadata.GET_SXML('TRIGGER', trigger_name),32000) text
              from USER_TRIGGERS
              )
              select *
              from tab
              where upper(text) like '%BEFORE%INSERT%' ;
              • 4. Re: How to perform a LIKE on a long datatype
                Billy~Verreynne
                Ouch. You need to convert that LONG into VARCHAR2 using a user defined function that uses dynamic SQL - in order to deal with that LONG as a string value in the SQL statement. Not a pretty thing to do, and limits you to the 1st 4000 bytes of the LONG. However, it does work.

                Another option would be to use this approach to output the LONG as a CLOB - should work. Have used this approach numerous times for LONG to VARCHAR2 conversion in SQL, so using a CLOB should not be a problem.

                The alternative is to deal with the LONG using PL/SQL code instead of trying to do it natively via SQL.

                The function I use looks as follows:
                create or replace function GetLong( 
                        tableName varchar2, 
                        longColumn varchar2, 
                        filterColumn TStrings,
                        filterValue TStrings 
                ) return varchar2 authid current_user is
                -- function:    GET LONG
                -- descr:       converts a LONG into a VARCHAR2
                -- (TStrings is: create or replace type TStrings as table of varchar2(4000); )
                --
                -- to do:
                -- tighten dynamic SQL to guard against SQL injection
                --      table name verification
                --      long column must be long and in table
                --      filter column list must be columns in table
                --
                        sqlStmt varchar2(4000);
                        c       integer;
                        rc      number;
                        longVal varchar2(4000);
                        longLen number := 4000;
                        buflen  number := 4000;
                        curPos  number := 0;
                begin
                        sqlStmt := 'select '||longColumn||' from '||tableName||' where 1=1 ';
                
                        for i in 1..filterColumn.Count
                        loop
                                sqlStmt := sqlStmt||' and '||filterColumn(i)||' = :'||i;
                        end loop;
                
                        c := dbms_sql.open_cursor;
                        dbms_sql.parse( 
                                c,
                                sqlStmt,
                                dbms_sql.native 
                        );
                
                
                        for i in 1..filterValue.Count 
                        loop
                                dbms_sql.bind_variable( c, trim(to_char(i)), filterValue(i) );
                        end loop;
                
                        dbms_sql.define_column_long( c, 1 );
                
                        rc := dbms_sql.execute( c );
                        rc := dbms_sql.fetch_rows( c );
                
                        if rc = 1 then
                                dbms_sql.column_value_long( c, 1, bufLen, curPos , longVal, longLen );
                        end if;
                
                        dbms_sql.close_cursor( c );
                
                        return( longVal );
                end;
                /
                And to use it:
                plsql-code...
                begin
                        select
                                x.column_name,
                                GetLong(
                                        'all_tab_cols',
                                        'data_default',
                                        TStrings('table_name', 'column_id', 'column_name'),
                                        TStrings(x.table_name, x.column_id, x.column_name )
                                )                       as data_default
                                        bulk collect into
                                colList, expList
                        from    all_tab_cols x
                        where   x.owner = targetSchema
                        and     x.table_name = targetTable
                        and     x.virtual_column = 'YES'
                        order by
                                x.column_id;
                ..
                end;
                If a normal table is used (and not views), it is easier as you simply pass the rowid of the row across as predicate to GetLong() for building the dynamic SQL to get that LONG and output VARCHAR2.
                • 5. Re: How to perform a LIKE on a long datatype
                  Billy~Verreynne
                  Here is an approach using dynamic SQL under the hood to convert a LONG into a CLOB - allowing one to perform LIKE conditions on LONG columns.

                  Obviously this is not exactly optimal - as LONG is a horrible and ugly and nasty data type. But this approach seems to work fine:
                  // need a string array for storing column names and column values for
                  // building dynamic SQL
                  SQL> create or replace type TStrings as table of varchar2(4000);
                    2  /
                  
                  Type created.
                  
                  // to perform the conversion, the name of the object (view/table) is needed, the name
                  // of the column containing the LONG, and 2 arrays - the 1st array contains the column(s)
                  // to use to identify a unique row and find the LONG, and the 2nd array contains the column
                  // values to use
                  SQL> create or replace function LongToClob(
                    2          tableName varchar2,
                    3          longColumn varchar2,
                    4          filterColumn TStrings,
                    5          filterValue TStrings
                    6  ) return clob authid current_user is
                    7          sqlStmt varchar2(4000);
                    8          c       integer;
                    9          rc      number;
                   10          strBuf  varchar2(32767);
                   11          bufLen  constant number := 32767;
                   12          bytesRd number;
                   13          currPos number;
                   14          buffer  clob;
                   15  begin
                   16          sqlStmt := 'select '||longColumn||' from '||tableName||' where 1=1 ';
                   17  
                   18          for i in 1..filterColumn.Count
                   19          loop
                   20                  sqlStmt := sqlStmt||' and '||filterColumn(i)||' = :'||i;
                   21          end loop;
                   22  
                   23          c := dbms_sql.open_cursor;
                   24          dbms_sql.parse(
                   25                  c,
                   26                  sqlStmt,
                   27                  dbms_sql.native
                   28          );
                   29  
                   30  
                   31          for i in 1..filterValue.Count
                   32          loop
                   33                  dbms_sql.bind_variable( c, trim(to_char(i)), filterValue(i) );
                   34          end loop;
                   35  
                   36          dbms_sql.define_column_long( c, 1 );
                   37  
                   38          rc := dbms_sql.execute( c );
                   39          rc := dbms_sql.fetch_rows( c );
                   40  
                   41          if rc = 1 then
                   42                  dbms_lob.CreateTemporary( buffer, true );
                   43                  bytesRd := bufLen;
                   44                  currPos := 0;
                   45  
                   46                  while bytesRd > 0 loop
                   47                          dbms_sql.column_value_long( c, 1, bufLen, currPos , strBuf, bytesRd );
                   48                          currPos := currPos + bytesRd;
                   49                          if bytesRd > 0 then
                   50                                  dbms_lob.WriteAppend( buffer, bytesRd, strBuf );
                   51                          end if;
                   52                  end loop;
                   53          end if;
                   54  
                   55          dbms_sql.close_cursor( c );
                   56  
                   57          return( buffer );
                   58  end;
                   59  /
                  
                  Function created.
                  
                  // ordinary LIKE search does not work of course
                  SQL> select
                    2          owner, view_name
                    3  from       all_views
                    4  where      owner = 'SYS'
                    5  and        text like '%DUAL%';
                  and     text like '%DUAL%'
                          *
                  ERROR at line 5:
                  ORA-00932: inconsistent datatypes: expected NUMBER got LONG
                  
                  // using the LongToClob() converter, the LIKE condition can now be used (important for the
                  // conversion function to be supplied with the correct parameters to identify the LONG in the
                  // appropriate row)
                  SQL> select
                    2          owner, view_name
                    3  from       all_views
                    4  where      owner = 'SYS'
                    5  and        LongToClob( 'ALL_VIEWS', 'TEXT', TStrings('OWNER','VIEW_NAME'), TStrings(OWNER,VIEW_NAME) ) like '%DUAL%';
                  
                  OWNER                          VIEW_NAME
                  ------------------------------ ------------------------------
                  SYS                            IMP8UEC
                  SYS                            EXU81CSC
                  SYS                            DICT_COLUMNS
                  SYS                            DICTIONARY
                  
                  SQL> 
                  A concern for this approach is obviously SQL injection. But then I doubt that one would ever want to expose the above to external users and applications. Typically this will be used by maintenance code on your data dictionary objects to perform some kind of data management function.