Dynamic SQL - pass table and column name

jherron · #1 (**permalink**) 06-30-04, 22:00

I am trying to write an Oracle function where the input parameters are a table name and a column name. Inside the function I am creating a cursor to select the specified column from the specified table. Here is what I have mocked up thus far:

Code:

   
CREATE OR REPLACE Function Get_Change_date ( tablename_in VARCHAR2,
 columnname_in VARCHAR2 )Return DATE As

v_username VARCHAR2(30);
v_runtime DATE;

CURSOR c_runtimes IS
SELECT [columnname_in], RUN_TIME FROM [tablename_in] order by RUN_TIME desc;

BEGIN

Open c_runtimes;
Fetch c_runtimes into v_username, v_runtime;

RETURN v_runtime;
CLOSE c_runtimes;
End Get_Change_Date;
/

Any help would be greatly appreciated!

andrewst · #2 (**permalink**) 07-01-04, 05:32

This requires dynamic SQL and a REF CURSOR:

Code:

CREATE OR REPLACE Function Get_Change_date ( tablename_in VARCHAR2,
 columnname_in VARCHAR2 )Return DATE As

 v_username VARCHAR2(30);
 v_runtime DATE;
 TYPE rc_type IS REF CURSOR;
 rc rc_type;

BEGIN

  OPEN rc FOR 'SELECT ' || columnname_in || ', RUN_TIME FROM ' || tablename_in || ' ORDER BY RUN_TIME DESC';
  FETCH rc INTO v_username, v_runtime;
  CLOSE rc;

  RETURN v_runtime;

End Get_Change_Date;
/

However, the columnname_in parameter seems to be redundant as you don't really make any use of the value in that column. An equivalent function would be:

Code:

CREATE OR REPLACE Function Get_Change_date ( tablename_in VARCHAR2 )
Return DATE As

	v_runtime DATE;

BEGIN

  EXECUTE IMMEDIATE 'SELECT MAX(RUN_TIME) FROM ' || tablename_in
  INTO v_runtime;

  RETURN v_runtime;

End Get_Change_Date;
/

jherron · #3 (**permalink**) 07-01-04, 09:27

Tony,

This complied beautifully, but how do you run it to make sure its working? This is a novice question and I do appreciate your response!

Jessica

andrewst · #4 (**permalink**) 07-01-04, 09:30

Since it is a function, you can easily test it like this:

select get_change_date ('EMP') from dual;

or if you still have the clumnname_in parameter:

select get_change_date ('EMP','EMPNO') from dual;

jherron · #5 (**permalink**) 07-01-04, 09:36

Thank you VERY MUCH!

Everyone on these boards is VERY helpful!

Jessica

Internet Services

Web Development

Digital Marketing

Consumer Tech