How to Pass Table Name as a Parameter in Oracle

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

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.

passing in table name as plsql parameter

You can use dynamic SQL:

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  execute immediate 'select count(*) from ' || table_name into table_count;
  dbms_output.put_line(table_count);
  return table_count;
end;

There is also an indirect way to get number of rows (using system views):

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  select num_rows
    into table_count
    from user_tables
   where table_name = table_name;

  return table_count;
end;

The second way works only if you had gathered statistics on table before invoking this function.

Dynamic SQL with table name as a parameter

You can only use bind variables (denoted by colons) for values, not for parts of the structure. You will have to concatenate the table and column names into the query:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

Which implies REF_D is a fixed column name that can appear in any table you'll call this for; in a previous question that seems to be a variable. If it is actually a string variable then you'd need to bind and set that:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

If it's supposed to be a date you should make sure the local variable is the right type, or explicitly convert it.

How can I pass column and table name as parameters to a SQL stored procedure

If you create a function like this, ensure it's safe from sql injection with something like the following. This uses dbms_assert to sanitize the inputs against thing like ';drop table xyz;'

 CREATE OR REPLACE FUNCTION getidfromnameparameter (
        in_columnname        VARCHAR2,
        in_tablename         VARCHAR2,
        in_wherecolumn       VARCHAR2,
        in_parametercolumn   VARCHAR2
    ) RETURN VARCHAR2 AS
        idcatc   VARCHAR2(50);
    BEGIN
        EXECUTE IMMEDIATE 'SELECT '
                         || sys.dbms_assert.qualified_sql_name(in_columnName)
                         || ' FROM '
                         || sys.dbms_assert.sql_object_name(in_tableName)
                         || ' WHERE '
                         || sys.dbms_assert.qualified_sql_name(in_whereColumn)
                         || ' = '
                         || sys.dbms_assert.enquote_literal(in_parametercolumn)
       INTO idcatc;

       RETURN idcatc;
  END;
  /

Oracle stored function - pass table name as parameter

1. Your execute immediate will return only one value, the count, so what is there to loop over?
2. Also I'm not sure that execute immediate works with an implicit cursor.
3. In your SQL it looks like you don't have a space after the from keyword.

Try something like this instead:

create or replace function jumlahBaris(namatabel varchar)
return tes_jml_table
is
  tabel tes_jml_table := tes_jml_table();
  the_count integer;
  the_sql varchar(100);
begin
  the_sql := 'select count(*) as jumlah from ' || namatabel;
  execute immediate the_sql INTO the_count;

  if the_count IS NOT NULL THEN
      tabel.extend;
      tabel(1) := tes_jml_obj(the_count);
  end if;
  return tabel;
end;

PL/SQL - function parameter as table name - how is this possible?

If you take this line

for r in ( select * from a ) -- parameter (type varchar2) as table name!

Then Oracle dose not select from varchar2 variable a - it selects from table a which by coincidence has the same name as your variable.

---

Related Topics