I was trying to create a PL/SQL program to search for a value in entire database. Following is the code:
DECLARE
custom_query VARCHAR2(100);
user_input VARCHAR2(100);
i NUMBER (6);
TYPE temp_record
IS
varray(1000) OF VARCHAR2(100);
CURSOR t_rec IS
SELECT a.name AS t_name,
b.name AS c_name
FROM sys.TABLES a,
sys.COLUMNS b
WHERE a.object_id = b.object_id;
table_rec t_name%ROWTYPE;
BEGIN
IF NOT t_rec%isopen THEN
OPEN t_rec;
END IF;
FETCH t_rec
INTO table_rec;
WHILE t_rec%FOUND THEN
LOOP
custom_query := 'select'
|| table_rec.c_name
|| 'into temp_record from'
||table_rec.t_name ;
dbms_output.Put_line(custom_query);
EXECUTE IMMEDIATE custom_query;
FOR i IN 1 .. temp_record.count
LOOP
IF (Temp_record(i) = user_input) THEN
dbms_output.Put_line ('The value you are looking for is in '
|| table_rec.c_name
|| 'column of table'
|| table_rec.t_name);
END IF;
END LOOP;
END LOOP;
END;
Can anyone let me know what's wrong with this code?
