Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,365 Comments

Discussions

dynamic column name in select

SJenkins
SJenkins Member Posts: 71 Blue Ribbon

I need a dynamic action (preferably PL/SQL Function Body) to:

declare ret_val varchar(50);

begin

select :column_name into ret_val from table where primary_col = :primary_value;

return ret_val;

end;


basically I have a select list in an interactive grid where they can pick the column_name

then the dynamic action looks up the value in that column and sets it in another column.

problem is, the dynamic action sets it to the column_name instead of the value...

its acting like: select "column_name" into ret_val from table; instead of looking up the value of the column

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond
    Accepted Answer

    Hi,

    If the column names (or table names) are not knows when the statement is compiled, then you need Dynamic SQL. In PL/SQL, that means EXECUTE IMMEDIATE. Here's one way to do that, using the scott.emp table:

    CREATE OR REPLACE FUNCTION get_str
    ( column_name VARCHAR2
    , primary_val scott.emp.empno%TYPE
    )
    RETURN VARCHAR2
    --		get_str returns the value of a given VARCHAR2 column in
    --		the scott.emp table, for the given primary key (empno) value.
    IS
      ret_val	VARCHAR2 (50);
      sql_stmt	VARCHAR2 (100);
    BEGIN
      sql_stmt := 'SELECT ' || column_name
      	   || ' FROM scott.emp'
    	   || ' WHERE empno = :1';
      dbms_output.put_line (sql_stmt || ' = sql_stmt in get_str'); -- FOR DEBUGGING ONLY
      EXECUTE IMMEDIATE sql_stmt INTO ret_val USING primary_val;
      RETURN ret_val;
    END get_str;
    /
    

    Whenever you use EXECUTE IMMEDIATE, display the entire statement before the call to EXECUTE IMMEDIATE. When you begin testing, the EXECUTE IMMEDIATE statement should be commented out; just run the code to see that the statement looks correct before you actually run it. Before moving the code into production, comment out the call to put_line.

    The most important thing to know about Dynamic SQL is: Don't use it unless you really need to. Dynamic SQL is very often the third or fourth best way to do a particular job. Look for better ways before you start a Dynamic SQL approach. Chances are, there's a more efficient, less complicate, more secure, less error-prone way to do whatever you need. In this example, you could have a CASE expression, with all the column names hard-coded.

    SJenkins

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond
    Accepted Answer

    Hi,

    If the column names (or table names) are not knows when the statement is compiled, then you need Dynamic SQL. In PL/SQL, that means EXECUTE IMMEDIATE. Here's one way to do that, using the scott.emp table:

    CREATE OR REPLACE FUNCTION get_str
    ( column_name VARCHAR2
    , primary_val scott.emp.empno%TYPE
    )
    RETURN VARCHAR2
    --		get_str returns the value of a given VARCHAR2 column in
    --		the scott.emp table, for the given primary key (empno) value.
    IS
      ret_val	VARCHAR2 (50);
      sql_stmt	VARCHAR2 (100);
    BEGIN
      sql_stmt := 'SELECT ' || column_name
      	   || ' FROM scott.emp'
    	   || ' WHERE empno = :1';
      dbms_output.put_line (sql_stmt || ' = sql_stmt in get_str'); -- FOR DEBUGGING ONLY
      EXECUTE IMMEDIATE sql_stmt INTO ret_val USING primary_val;
      RETURN ret_val;
    END get_str;
    /
    

    Whenever you use EXECUTE IMMEDIATE, display the entire statement before the call to EXECUTE IMMEDIATE. When you begin testing, the EXECUTE IMMEDIATE statement should be commented out; just run the code to see that the statement looks correct before you actually run it. Before moving the code into production, comment out the call to put_line.

    The most important thing to know about Dynamic SQL is: Don't use it unless you really need to. Dynamic SQL is very often the third or fourth best way to do a particular job. Look for better ways before you start a Dynamic SQL approach. Chances are, there's a more efficient, less complicate, more secure, less error-prone way to do whatever you need. In this example, you could have a CASE expression, with all the column names hard-coded.

    SJenkins
  • SJenkins
    SJenkins Member Posts: 71 Blue Ribbon

    thanks I got it working...