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?

what people are saying is, in fact, correct. You can't do this this way. If you want, post the 'big picture' question or feel free to send it to me. — moleboy
– moleboy, Commented Aug 20, 2009 at 14:09

jva · Accepted Answer · 2009-08-17 20:55:28Z

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.

answered Aug 17, 2009 at 20:55

jva

2,8051 gold badge28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Hale Over a year ago

Your comment re: the execute immediate context is as I suspected, though I started to wonder if something could be hacked together using sys_context()... And re: the workaround passing the ID, in one instance this function will already be called once per row in a loop through 700k rows of mapped text file. Another select is probably not cool though admittedly I have not profiled it. Thanks for the response.

Gary Myers · Accepted Answer · 2009-08-17 22:47:23Z

1

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.

answered Aug 17, 2009 at 22:47

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

1 Comment

Jamie Hale Over a year ago

It's just harder to maintain. Truth be told, I'm probably not going to leave this in the production code - I think I'll have to find a different way to do it. Thanks for your answer though.

Pierre-Gilles Levallois · Accepted Answer · 2009-08-18 14:57:14Z

1

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.

answered Aug 18, 2009 at 14:57

Pierre-Gilles Levallois

4,3804 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related