Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6

    Unanswered: Dynamic SQL - pass table and column name

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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;
    /

  3. #3
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6
    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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;

  5. #5
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6
    Thank you VERY MUCH!

    Everyone on these boards is VERY helpful!

    Jessica

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •