Re: PL/SQL Problem

Am 18 Jan 2005 20:01:45 -0800 schrieb <pasindud_at_gmail.com>:

> Hi,
>
> I have a record type and I also have a string variable and a interger
> value.
> ex:
> rec MY_TAB%ROWTYPE;
> column_name VARCHAR2(30);
> column_index INTEGER;
>
> Is there a way to access get a value from the rec given when i have a
> value for column_name or column_index.
>
> Yes I know i can get the value like rec.my_field. But is there a way to
> get this when the fild_name is given dynamically through another
> variable like column_name or when the column_id is given through
> column_index.

The real problem here is to choose dynamically the correct datatype to hold the result. I see two possible solutions:

1)
Of course you may write a function

function(rec in MY_TAB%ROWTYPE,

          column_name in varchar2,
          type_indicator out number) return varchar2;

which uses in their body a case structure to return the value (converted to varchar2) depending on Column_Name. The type_indicator should be set according to the datatype of the column.

2)
If possible, redesign the application and use "execute immediate" to fetch dynamically the column_value. But again you will have the datatype problem and will have to use different conversion parts in your dynamically created SQL.

-- 
Frank Piron,
defrankatkonaddot
(leftrotate two)