Addressing columns in a rowtype variable ORACLE

Question

So, my problem is that i can't figure out how to address columns. I got all table data in variable l_table:

CURSOR l_cursor2_cur IS SELECT * FROM exceptions_table;
TYPE table_data_type IS TABLE OF l_cursor2_cur%ROWTYPE INDEX BY BINARY_INTEGER;

l_table_data TABLE_DATA_TYPE;

I would normally do like this (where column1 is actually a name, not a variable):

FOR i IN 1..l_table_data.COUNT LOOP
      ...
      ... l_table_data(i).column1 ...
      ...
   END LOOP;

But i have an array with columns names:

TYPE list_of_column_names IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
l_columns_names LIST_OF_COLUMN_NAMES;

And this doesn't work. I guess i have tried everything. So, is it even possible?

FOR i IN 1..l_table_data.COUNT LOOP
     ...
     ... l_table_data(i).l_columns_names(1) ...
     ...
  END LOOP;

tbone · Accepted Answer · 2014-04-12 18:40:52Z

1

Maybe I misunderstand the question, but you don't need the associative array. Just do something like:

declare

  CURSOR l_cursor2_cur IS SELECT * FROM exceptions_table;

begin


 for rec in l_cursor2_cur
 loop

   dbms_output.put_line('Column1 value is: ' || rec.column1);

 end loop;

end;

Typically if I'm only interested in a few columns, I won't use select *, I'll specify the columns (cursor sel_cur is select column1, column2 from ...)

edited Apr 12, 2014 at 18:40

answered Apr 12, 2014 at 13:49

tbone

15.5k3 gold badges38 silver badges42 bronze badges

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

Comments

Martin Drautzburg · Accepted Answer · 2014-04-12 13:52:33Z

0

In the relational world you cannot easily take table and column-names from variables. And if your DB is properly designed you will never be attempted to do this.

Even for-loops are somewhat ugly in the relational world and so are those record and collection types. Many programmers with an OO background have trouble understanding this. You need to approach yout problem from a different angle.

answered Apr 12, 2014 at 13:52

Martin Drautzburg

5,2831 gold badge29 silver badges40 bronze badges

Collectives™ on Stack Overflow

Addressing columns in a rowtype variable ORACLE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related