CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
Delin Mathew
Delin Mathew

Reputation: 279

Oracle search text of views

I have over 1000 views and I want to run a search which will display the names of the views containing the string abc in its SQL. How do I search all stored procedures/SQL including that of my views? When I run the command: 

SELECT *
FROM   all_source
WHERE  text LIKE '%abc%'

it returns me source code in which the string abc is present. But this does not include views.

Upvotes: 7

Views: 36653

Answers (3)

Piyush Kulkarni
Piyush Kulkarni

Reputation: 1

This PL/SQL script iterates through all views in a specified schema and searches for a given text or keyword within the view definitions. It uses DBMS_METADATA.GET_DDL to extract the DDL of each view and DBMS_LOB.INSTR to perform a case-insensitive search. This approach helps overcome the issue of handling LONG datatypes by leveraging CLOB output, which allows for more straightforward text processing. Replace &owner with the schema name and &inputtext with the keyword you want to search for.

-- To search for text in view definition
 
DECLARE
    v_view_name VARCHAR2(200); 
    v_ddl       CLOB;          
BEGIN
    -- Loop through all views in a given schema
    FOR rec IN (SELECT view_name FROM all_views WHERE owner = '&owner' ORDER BY view_name) LOOP
        v_view_name := rec.view_name;
        
        v_ddl := DBMS_METADATA.GET_DDL('VIEW', v_view_name);
        
        -- INPUT THE SEARCH STRING/KEYWORD BELOW
        IF DBMS_LOB.INSTR(UPPER(v_ddl), UPPER('&inputtext')) > 0 THEN
 
          DBMS_OUTPUT.PUT_LINE('View Name: ' || v_view_name);
          --DBMS_OUTPUT.PUT_LINE('DDL: ' || TO_CHAR(v_ddl));
          --DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
        
        END IF;
    END LOOP;
END;
/

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

This gets easier in 12c, where you can use

select *
from   all_views v
where  lower(v.text_vc) like '%abc%';

This assumes the text string you are looking for is in the first 4000 characters. You might also have the report include any view where text_length is > 4000 along with a warning.

In earlier versions (or to avoid the 4000 character restriction), you might try a PL/SQL loop like this:

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 '%abc%' then
            dbms_output.put_line(rpad(r.owner,31) || r.view_name);
        end if;
    end loop;
end;

PL/SQL implicitly converts the SQL LONG value to a 32K PL/SQL string.

(In my tests in 12.2.0.1.0, this failed with ORA-06502: PL/SQL: numeric or value error at the select statement when my cursor included SYS.DBA_SCHEDULER_RUNNING_JOBS or SYS."_user_stat", even though other views with longer text were handled successfully, and I'm not sure why. There may be some issue with this I'm not seeing.)

Upvotes: 15

Littlefoot
Littlefoot

Reputation: 143103

Select ALL_VIEWS instead (column name is TEXT as well).

Though, you'd rather use one of UPPER or LOWER functions as

select *
from all_views
where lower(text) like '%abc%'

because once you might have put it as 'abc', another time 'ABC', etc.

[EDIT, because of ORA-00932]

Oh, yes - in ALL_VIEWS, the TEXT column is of a LONG datatype (while in ALL_SOURCE it is a VARCHAR2, so LIKE won't work on ALL_VIEWS.

One option is to create a "temporary" table which contains all views & apply the TO_LOB function on the TEXT column, and then select from it:

SQL> create or replace view my_emp as select empno, ename xxx_ename, job from emp;

View created.

SQL> create table my_all_views as
  2  select owner, view_name, to_lob(text) text
  3  from all_views;

Table created.

SQL> select owner, view_name
  2  from my_all_views
  3  where lower(text) like '%xxx%';

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            USER_SCHEDULER_JOB_DESTS
SYS                            ALL_SCHEDULER_JOB_DESTS
SYS                            USER_XML_SCHEMAS
SYS                            ALL_XML_SCHEMAS
SYS                            ALL_XML_SCHEMAS2
SCOTT                          MY_EMP

6 rows selected.

SQL>

Its drawback is that it doesn't scale; if you create a new view, you'll have to recreate the table.

Or, you could create your own function which would do that search. For example:

SQL> create or replace function f_search_view (par_string in varchar2)
  2    return sys.odcivarchar2list
  3    pipelined
  4  is
  5  begin
  6    for cur_r in (select view_name, text from all_views
  7                  where text_length < 32767)
  8    loop
  9      if instr(cur_r.text, par_string) > 0 then
 10         pipe row(cur_r.view_name);
 11      end if;
 12    end loop;
 13
 14    return;
 15  end;
 16  /

Function created.

SQL> select * from table(f_search_view('xxx'));

COLUMN_VALUE
--------------------------------------------------------------------------------
USER_XML_SCHEMAS
ALL_XML_SCHEMAS
ALL_XML_SCHEMAS2
MY_EMP

SQL>

Upvotes: 5

Related Questions