CODEWITHSUNDEEP
oracle-databaseplsql
chris01
chris01

Reputation: 12427

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

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?

Upvotes: 2

Views: 542

Answers (2)

Popeye
Popeye

Reputation: 35930

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.

Upvotes: 6

Blag
Blag

Reputation: 5894

You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

From the Oracle documentation : https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm

So yes, your 2nd query is bound to fail. Your concat in the first one seem the good way to go.

Upvotes: 2

Related Questions