Home » SQL & PL/SQL » SQL & PL/SQL » Accessing a record´s column dynamically
Accessing a record´s column dynamically [message #309681] Fri, 28 March 2008 07:56 Go to next message
brunosdiniz
Messages: 3
Registered: March 2008
Junior Member
Hi everybody,

I´m stuck with a problem for quite some time now and I hope someone will be able to help me with this.

I have two cursors and two variables that represent records of the result sql executed, as follows:

CURSOR cur_myTable IS SELECT * FROM MyTable;
CURSOR cur_cols    IS SELECT * FROM cols WHERE TABLE_NAME = 'MyTable';

v_myTable_rec cur_one %ROWTYPE;
v_cols_rec    cur_two %ROWTYPE;


I´m looping through 'cur_myTable' fetching each row into 'v_myTable_rec' and inside this loop I have a second one that loops through 'cur_cols'.

The only way I found to access the columns of the record is either call
v_myTable_rec.columnName or
v_myTable_rec."columnName"

What I need, and couldn´t manage to achieve, is to dynamically access a column in the main record (v_myTable_rec) using as "columnName" the value of a column from the second record (v_cols_rec), which in this case would contain the names of all the columns of the main record.

Something like this:

v_myTable_rec.'"' || v_cols_rec.COLUMN_NAME || '"'


Any help will be much appreciated.

Bruno.
Re: Accessing a record´s column dynamically [message #309692 is a reply to message #309681] Fri, 28 March 2008 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it like this.
You have to use DBMS_SQL, if you don't know the columns you are selecting.
BUT you should never have "*" but the list of the columns unless you write a SQL processor.

Regards
Michel
Re: Accessing a record´s column dynamically [message #309751 is a reply to message #309681] Fri, 28 March 2008 12:17 Go to previous messageGo to next message
brunosdiniz
Messages: 3
Registered: March 2008
Junior Member
I´d imagined it wouldn´t be possible.
I´ll have a look at this DBMS_SQL and see if I can manage.

Thanks a lot Michel.
Re: Accessing a record´s column dynamically [message #309755 is a reply to message #309751] Fri, 28 March 2008 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To get an idea of how to do it, study T.Kyte's print_table function.

Regards
Michel

[Updated on: Fri, 28 March 2008 12:22]

Report message to a moderator

Re: Accessing a record´s column dynamically [message #309756 is a reply to message #309681] Fri, 28 March 2008 12:23 Go to previous messageGo to next message
brunosdiniz
Messages: 3
Registered: March 2008
Junior Member
Will do. Thanks again.
Re: Accessing a record´s column dynamically [message #309757 is a reply to message #309756] Fri, 28 March 2008 12:24 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note I changed the link, the previous one no more work.

Regards
Michel
Previous Topic: Problem Group by command
Next Topic: bulk data loading in oracle 9i
Goto Forum:
  


Current Time: Wed Dec 07 16:47:49 CST 2016

Total time taken to generate the page: 0.12005 seconds