Buscar texto en columnas de tablas Oracle PL/SQL

--Mediante Variable

--DROP PROCEDURE BUSCADOR;

CREATE OR REPLACE PROCEDURE BUSCADOR  IS

LV_QUERY VARCHAR2(1000);

TEXTO VARCHAR(200);

ENCONTRADOS NUMBER;

CURSOR BUSCATXT IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE

FROM ALL_TAB_COLUMNS 

WHERE OWNER='USUARIO' 

AND DATA_TYPE IN ('CHAR','VARCHAR2');

BEGIN

TEXTO:='TEXTO_A_BUSCAR';

FOR K IN BUSCATXT LOOP

LV_QUERY := 'SELECT COUNT(*) FROM'||K.OWNER||'.'||K.TABLE_NAME||' WHERE '||K.COLUMN_NAME||' LIKE ''%'||TEXTO||'%''';

EXECUTE IMMEDIATE LV_QUERY INTO ENCONTRADOS;

IF ENCONTRADOS>0 THEN

DBMS_OUTPUT.PUT_LINE(LV_QUERY);

END IF;

END LOOP;

END BUSCADOR;

--Mediante Parámetro

--DROP PROCEDURE BUSCADOR2;

CREATE OR REPLACE PROCEDURE BUSCADOR2 (LV_TXT VARCHAR2) IS

LV_QUERY VARCHAR2(1000);

ENCONTRADOS NUMBER;

CURSOR BUSCATXT IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE 

FROM ALL_TAB_COLUMNS 

WHERE OWNER='USUARIO2' 

AND DATA_TYPE IN ('CHAR','VARCHAR2');

BEGIN

--TEXTO:='TEXTO_A_BUSCAR';

FOR K IN BUSCATXT LOOP

LV_QUERY := 'SELECT COUNT(*) FROM '||K.OWNER||'.'||K.TABLE_NAME||' WHERE '||K.COLUMN_NAME||' LIKE ''%'||LV_TXT||'%''';

EXECUTE IMMEDIATE LV_QUERY INTO ENCONTRADOS;

IF ENCONTRADOS>0 THEN

DBMS_OUTPUT.PUT_LINE(LV_QUERY);

END IF;

END LOOP;

END BUSCADOR2;

--Mediante Query
SELECT owner,name,TYPE,line,text
FROM All_Source
WHERE LOWER (text) LIKE LOWER('%TEXTO_A_BUSCAR%')
AND OWNER='ESQUEMA';