Search all views & all fields

[kokiri]

I'm not sure what is available to accomplish this:

1. I need to be able to search if any of view has specific field name such as CustID.

2. If there is CustID, I would like to search all columns that has certain strings like '%xxx%'.

3. at last, I need to be able to identify list of view(s) name that meets above criteria.

Thanks in advance.

 

[LKBrwnDBA]

Maybe this will help:

-- Using SQL*Plus:
set pages 0 term off trims on feed off
spo /tmp/find_custid.sql
prompt set pages 55 term on feed on
select 'select * from '||v.view_name||' where custid=''%xxx%'';'
  from user_views, user_tab_columns
 where table_name = view_name
   and column_name = 'CUSTID';
spo off
@/tmp/find_custid.sql

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Kokiri,

Here is a script that lists all database Table Names (qualified by Owners) that have a specific Column name and contains a specific string in that Column:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave"at"dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM This script displays the Owner, Object, Column name, and content
REM of rows which reside in a table or view having a column matching
REM input prompt "Col_name" and content matching input prompt
REM "Content_Frag".
REM
REM In the code, below, 'TABLE' is commented out of the object_type
REM comparisons ["...o.object_type in (/*'TABLE',*/'VIEW')..."].
REM If you wish to have the script search TABLEs, as well, then
REM remove the comment symbols so that the code reads:
REM "...o.object_type in ('TABLE','VIEW')..."
REM **************************************************************
accept Col_name prompt "Enter the column name for which you are looking: "
accept Content_Frag prompt "Enter (a fragment of) the text for which you are looking: "
set serveroutput on format wrap
declare
    results_cnt number := 0;
    sql_stm     varchar2(2000);
    found_cnt   number := 0;
begin
    dbms_output.enable(1000000);
    dbms_output.put_line(' ');
    for t in (select c.owner, c.table_name, c.column_name
                from dba_tab_columns c, dba_objects o
               where column_name = upper('&Col_name')
                 and c.owner = o.owner
                 and c.table_name = o.object_name
                 and o.object_type in (/*'TABLE',*/'VIEW')) loop
        sql_stm := 'select count(*) from '||t.owner||'.'||t.table_name
            ||' where upper(&col_name) like upper(''%&Content_Frag'||'%'')'
            ;
        execute immediate sql_stm into results_cnt;
        if results_cnt > 0 then
            found_cnt := found_cnt+1;
            dbms_output.put_line(t.owner||'.'||t.table_name||'.'||t.column_name
            ||' contains "&Content_Frag".');
        end if;
    end loop;
    dbms_output.put_line(' ');
    if found_cnt = 1 then
        dbms_output.put_line('1 object contains "&Content_Frag" '||
            'in its "&Col_name" column.');
    else
        dbms_output.put_line(found_cnt||' objects contain "&Content_Frag" '||
            'in their "&Col_name" columns.');
    end if;
end;
/

You must ave the above code to a script to a file (I named my script, "ContentFinder.sql") since the script contains a PROMPT command. (I.e., you cannot just copy and paste the above code to a SQL*Plus prompt.)

If you read the code, it gives you the option and running this code against either "Tables and Views" or "Just Views" (as you specified).

Here is an invocation with "Tables and Views" as the horizon for the string search:

SQL> @contentfinder
Enter the column name for which you are looking: last_name
Enter (a fragment of) the text for which you are looking: velas

DHUNT.EMP2BIG.LAST_NAME contains "velas".
TESTNEW.S_EMP.LAST_NAME contains "velas".
TEST.EMP_VIEW.LAST_NAME contains "velas".
TEST.S_EMP.LAST_NAME contains "velas".
TEST.TESTC.LAST_NAME contains "velas".

5 objects contain "velas" in their "last_name" columns.

PL/SQL procedure successfully completed.

Here is an invocation with "Just Views" as the search horizon:

SQL> @contentfinder
Enter the column name for which you are looking: last_name
Enter (a fragment of) the text for which you are looking: velas

TEST.EMP_VIEW.LAST_NAME contains "velas".

1 object contains "velas" in its "last_name" column.

PL/SQL procedure successfully completed.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[kokiri]

Thank you LKBrwnDBA & Mufasa for your scripts.

Maybe I need to be more specific.
I need to be able to catch any views that I have granted for with CustID=1234. From that, I need to be able to search any columns that contains '%xxx%'.

Mufasa - I ran your script and narrow it down to certain schema and I got an error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at line 18

Apparently, I don't have permission to access some of views.

I'm not sure which table contains permission information....

LKBrwnDBA - using your script, I can only access to my schema.

Thank you guys for your prompt response.

Kokiri

 

[SantaMufasa]

Kokiri,

I have made the following enhancements to the script (which I renamed, "ContentFinderInUser.sql") to address your additional needs:

1) Caused the scan to occur on just the current schema (i.e., script accesses "USER_..." instead of "DBA_..." views).
2) Added a user-defined search-condition capability (example: "CustID=1234")
3) Implemented significant error checking to reduce likelihood of run-time errors.

Here is the revised code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave"at"dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM This script displays (from the current Oracle user/schema) any
REM data object (e.g. table/view), Column name, and content
REM of rows which reside in a table or view having a column matching
REM input prompt "Col_name" and content matching input prompt
REM "Content_Frag".
REM 
REM The code also allows user-defined WHERE conditions.
REM
REM In the code, below, 'TABLE' is commented out of the object_type
REM comparisons ["...o.object_type in (/*'TABLE',*/'VIEW')..."].
REM If you wish to have the script search TABLEs, as well, then
REM remove the comment symbols so that the code reads:
REM "...o.object_type in ('TABLE','VIEW')..."
REM **************************************************************
set verify off
set echo off
accept Col_name prompt "Enter the column name for which you are looking: "
accept Content_Frag prompt "Enter (a fragment of) the text for which you are looking: "
prompt
prompt You may enter here an additional condition to search for.
prompt For example: "CustID=1234". If your condition requires single quotes
prompt (to surround a literal, for example), then use two single quotes
prompt as in this example:
prompt
prompt ---> Last_name like ''%Smith%'' <----
prompt
prompt Note: you must ensure that your condition is syntactically correct,
prompt       otherwise you will receive a run-time error.
prompt
prompt If you do not need an additional condition, then press [Enter]
prompt
accept WhereCondition prompt "Enter condition ---> "
prompt
prompt ====================================================================
set serveroutput on format wrap
declare
    results_cnt number := 0;
    sql_stm            varchar2(2000);
    found_cnt          number := 0;
    confirmation       varchar2(2000);
    invalid_identifier exception;
    pragma             exception_init(invalid_identifier,-904);
    errors             boolean;
    conjunction        varchar2(10);
    suffix             varchar2(100);
    procedure suffix_creation is
    Begin
    suffix := null;
    if '&WhereCondition' is not null then
        if errors then
            conjunction := '..but "';
            suffix      := ' encountered at least 1 run-time error.';
        else
            conjunction := '..plus "';
        end if;
        confirmation := confirmation||conjunction
            ||replace('&WhereCondition','''''','''')||'"'
            ||suffix;
    end if;
    end;
begin
    dbms_output.enable(1000000);
    dbms_output.put_line(' ');
    for t in (select c.table_name, c.column_name
                from user_tab_columns c, user_objects o
               where column_name = upper('&Col_name')
                 and c.table_name = o.object_name
                 and o.object_type in (/*'TABLE',*/'VIEW')) loop
        sql_stm := 'select count(*) from '||t.table_name
            ||' where upper(&col_name) like upper(''%&Content_Frag'||'%'')';
        if '&WhereCondition' is not null then
            sql_stm := sql_stm||' and &WhereCondition';
        end if;
	results_cnt := 0;
        errors := false;
        begin
            execute immediate sql_stm into results_cnt;
        exception
            when invalid_identifier then
                dbms_output.put_line
                    ('Error: --> '||t.table_name||'...Invalid identifier in '||
                      replace('"&WhereCondition".','''''',''''));
                errors := true;
            when others then
                dbms_output.put_line
                    ('Error: --> '||t.table_name||'...syntax error in '||
                      replace('"&WhereCondition".','''''',''''));
                errors := true;
	end;
        confirmation := null;
        if results_cnt > 0 then
            found_cnt := found_cnt+1;
            suffix_creation;
            dbms_output.put_line('Found: --> '||t.table_name||'.'||t.column_name
            ||' contains "&Content_Frag".'||confirmation);
        end if;
    end loop;
    dbms_output.put_line(' ');
    if found_cnt = 1 then
        confirmation := '1 object contains "&Content_Frag" '||
            'in its "&Col_name" column.';
    else
        confirmation := found_cnt||' objects contain "&Content_Frag" '||
            'in their "&Col_name" column.';
    end if;
    suffix_creation;
    dbms_output.put_line(confirmation);
end;
/
prompt ====================================================================

Here is a sample invocation with the new code:

SQL> @contentfinderInUser
Enter the column name for which you are looking: last_name
Enter (a fragment of) the text for which you are looking: velas

You may enter here an additional condition to search for.
For example: "CustID=1234". If your condition requires single quotes
(to surround a literal, for example), then use two single quotes
as in this example:

---> Last_name like ''%Smith%'' <--- prompt
Note: you must ensure that your condition is syntactically correct,
otherwise you will receive a run-time error.

If you do not need an additional condition, then press [Enter]

Enter condition ---> first_name like ''%men%''

====================================================================

Error: --> EMP_VIEW...Invalid identifier in "first_name like '%men%'".
Found: --> S_EMP.LAST_NAME contains "velas"...plus "first_name like '%men%'"
Found: --> TESTC.LAST_NAME contains "velas"...plus "first_name like '%men%'"

2 objects contain "velas" in their "last_name" column...plus "first_name like '%men%'"

PL/SQL procedure successfully completed.

====================================================================
***************************************************************************************

Let us know if this addresses your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[kokiri]

Hi Mufasa,

Thank you for your reply and enhanced scripts. I modified using ContentFinder.sql referenced tables since I need to access different schema

for t in
(select c.owner, c.table_name, c.column_name
from dba_tab_columns c, dba_objects o
where column_name = upper('&Col_name')
and c.owner = o.owner
/* schema that I need to query */
and c.owner='xxx'
and c.table_name = o.object_name

I'm getting bunch of errors with syntax like below
Error: --> WOUND_OSTOMYPHOTO...syntax error in "data like '%Allergi%'".

When I checked this view with CustID=1234, I didn't have any hit.

I would like to see only if view has CustID=1234 and a field name contained=''%Smith%''

Thanks.

Kokiri