CODEWITHSUNDEEP
sqlplsqloracle11g
merp
merp

Reputation: 282

Oracle functions: How to pass a table name as parameter, and use a cursor result as a table name?

I need help with this oracle function I am trying to create. Basically what I want to do is pass in the name of a table, and return the maximum value of the column which is the variable table name + '_ID' So it would look like this (tableName)_ID

Here's what I've tried (But I can't get it to even work):

CREATE OR REPLACE FUNCTION RETURN_ID(tableName IN varchar2)
return int
IS 
   curResult varchar2;

   cursor cur1 is
       SELECT column_name
       FROM all_tab_cols
       WHERE table_name = tableName
       AND column_name like '%_ID';

BEGIN

   OPEN cur1;
   FETCH cur1 INTO curResult;
   CLOSE cur1;

   SELECT MAX(curResult) AS MaxID
   FROM tableName;

   RETURN maxID;
END RETURN_ID;

Upvotes: 4

Views: 8791

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40603

Replace

SELECT MAX(curResult) AS MaxID
FROM tableName;

with

execute immediate 
  'select max(' || curResult || ')' ||
  '  from ' || tableName 
  into MaxID;

Whenever you want to dynamically change table or column names in a select statement, there almost always is no other way than to resort to execute immediate statements.

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231861

You'd need to use dynamic SQL. Something like

EXECUTE IMMEDIATE 'SELECT MAX(' || tablename || '_id ) ' ||
                  '  FROM ' || tablename
   INTO maxID;

Upvotes: 0

Related Questions