PL/SQL: How to pass a tablename to a dynamic SQL?

user_307297 0
Viewed 217

I am new to PL/SQL and I try to have the table name of a SELECT dynamically set by a parameter.

This is working fine.

DECLARE 
  FUNCTION foo (pat VARCHAR) RETURN NUMBER IS
    tabname VARCHAR (100) := 'my_table';
    n NUMBER := -1;
    sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM ' || tabname || ' WHERE bezeichnung LIKE :1';
  BEGIN
    EXECUTE IMMEDIATE sqlcmd INTO n USING pat;
    RETURN n;
  END foo;
BEGIN
    dbms_output.put_line (foo ('bla%'));
END;

If I try to have tabname set by a parameter as it is with pat then it fails with ther error :

invalid table name

DECLARE

  FUNCTION defval (pat VARCHAR, offs NUMBER) RETURN NUMBER IS
    tabname VARCHAR (100) := 'A_KGL_EIGENSCHAFTEN';
    n NUMBER := -1;
    sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM :1 WHERE bezeichnung LIKE :2';
  BEGIN     
    EXECUTE IMMEDIATE sqlcmd INTO n USING tabname, pat;
    dbms_output.put_line ('tabname: ' || tabname);
    dbms_output.put_line ('n: ' || n);
    RETURN n;
  END defval;
BEGIN
    dbms_output.put_line (defval ('LPG.GAX.%.DBE', 2));
END;

How can I set the table name by this bound parameters?

plsql oracle
2 Answers

If you are looking to prevent the sql injection using concate then you can use the sys.DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name)

So your string variable should look like this:

sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM ' 
                        || sys.DBMS_ASSERT.SQL_OBJECT_NAME(tabname) 
                        || ' WHERE bezeichnung LIKE :1';

You can learn more about DBMS_ASSERT from oracle documentation.

user_115656290
user_55462670
Related