Generating Dynamic SQL in Oracle

Question

I have a problem with my plsql code and try almost everything. Now i'm loosing my mind and power to solve my problem :)

The case is that I want to search all tables schema for specific string assigned to variable v_ss and print it to DBMS_OUTPUT. I know that there are ready-made solutions for that kind of cases, but I wanted to code it by myself. Below code gives me an error that "table does not exist" in my v_stmt. I assume that this select does not recognize rec.column_name, but why?

Here is my code:

DECLARE
 v_stmt VARCHAR2(1000);
 v_ss VARCHAR2(30) := 'Argentina';

 v_own ALL_TAB_COLUMNS.OWNER%TYPE;
 v_tab_nam ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
 v_col_nam ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;

 CURSOR cur_asc IS
   SELECT t.owner, t.table_name, t.column_name
   FROM SYS.ALL_TAB_COLUMNS t
   WHERE t.OWNER LIKE 'HR'
     AND t.DATA_TYPE LIKE 'VARCHAR2';


BEGIN
  FOR rec IN cur_asc LOOP
    v_stmt := 'SELECT rec.owner, rec.table_name, rec.column_name FROM rec.table_name WHERE rec.column_name LIKE :1';
    EXECUTE IMMEDIATE v_stmt INTO v_own, v_tab_nam, v_col_nam USING v_ss;
    DBMS_OUTPUT.put_line(v_own || ':' || v_tab_nam || ':' || v_col_nam);
  END LOOP;


END;
/

Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 19
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Could You give me an explanation for my bug and how to fix it? Thanks in advance

Answer 1

The dynamic statement is executed in a context that cannot see your PL/SQL variables, so when it runs rec.table_name etc. are interpreted as SQL-level objects - which don't exist.

You have to concatenate the variable values into the dynamic statement for the from and where clause; you can do the same in the select list (though they'd need to be wrapped in escaped single-quotes as they are strings), or use bind variables there:

v_stmt := 'SELECT :owner, :table_name, :column_name FROM '
  || rec.table_name || ' WHERE ' || rec.column_name || ' LIKE :ss';
EXECUTE IMMEDIATE v_stmt INTO v_own, v_tab_nam, v_col_nam
USING rec.owner, rec.table_name, rec.column_name, v_ss;

You can't use bind variables for object identifiers, hence needing the concatenation for those parts.

Unless you run this as the HR user, in which case you could be using user_tables instead of all_tables, you also need to specify the schema in the query (As Tony and Lalit mentioned); either hard-coding HR or using the queried owner:

v_stmt := 'SELECT :owner, :table_name, :column_name FROM '
  || rec.owner || '.' || rec.table_name
  || ' WHERE ' || rec.column_name || ' LIKE :ss';

This is going to error for all the tables that don't contain exactly one matching value though - if the dynamic select gets zero rows, or more than one row. But that's a separate issue.

Answer 2

There are 2 issues here:

1. You need to concatenate the table and column names into the dynamic SQL because 'SELECT rec.owner...' is trying to select a column called owner from a table with alias rec, it does not reference your for loop record.
2. Since there could be no matching rows or many matching rows in a table, you cannot use select into, you need to use a cursor.

try this:

DECLARE
 v_stmt VARCHAR2(1000);
 v_ss VARCHAR2(30) := 'Argentina';

 v_own ALL_TAB_COLUMNS.OWNER%TYPE;
 v_tab_nam ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
 v_col_nam ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;

 CURSOR cur_asc IS
   SELECT t.owner, t.table_name, t.column_name
   FROM ALL_TAB_COLUMNS t
   WHERE t.DATA_TYPE LIKE 'VARCHAR2'
   AND ROWNUM < 10;

 c SYS_REFCURSOR;

BEGIN
  FOR rec IN cur_asc LOOP
    v_stmt := 'SELECT ''' || rec.owner || ''',''' || rec.table_name || ''', ''' || rec.column_name || ''' FROM ' || rec.table_name || ' WHERE ' || rec.column_name || ' LIKE :1';
    OPEN c FOR v_stmt USING v_ss;
    LOOP
      FETCH c INTO v_own, v_tab_nam, v_col_nam;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line(v_own || ':' || v_tab_nam || ':' || v_col_nam);
    END LOOP;
    CLOSE c;
  END LOOP;
END;
/

Answer 3

v_stmt := 'SELECT rec.owner, rec.table_name, rec.column_name FROM rec.table_name WHERE rec.column_name LIKE :1';

1. Your dynamic sql statement is malformed. If you enclose the variables between single-quotation marks then they are treated as literals and not variables any more.
2. You must prefix the schema before the table_name else you must run the script while you are connected as HR user.

v_stmt := 'SELECT '''||rec.owner||''', '''|| rec.table_name||''', '
                     ||rec.column_name||' FROM '||rec.owner||'.'
                     ||rec.table_name||' WHERE '||rec.column_name||' LIKE :1';

Always remember, whenever working with dynamic statements, always use DBMS_OUTPUT to first verify the actual SQL being generated. This is the best way to debug dynamic queries.

3. You need to handle NO_DATA_FOUND exception because it will throw error for all those tables which do not have any match for the filter you are using.

SQL> DECLARE
  2    v_stmt VARCHAR2(1000);
  3    v_ss   VARCHAR2(30) := 'Argentina';
  4    v_own ALL_TAB_COLUMNS.OWNER%TYPE;
  5    v_tab_nam ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
  6    v_col_nam ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
  7    CURSOR cur_asc
  8    IS
  9      SELECT t.owner,
 10        t.table_name,
 11        t.column_name
 12      FROM SYS.ALL_TAB_COLUMNS t
 13      WHERE t.OWNER LIKE 'HR'
 14      AND t.DATA_TYPE LIKE 'VARCHAR2';
 15  BEGIN
 16    FOR rec IN cur_asc
 17    LOOP
 18      v_stmt := 'SELECT '''||rec.owner||''', '''|| rec.table_name||''', '
 19                           || rec.column_name||' FROM '||rec.owner||'.'
 20                           || rec.table_name||' WHERE '||rec.column_name||' LIKE :1';
 21      BEGIN
 22        EXECUTE IMMEDIATE v_stmt INTO v_own,
 23        v_tab_nam,
 24        v_col_nam USING v_ss;
 25        DBMS_OUTPUT.put_line(v_own || ':' || v_tab_nam || ':' || v_col_nam);
 26      EXCEPTION
 27      WHEN NO_DATA_FOUND THEN
 28        NULL;
 29      END;
 30      END LOOP;
 31    END;
 32    /
HR:COUNTRIES:Argentina

PL/SQL procedure successfully completed.