How to fetch named column from row parameter dynamically in Oracle PL/SQL?

Question

I have a procedure like this:

procedure foo(
  p_field_name in varchar2,
  p_record in table%rowtype )
is
  v_value varchar2( 100 );
begin
  execute immediate 'select p_record.' || p_field_name || ' from dual' into v_value;
end

Oracle complains that "p_record"."my_field_name" is an invalid identifier, which leads me to believe that the execute immediate is running in some other context.

Is it possible to do what I'm trying to do above?

Answer 1

What you do in EXECUTE IMMEDIATE should be doable in any context. You example does not fit this as it would not be doable outside of your procedure.

One possible workaround - pass ID instead of full row and select that single column from table. Although this could kill performance if used often.

Answer 2

What's wrong with a IF/ELSIF

IF    p_field_name = 'COL1' THEN v_value := p_record.col1
ELSIF p_field_name = 'COL2' THEN v_value := p_record.col2
...
END IF;

You could event generate that from a SELECT from ALL_TAB_COLUMNS. It may not be pretty but it would work.

Answer 3

I think what you are try to do is impossible. Execute immediate orders are dynamic SQL executions. The sql statment is not parsed when you create "foo" procedure, but at runtime. For oracle it is 'select p_record.' || p_field_name || ' from dual' is a varchar, and p_record too. So this explains why you have this error. The solution is to write something like if/elsif/... or case/when.