I'm trying to write a PL/SQL script that searches the entire database for a string and report the tables and columns it finds it in. It looks like this:
DECLARE
ncount NUMBER;
vwhere VARCHAR2(1000) := '';
vsearchstr VARCHAR2(1000) := 'search string here';
vresult VARCHAR2(10000) := 'result: ';
vtab VARCHAR2(1000) := '';
vcol VARCHAR2(1000) := '';
BEGIN
FOR k IN (SELECT a.table_name, a.column_name FROM all_tab_columns a WHERE a.data_type LIKE '%VARCHAR%')
LOOP
vtab := k.table_name;
vcol := k.column_name;
vwhere := ' where ' || vcolumnname || ' = :vsearchstr ';
EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr;
IF (ncount > 0)
THEN
vresult := CONCAT(vresult, vcol || ' ' || vtab || ', ');
END IF;
END LOOP;
IF (LENGTH(vresult) > 1)
THEN
dbms_output.put_line(vresult);
ELSE
dbms_output.put_line('not found');
END IF;
END;
When I run it, I get the following error:
It is essentially saying it doesn't recognize the table vtab
in the line EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr
.
So in order to see which table it's complaining about, I added the following exception block to the end of the script:
EXCEPTION
WHEN OTHERS
THEN
BEGIN
dbms_output.put_line('exception: ' || vtab);
END;
It tells me the table name is IND$
.
I'm not sure what this table (or view) is and it doesn't look relevant anyway.
So my question is two fold: 1) If it is fetching IND$ in the FOR loop from k.table_name (which in turn is from all_tab_columns), why does it say it doesn't exist in the select query? 2) I'm not sure what IND$ is but I'm pretty sure I don't need to search it; So is there a way to limit my search to only relevant tables (not views)? By 'relevant', I mean tables that we created to store data for our application (as opposed to system tables or user tables, etc.).
Thanks very much.
CodePudding user response:
all_tab_columns
lists columns in all tables that your current schema has access to. In general, if you're going to use ALL_... views to build dynamic SQL, you should incorporate the OWNER
column as well since it may return rows for tables in other schemas.
If you really only want to consider tables in your current schema, use user_tab_columns
instead.
If you want to get more, or less, particular than that about which tables are 'relevant', then you'll probably need to hardcode specific rules into your query.
FYI: IND$
is part of the Oracle data dictionary and is in the SYS schema. (Although, it's always possible someone has created a table with that name in some other schema.) It's unusual that your application schema would have direct access to this.
CodePudding user response:
If you know how to skip tables you don't want, do it - you might filter by OWNER
, maybe table name, etc.
If you don't want to bother, include inner begin-exception-end
block into the loop so that it skips errors (either silently, or display errors, or store them into some table). Here's one option:
DECLARE
ncount NUMBER;
vwhere VARCHAR2(1000) := '';
vsearchstr VARCHAR2(1000) := 'search string here';
vresult VARCHAR2(10000) := 'result: ';
vtab VARCHAR2(1000) := '';
vcol VARCHAR2(1000) := '';
BEGIN
FOR k IN (SELECT a.table_name, a.column_name FROM all_tab_columns a WHERE a.data_type LIKE '%VARCHAR%')
LOOP
BEGIN
vtab := k.table_name;
vcol := k.column_name;
vwhere := ' where ' || vcolumnname || ' = :vsearchstr ';
EXECUTE IMMEDIATE 'select count(1) from ' || vtab || vwhere INTO ncount USING vsearctstr;
IF (ncount > 0)
THEN
vresult := CONCAT(vresult, vcol || ' ' || vtab || ', ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(vtab ||': '|| sqlerrm);
END;
END LOOP;
IF (LENGTH(vresult) > 1)
THEN
dbms_output.put_line(vresult);
ELSE
dbms_output.put_line('not found');
END IF;
END;
CodePudding user response:
You need to:
- Also get the
owner
fromall_tab_columns
- Use quoted identifiers around the
owner
,table_name
andcolumn_name
so that the query does not fail for identifiers in different cases or with special characters. - Handle errors (i.e. when you do not have the permissions to
SELECT
from a table).
To make it faster, you can:
- Filter the columns to only those that are long enough to contain the search string.
You can do that using:
DECLARE
ncount NUMBER;
vsearchstr VARCHAR2(1000) := 'search string here';
vresult VARCHAR2(10000);
BEGIN
FOR k IN (SELECT owner,
table_name,
column_name
FROM all_tab_columns
WHERE data_type LIKE '%VARCHAR%'
AND data_length >= LENGTH(vsearchstr))
LOOP
BEGIN
EXECUTE IMMEDIATE
'select count(1)
from "' || k.owner || '"."' || k.table_name || '"
where "' || k.column_name || '" = :1'
INTO ncount
USING vsearchstr;
IF (ncount > 0)
THEN
vresult := vresult || ','
|| k.owner || '.' || k.table_name || '.' || k.column_name;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
IF (LENGTH(vresult) > 1)
THEN
dbms_output.put_line(vresult);
ELSE
dbms_output.put_line('not found');
END IF;
END;
/
db<>fiddle here