Buscar código en vistas de Oracle con PL/SQL

Thursday, February 23, 2012 13:56
Posted in category Informática, Oracle
DECLARE
 
     lncount  NUMBER;
     lvsearch VARCHAR2(100) := lower('cadena_buscar');
     CURSOR cur_views IS
               SELECT * FROM all_views t where view_name like 'XX%';
     lrow_views cur_views%ROWTYPE;
     v_line     VARCHAR2(30000);
BEGIN
 
    lncount := 0;
    OPEN cur_views;
 
    LOOP
 
      BEGIN
 
         FETCH cur_views
              INTO lrow_views;
         EXIT WHEN cur_views%NOTFOUND;
         --Convert LONG to VARCHAR2
         v_line := substr(lrow_views.text, 1, 30000);
         IF v_line LIKE '%' || lvsearch || '%'
         THEN
              dbms_output.put('Owner:' || lrow_views.owner);
              dbms_output.put_line(' ViewName:' || lrow_views.view_name);
           --   dbms_output.put(' Text:' || v_line);
           --   dbms_output.put_line('');
              lncount := lncount + 1;
         END IF;
 
       EXCEPTION
         WHEN OTHERS THEN dbms_output.put_line('ERROR ViewName:' || lrow_views.view_name);
       END;
    END LOOP;
 
    dbms_output.put_line('');
    dbms_output.put_line(lncount ||    ' record(s) was found in ALL_VIEWS table.');
 
    CLOSE cur_views;
 
 
END;
You can leave a response, or trackback from your own site.

Leave a Reply