Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Find Columns that have a particular Value

verde1030Jul 14 2021

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
This post has been answered by Hub Tijhuis on Jul 14 2021
Jump to Answer

Comments

Hub Tijhuis
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.

Marked as Answer by verde1030 · Aug 26 2021
Paulzip

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.

1 person found this helpful
Billy Verreynne

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

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

Post Details

Added on Jul 14 2021
4 comments
21,605 views