developers

    Forum Stats

  • 3,873,766 Users
  • 2,266,638 Discussions
  • 7,911,626 Comments

Discussions

Find Columns that have a particular Value

verde1030
verde1030 Member Posts: 44 Red Ribbon

Hello All, is there a way to scan a particular Schema in an Oracle DB and try to find a particular 'Value' in a Column Name?


Background: Customers needed a new field that is not available in our Warehouse layer. When i spoke to the folks from our Front End Team, they pointed out to a particular 'Value' in their XML that i needed.

I know that our ETL process parses through the XML and populates data across multiple tables in our PSTG schema. 

So i would like to scan that PSTG schema / all the tables(columns) in that schema for this particular value. 


In the past i have used a query like below , but this just gives me Column names in a particular schema.(Doesnt look at the values in the columns).

-----------------

SELECT  TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY, T1.OWNER OWNER,

    T1.TABLE_NAME "TABLE NAME", T1.COLUMN_NAME "COLUMN NAME",

    DECODE(NULLABLE, 'N', 'NOT NULL') "NULL", DATA_TYPE||'('||DATA_LENGTH||')' "DATA TYPE",

    LAST_ANALYZED, USER_STATS

FROM  SYS.ALL_TAB_COLUMNS T1

WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'

and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'

ORDER BY T1.TABLE_NAME;

------------------

Any thoughts please ? Thanks a lot!

My oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Best Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 199 Gold Badge
    Answer ✓

    2 steps

    Generate your select statements

    SELECT 'SELECT ''' || T1.OWNER || '.' || T1.TABLE_NAME || '.' || T1.COLUMN_NAME || ':'', ' || T1.COLUMN_NAME || ' FROM ' || T1.OWNER || '.' || T1.TABLE_NAME || ' WHERE ' || T1.COLUMN_NAME || ' = ''' || '%YOUR_COLUMN_VALUE%' || ''';'

    FROM SYS.ALL_TAB_COLUMNS T1

    WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'

    and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'

    ORDER BY T1.OWNER, T1.COLUMN_NAME ;

    and then execute that statements.

    If your column is a number or a date you must adjust the select statement to get the correct one.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    Answer ✓

    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.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited Jul 15, 2021 6:42AM Answer ✓

    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; 
    /
    


Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 199 Gold Badge
    Answer ✓

    2 steps

    Generate your select statements

    SELECT 'SELECT ''' || T1.OWNER || '.' || T1.TABLE_NAME || '.' || T1.COLUMN_NAME || ':'', ' || T1.COLUMN_NAME || ' FROM ' || T1.OWNER || '.' || T1.TABLE_NAME || ' WHERE ' || T1.COLUMN_NAME || ' = ''' || '%YOUR_COLUMN_VALUE%' || ''';'

    FROM SYS.ALL_TAB_COLUMNS T1

    WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'

    and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'

    ORDER BY T1.OWNER, T1.COLUMN_NAME ;

    and then execute that statements.

    If your column is a number or a date you must adjust the select statement to get the correct one.

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    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.

    verde1030
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    Answer ✓

    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.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited Jul 15, 2021 6:42AM Answer ✓

    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; 
    /
    


developers