Let me brifly explain what i am trying to accomplish. I have a mapping
table M which describes how records of table A should be transformed
into records of table B. The mapping must vary according the specific
type of record in A (given by A.type). I wanted to perform the mapping
something like this (not exactly, planning on encapsulating the
mapping logic inside a stream function, but the principle remains
similar):
SELECT
...
CASE
WHEN M.field_1_mapping IS NOT NULL THEN
-- column of A given by value of M.field_1_mapping
ELSE
null -- field_1 not filled for record type
END field_1,
--- etc.
FROM
table_a A,
mapping_table M
WHERE
A.TYPE = M.TYPE
So my question is how I might do this. Again i cannot use dynamic SQL
as it has to different for each record type, but if column could be
selected based on the value of the mapping field then the obovemention
sql would work.
I realize that it simply might not be possible (and may go against the
PL/SQL design philosophy), in which case i would welcome any
suggestions you might have as to how this problem could be solved.
P.S: I suppose it would be possible to simply hard-code a mapping
function e.g.:
FUNCTION get_field(field_key IN VARCHAR(32), a NOCOPY IN table_a
%rowtype) RETURN VARCHAR(2000) IS
out VARCHAR2(2000)
BEGIN
-- ...
IF field_key = 'field_1' THEN
RETURN a.field_1; END IF;
-- ..
END;
But that seems really inelegant.
On Mar 26, 10:23 am, Jens <[email protected]> wrote:
> Hello everyone,
>
> Is there any way to access a dynamically accessing member of a User-
> defined record, object or reference cursor using a variable? E.g.
> Something like
>
> get_member(my_object, 'member name');
>
> Or maybe
>
> my_object.$'member name';
>
> - Jens
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
