Is it possible to pass table name as a parameter in Oracle?

Question

I want to create a stored procedure like this:

PROCEDURE P_CUSTOMER_UPDATE
  (
      pADSLTable IN Table,
      pAccountname IN NVARCHAR2,
      pStatus IN NUMBER,
      pNote IN NVARCHAR2,
      pEmail IN NVARCHAR2,
      pMobi IN NVARCHAR2,
      pServiceTypeID IN NUMBER,
      pDate IN DATE
  )
  IS
  BEGIN
      UPDATE pADSLTable
      SET STATUS = pStatus, NOTE = pNote, EMAIL = pEmail, MOBI = pMobi, SERVICETYPE_ID = pServiceTypeID, ACTIVATION_DATE = pDate
      WHERE ACCOUNT_NAME = pAccountname;
  END;

Of course, Oracle does not let me do that. Is there a way to work around this problem? Thank you very much.

Solution 1

You have several different tables with exactly the same column names and data types? Smells like a dodgy design.

Anyway, we cannot use variables as database objects in straightforward SQL like that. We have to use dynamic SQL.

PROCEDURE P_CUSTOMER_UPDATE
  (
      pADSLTable IN USER_TABLES.table_name%type,
      pAccountname IN NVARCHAR2,
      pStatus IN NUMBER,
      pNote IN NVARCHAR2,
      pEmail IN NVARCHAR2,
      pMobi IN NVARCHAR2,
      pServiceTypeID IN NUMBER,
      pDate IN DATE
  )
  IS
  BEGIN
      execute immediate 
          'UPDATE '||pADSLTable
          ||' SET STATUS = :1, NOTE = :2, EMAIL = :3, MOBI = :4, SERVICETYPE_ID = :5, ACTIVATION_DATE = :6'
          ||' WHERE ACCOUNT_NAME = :7'
      using pStatus, pNote, pEmail, pMobi, pServiceTypeID, pDate, pAccountname;
  END;

One reason to avoid the use of dynamic SQL is that it is open to abuse. Malicious people can use the parameters to attempt to bypass our security. This is called SQL injection. I think people over estimate the significance of SQL injection. It's not automatically a threat. For instance if the procedure is a private procedure in a package (i.e. not declared in the specification) it is unlikely that anybody will hijack it.

But it is sensible to take precautions. DBMS_ASSERT is a package introduced in Oracle 10g to trap attempted SQL injection attacks. It this case it would be worth using it to validate the passed table name

....
'UPDATE '|| DBMS_ASSERT.simple_sql_name(pADSLTable)
....  

This would prevent anybody passing 'pay_table set salary = salary * 10 where id = 1234 --' as the table name parameter.

Another reason to avoid dynamic SQL is that it is harder to get right and harder to debug. The syntax of the actual statement is only checked at run time. It is good to have a complete suite of unit tests which validate all the passed inputs, to ensure that the procedure doesn't hurl a syntax exception.

Finally, such dynamic SQL doesn't show up in views such as ALL_DEPENDENCIES. This makes it harder to undertake impact analysis and locate all the programs which use a given table or column.

Solution 2

Yes, there is Native Dynamic SQL:

EXECUTE IMMEDIATE 'UPDATE ' || pADSLTable || 
  'SET STATUS = :1, NOTE = :2, EMAIL = :3, MOBI = :4, SERVICETYPE_ID = :5, ACTIVATION_DATE = :6 WHERE ACCOUNT_NAME = :7 '  
  USING pStatus, pNote, pEmail, pMobi, pServiceTypeId, pDate, pAccountname;

Performance and error checking is not as good (no compile-time syntax and schema validation). Beware of SQL injection.

So, if you have only a couple of tables to choose from, consider using an if/then/else construct with all options instead.

Comments

[Adam Musch]

DBMS_ASSERT isn't documented in 10gR2, though it is present. Thanks for pointing it out.