Search string to match Data inside of all existing columns and rows from Views in Oracle SQL

Question

The code below is looking for a string to match a column name. However I would like to search for a string to match Data (meaning, search on each existing column and row from all views - not column names). I want the results to show me all Views Names that contain that string on their Data. Hope this makes sense.

begin
    dbms_output.put_line('Owner                          View name');
    dbms_output.put_line('------------------------------ -------------------------------');

    for r in (
        select v.owner, v.view_name, v.text
        from   all_views v
        where  v.owner <> 'SYS'
    )
    loop
        if lower(r.text) like '%my_String%' then
            dbms_output.put_line(rpad(r.owner,31) || r.view_name);
        end if;
    end loop;
end;

Answer 1

I suggest you at least review some of the Oracle documentation. Your statement "Oracle SQL... i am using Oracle SQL Developer" indicates a lack of understanding. So lets clear some of that up. You are actually using 3 separate software tools:

1. Oracle SQL - all access to data being stored in your Oracle database.
2. Oracle Pl/SQL - the programming language, which tightly integrates with but is still separate from SQL. This is the language in which your script is written.
3. Oracle SQL Developer - an IDE within which you develop, run, etc run your Pl/SQL scripts and/or SQL statements.

Now as for your script. The syntax is fine and it will run, successfully retrieving all non sys owned views and printing the names of those containing the specified search string. However, as it currently stands it'll never find a match. Your match statement attempts to match a lower case string to a string that contains the character 'S' (upper case). There will never be a match. Also, keep in mind that within LIKE the underscore (_) is a single character wild card. You may need to escape it as "like '%my_ ...'". With these in mind we come to:

REVISED

The requirement to actually find the string in view columns completely changes things from your original query. Although the title does suggest that was actually the initial desire. What you want to accomplish is much more complex and cannot be done in SQL alone; it requires PL/SQL or an external language code. The reason is that you need run select but you don't know against what nor what columns nor even how many columns area needed. For that you need to identify the view, isolate the viable columns and generate the appropriate SQL dynamically then actually execute that sql and check the results.

Two approaches come to mind: Parse the view to extract the columns (something I would even consider doing in PL/SQL) or join ALL_VIEWS with ALL_TAB_COLUMNS (All table columns). That we'll do. The majority of the code will be constructing the SQL and the necessary error handling dynamic SQL essentially forces on you. I've created this a a procedure with 2 parameters: the target string, and the schema.

create or replace procedure find_string_in_view(
                            schema_name_in   varchar2
                          , target_string_in varchar2
                          )
is 
    --- set up components for eventual execution 
    k_new_line constant varchar2(2) := chr(10);
    k_base_statement constant varchar2(50) :=
         q'!select count(*) from <view_name> where 1=1 and !' || k_new_line;
    k_where_statement constant varchar2(50) :=
         q'! <column_name> like '%<target>%' !' || k_new_line;
    k_limit_statement constant varchar2(20) :=  
        ' ) and rownum < 2'; 
    k_max_allowed_errors constant integer := 3;

    --- cursor for views and column names
    cursor c_view_columns is
          (select av.owner,av.view_name , atc.column_name
                , lead(atc.column_name) over (partition by av.view_name order by atc.column_id) next_col
                , lag(atc.column_name)  over (partition by av.view_name order by atc.column_id) prev_col      
             from all_views       av
             join all_tab_columns atc
               on (    atc.owner = av.owner 
                   and atc.table_name = av.view_name
                  )
              where av.owner = upper(schema_name_in) 
                and atc.data_type in 
                    ('CHAR', 'NCHAR', 'VARCHAR2','NVARCHAR2','VARCHAR','NVARCHAR')           
          ) ; 

    --- local variables
    m_current_view    varchar2(61); 
    m_sql_errors      integer := 0;
    m_where_connector varchar(2);
    m_sql_statement   varchar2(4000); 

    -- local helper function    
    function view_has_string
      return boolean
    is 
        l_item_count integer := 0;
    begin 
        execute immediate m_sql_statement into l_item_count;
        return (l_item_count > 0); 
    exception 
    when others then
         dbms_output.put_line(rpad('-',61,'-') || k_new_line); 
         dbms_output.put_line('Error processing:: ' || m_current_view);
         dbms_output.put_line('Statement::' || k_new_line || m_sql_statement);
         dbms_output.put_line(sqlerrm);
         m_sql_errors := m_sql_errors + 1;
         if m_sql_errors >= k_max_allowed_errors
         then 
             raise_application_error(-20199,'Maximun errors allowed reach. Terminating');
         end if;
         return false;
    end view_has_string;

begin  -- MAIN --
    dbms_output.put_line('Owner                          View name');
    dbms_output.put_line('------------------------------ -------------------------------');

    for rec in c_view_columns
    loop 
        if rec.prev_col is null 
        then
            m_current_view := rec.owner || '.' || rec.view_name;
            m_sql_statement := replace(k_base_statement,'<view_name>',m_current_view);
            m_where_connector := ' (';
        end if;

        m_sql_statement := m_sql_statement || m_where_connector 
                        || replace(k_where_statement,'<column_name>',rec.column_name);
        m_where_connector := 'or' ;

        if rec.next_col is null
        then 
            m_sql_statement := replace(m_sql_statement,'<target>',target_string_in);
            m_sql_statement := m_sql_statement || k_limit_statement; 

            if view_has_string
            then                
                dbms_output.put_line(rpad(rec.owner,31) || rec.view_name);
            end if;
        end if;
    end loop;

end find_string_in_view;

Answer 2

select v.owner, v.view_name, v.text
    from   all_views v
    where  v.owner <> 'SYS' and lower(v.text) like '%my_String%'

one SQL do this?