I have created the PL/SQL stored procedure below to search an entire Oracle11g database for a string (srchstr) and return the table and column where that string was found to a table called VALUESEARCHRESULTS.
The procedure has ran successfully in Oracle XE via SQL Developer as a user. However, when try to run it as user SYS in Oracle11g for schema ABC, I receive the following error:
ORA-00911: invalid character Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes (q"#...#") cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
Does anyone know why this may be? Please see my code below.
CREATE OR REPLACE PROCEDURE ABC.FIND_STRING(p_str IN VARCHAR2) authid current_user is
l_query clob;
srchstr varchar2(30) := '';
r_cname varchar2(30) := '';
l_case clob;
l_runquery boolean;
l_tname varchar2(30);
l_cname varchar2(30);
begin
dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
for x in (select * from user_tables)
loop
l_query := 'select ''' || x.table_name || ''', $$
from ' || x.table_name || '
where rownum = 1 and ( 1=0 ';
l_case := 'case ';
l_runquery := FALSE;
for y in ( select *
from user_tab_columns
where table_name = x.table_name
and (data_type in('CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
)
loop
l_runquery := TRUE;
l_query := l_query || ' or upper(' || y.column_name ||
') like userenv(''client_info'') ';
l_case := l_case || ' when upper(' || y.column_name ||
') like userenv(''client_info'') then ''' ||
y.column_name || '''';
end loop;
if ( l_runquery )
then
l_case := l_case || ' else NULL end';
l_query := replace( l_query, '$$', l_case ) || ')';
begin
execute immediate l_query into l_tname, l_cname;
r_cname := l_cname;
dbms_application_info.read_client_info(srchstr);
insert into ABC.ValueSearchResults (resulttable, resultcolumn, searchstring) values (x.table_name, r_cname, srchstr);
dbms_output.put_line
( srchstr || ' found in ' || l_tname || '.' || l_cname );
exception
when no_data_found then
dbms_output.put_line
( srchstr || ' has no hits in ' || x.table_name );
end;
end if;
end loop;
end;
EDIT: The stored procedure above compiles without error. The code below executes the stored procedure by passing values from a table into the stored procedure. The error shows when the code below is ran:
BEGIN
FOR c IN (SELECT ControlValue FROM ABC.ControlValues) LOOP
ABC.FIND_STRING(c.ControlValue);
END LOOP;
END;
I have found a resolution for the issue I initially raised.
Cause of error: Not specifying a schema; only grabbing user-specific tables.
While the stored procedure would execute when deployed as user ABC, the error generated when running the stored procedure as a user other ABC. It appeared the same table name existed in multiple schemas. Thus, adding an OWNER variable specified the schema associated with the table name and eliminated the error.
Additionally, the procedure was originally searching for USER_TABLES. This limited the results to only the tables of the current schema. By replacing USER_TABLES with DBA_TABLES, the stored procedure's search spanned through all tables of the database.
See below for the corrected code:
CREATE OR REPLACE
PROCEDURE FIND_STRING(
p_str IN VARCHAR2) authid current_user
IS
l_query CLOB;
srchstr VARCHAR2(100) := '';
r_cname VARCHAR2(100) := '';
l_case CLOB;
l_runquery BOOLEAN;
l_tname VARCHAR2(100);
l_cname VARCHAR2(100);
BEGIN
dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
FOR x IN
(SELECT *
FROM dba_tables
WHERE table_name <> 'CONTROLVALUES'
AND table_name <> 'VALUESEARCHRESULTS'
AND tablespace_name <> 'SYSTEM'
AND tablespace_name <> 'SYSAUX'
AND tablespace_name <> 'TEMP'
AND tablespace_name <> 'UNDOTBS1'
)
LOOP
l_query := 'select ''' || x.owner || '.' || x.table_name || ''', $$
from ' || x.owner || '.' || x.table_name || '
where rownum = 1 and ( 1=0 ';
l_case := 'case ';
l_runquery := FALSE;
FOR y IN
(SELECT *
FROM dba_tab_columns
WHERE table_name = x.table_name
AND owner = x.owner
AND (data_type IN ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
OR data_type LIKE 'INTERVAL%'
OR data_type LIKE 'TIMESTAMP%' )
)
LOOP
l_runquery := TRUE;
l_query := l_query || ' or upper(' || y.column_name || ') like userenv (''client_info'') ';
l_case := l_case || ' when upper(' || y.column_name || ') like userenv (''client_info'') then ''' || y.column_name || '''';
END LOOP;
IF ( l_runquery ) THEN
l_case := l_case || ' else NULL end';
l_query := REPLACE( l_query, '$$', l_case ) || ')';
BEGIN
EXECUTE immediate l_query INTO l_tname, l_cname;
r_cname := l_cname;
dbms_application_info.read_client_info(srchstr);
INSERT
INTO VALUESEARCHRESULTS
(
resulttable,
resultcolumn,
searchstring
)
VALUES
(
x.table_name,
r_cname,
srchstr
);
dbms_output.put_line ( srchstr || ' found in ' || l_tname || '.' || l_cname );
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line ( srchstr || ' has no hits in ' || x.owner || '.' || x.table_name );
END;
END IF;
END LOOP;
END;