SQL & PL/SQL

I show exactly what you want as the final example in a post I made about using DBMS_XMLGen for dynamic SQL, that is, searching all columns for a specific value.

I like my dynamic SQL served as a DBMS_SQL dish.

SQL> create or replace type TStrings is table of varchar2(4000); 
 2 / 
 
Type created. 
 
SQL> 
SQL> <<plBlock>> 
 2 declare 
 3         -- table to process 
 4         TABLE_NAME     constant varchar2(30)  := 'EMP'; 
 5  
 6         -- string filter to search for 
 7         STRING_FILTER  constant varchar2(10)  := '%E%'; 
 8  
 9         -- for making pretty dynamic SQL 
 10         TAB            constant varchar2(1)   := chr(9); 
 11         LF             constant varchar2(1)   := chr(10); 
 12  
 13         c              integer;               -- dbms sql cursor handle 
 14         cur            sys_refcursor; 
 15         rc             integer;               -- dbms sql return code 
 16  
 17         colsList       TStrings;              -- string cols to search 
 18         buffer         TStrings;              -- string cols matches found 
 19         rid            varchar2(50);          -- rowid of row with matching col(s) 
 20  
 21         -- dynamic SQL 
 22         sqlProjection  varchar2(32767); 
 23         sqlPredicate   varchar2(32767); 
 24         dynamicSQL     varchar2(32767); 
 25 begin 
 26         -- get the string columns in the table 
 27         select 
 28                 column_name bulk collect into colsList 
 29         from   user_tab_cols 
 30         where  table_name = plBlock.TABLE_NAME 
 31         and    data_type like '%CHAR%' 
 32         order by column_id; 
 33  
 34         -- if no string colums then skip table 
 35         if colsList.count = 0 then 
 36                 return; 
 37         end if; 
 38  
 39         -- build dynamic SQL using colums on table 
 40         for i in 1 .. colsList.count loop 
 41                 if i = colsList.count then 
 42                         sqlProjection := sqlProjection||TAB||colsList(i); 
 43                 else 
 44                         sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; 
 45                 end if; 
 46  
 47                 if i = 1 then 
 48                         sqlPredicate := 'from'||TAB||TABLE_NAME||LF; 
 49                         sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; 
 50                 else 
 51                         sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; 
 52                 end if; 
 53         end loop; 
 54         dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; 
 55         dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); 
 56  
 57         -- we need to parse and bind dynamically 
 58         c := DBMS_SQL.Open_Cursor(); 
 59         DBMS_SQL.Parse( c, dynamicSQL, dbms_sql.NATIVE ); 
 60         DBMS_SQL.Bind_Variable( c, 'Filter', STRING_FILTER ); 
 61         rc := DBMS_SQL.execute( c ); 
 62  
 63         -- convert to a refcursor - we can use a static coded fetch as we know 
 64         -- the SQL projection returned is a rowid and column of type TStrings 
 65         cur := DBMS_SQL.to_RefCursor( c ); 
 66  
 67         dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); 
 68         loop 
 69                 fetch cur into rid, buffer; 
 70                 exit when cur%NotFound; 
 71  
 72                 -- determine which columns matches 
 73                 dbms_output.put( 'Table='||TABLE_NAME||' | rowid='||rid ); 
 74                 for i in 1 .. buffer.count loop 
 75                         if buffer(i) like STRING_FILTER then 
 76                                 dbms_output.put( ' | matched='||colsList(i)||' value='||buffer(i) ); 
 77                         end if; 
 78                 end loop; 
 79                 dbms_output.put_line( '' ); 
 80  
 81         end loop; 
 82         close cur; 
 83 end; 
 84 / 
 
Dynamic SQL: 
select rowid as RID, TStrings( 
       ENAME, 
       JOB 
) as COL 
from   EMP 
where  ENAME like :Filter 
or     JOB like :Filter 
 
Filter: <column> like '%E%' 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAA | matched=JOB value=CLERK 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAB | matched=ENAME value=ALLEN | matched=JOB value=SALESMAN 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAC | matched=JOB value=SALESMAN 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAD | matched=ENAME value=JONES | matched=JOB value=MANAGER 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAE | matched=JOB value=SALESMAN 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAF | matched=ENAME value=BLAKE | matched=JOB value=MANAGER 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAG | matched=JOB value=MANAGER 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAI | matched=JOB value=PRESIDENT 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAJ | matched=ENAME value=TURNER | matched=JOB value=SALESMAN 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAK | matched=JOB value=CLERK 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAL | matched=ENAME value=JAMES | matched=JOB value=CLERK 
Table=EMP | rowid=AAAUR+AABAAAR4ZAAN | matched=ENAME value=MILLER | matched=JOB value=CLERK 
 
PL/SQL procedure successfully completed. 
 
SQL>

This can be implemented as a pipeline table function, with parameters to specify the schema, tables and value to search for.

The pipeline version:

SQL> select * from table(SearchTables(filter=>'SALE%'));

OWNER      TABLE_NAME COLUMN_NAM ROW_ID               VALUE
---------- ---------- ---------- -------------------- ----------
BILLYV     DEPT       DNAME      AAAUR8AABAAAPvRAAC   SALES
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAB   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAC   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAE   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAJ   SALESMAN

5 rows selected.

SQL> select * from table(SearchTables(filter=>'AL%'));

OWNER      TABLE_NAME COLUMN_NAM ROW_ID               VALUE
---------- ---------- ---------- -------------------- ----------
BILLYV     EMP        ENAME      AAAUR+AABAAAR4ZAAB   ALLEN

1 row selected.

The code:

create or replace type TStrings is table of varchar2(4000); 
/ 
 
create or replace type TRowMatch is object( 
       owner          varchar2(128), 
       table_name     varchar2(128), 
       column_name    varchar2(128), 
       row_id         varchar2(100), 
       value          varchar2(4000) 
); 
/ 
 
create or replace type TRowMatchTable is table of TRowMatch; 
/ 
 
create or replace function SearchTables( owner varchar2 default user(), tableName varchar2 default '%', filter varchar2 ) 
return TRowMatchTable pipelined is 
       -- for making pretty dynamic SQL 
       TAB            constant varchar2(1)   := chr(9); 
       LF             constant varchar2(1)   := chr(10); 
 
       c              integer;               -- dbms sql cursor handle 
       cur            sys_refcursor; 
       rc             integer;               -- dbms sql return code 
 
       colsList       TStrings;              -- string cols to search 
       buffer         TStrings;              -- string cols matches found 
       rid            varchar2(50);          -- rowid of row with matching col(s) 
 
       -- dynamic SQL 
       sqlProjection  varchar2(32767); 
       sqlPredicate   varchar2(32767); 
       dynamicSQL     varchar2(32767); 
begin 
       for tabList in( 
               select 
                       owner, table_name 
               from   all_tables 
               where  owner like SearchTables.owner 
               and    table_name like SearchTables.tableName 
               order by 1,2 
       ) loop 
               -- get the string columns in the table 
               select 
                       column_name bulk collect into colsList 
               from   all_tab_cols 
               where  owner = tabList.owner 
               and    table_name = tabList.table_name 
               and    data_type like '%CHAR%' 
               order by column_id; 
 
               -- if no string colums then skip table 
               if colsList.count = 0 then 
                       exit; 
               end if; 
 
               -- build dynamic SQL using colums on table 
               sqlProjection := null; 
               sqlPredicate := null; 
               for i in 1 .. colsList.count loop 
                       if i = colsList.count then 
                               sqlProjection := sqlProjection||TAB||colsList(i); 
                       else 
                               sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; 
                       end if; 
 
                       if i = 1 then 
                               sqlPredicate := 'from'||TAB||tabList.table_name||LF; 
                               sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; 
                       else 
                               sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; 
                       end if; 
               end loop; 
               dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; --'
       --     dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); 
 
               -- we need to parse and bind dynamically 
               c := DBMS_SQL.Open_Cursor(); 
               DBMS_SQL.Parse( c, dynamicSQL, DBMS_SQL.NATIVE ); 
               DBMS_SQL.Bind_Variable( c, 'Filter', filter ); 
               rc := DBMS_SQL.execute( c ); 
 
               -- convert to a refcursor - we can use a static coded fetch as we know 
               -- the SQL projection returned is a rowid and column of type TStrings 
               cur := DBMS_SQL.to_RefCursor( c ); 
 
       --     dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); 
               loop 
                       fetch cur into rid, buffer; 
                       exit when cur%NotFound; 
 
                       -- determine which columns matches 
                       for i in 1 .. buffer.count loop 
                               if buffer(i) like filter then 
                                       pipe row( 
                                               TRowMatch( 
                                                       tablist.owner, 
                                                       tabList.table_name, 
                                                       colsList(i), rid, buffer(i) 
                                               ) 
                                       ); 
                               end if; 
                       end loop; 
               end loop; 
 
               close cur; 
       end loop; 
       return; 
end; 
/