title: Oracle: Search an entire DB for a string: Error ORA-00911
body: <p>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.</p>
  
  <p>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:</p>
  
  <p><strong>ORA-00911: invalid character</strong>
  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.</p>
  
  <p>Does anyone know why this may be?  Please see my code below.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>EDIT</strong>: 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:</p>
  
  <pre><code>BEGIN
     FOR c IN (SELECT ControlValue FROM ABC.ControlValues) LOOP
         ABC.FIND_STRING(c.ControlValue);
     END LOOP;
  END;
  </code></pre>