Fetch record into a plsql table (record.column_1 into table(index_1))

Dear colleagues,

My plsql code automatically generates select statements, which look like this -

select
  stringfield_1,
  to_char(numeric_field_2) as stringfield_2,   to_char(date_field,mask) as stringfield_3,   ...
from
  underlying tables
where
  joins, filters

These statements are executed using Native Dynamic SQL and every row in the result set is converted into a plsql table as follows -

type columnlist is table of varchar2(4000) columns columnlist;
...

columns(1) := result.stringfield_1;
columns(2) := result.stringfield_2;
...

columns(N) := result.stringfield_N;
...

What is the most efficient and elegant way of doing it, considering that the number of columns is not known beforehand?

Currently I use the following technique –

I change the select clause of my autogenerated queries so that they look loke this -

select

stringfield_1||char(9)||to_char(numeric_field_2)||char(9)||to_char(date_field,mask)|| …
from
  underlying tables
where
  joins, filters

Now every statement returns records that contain of one varchar column. Upon fetching a record I parse this column into my plsql table using "while instr(result,char(9),pos) <> 0 loop … "

Could you suggest a better way of doing this?

Thanks and Regards,
Alex

Oracle 9.0.1

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG