Fetch record into a plsql table (record.column_1 into table(index_1)) 
Author Message
 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.***.com/



Wed, 11 May 2005 21:38:10 GMT
 Fetch record into a plsql table (record.column_1 into table(index_1))

Quote:

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

I assume you know the query that will return all of the columns?
Then, I would always execute this to help save the SQLShared
area.  Then, I would use the current logic for deciding what
SQLString to execute and use it to decide how to parse the
resultant cursor.
--
Galen Boyer


Fri, 13 May 2005 10:02:18 GMT
 Fetch record into a plsql table (record.column_1 into table(index_1))
On Sat, 23 Nov 2002 13:38:10 +0000 (UTC), "Alexander Miroshnikov"

Quote:

>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?

If you at least know the maximum possible number of columns, maybe you
could add a "BULK COLLECT INTO stringtable1 stringtable2 etc." clause
inside the dynamic SQL.  Then to get all the fields from row N of the
result set, you'd reference stringtable1(N), stringtable2(N), and so
on.

John



Fri, 13 May 2005 10:34:46 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Performance problem in fetching record from table containg 2 million record

2. 1 table for many records or many tables with few records

3. Cannot add record to Visual Foxpro table after deleting all records in table

4. one table with lots of records or more table with less records

5. can SQL fetch the last record in a table

6. Linked tables in Access not fetching enough records

7. TQuery fetches all records when updating any other table or query

8. fetch the nth record in an oracle table

9. Lots records not being posted in table, identity_column shows missing records

10. creating a table of pairs of record IDs for records with similar content in text field

11. Counting Records in Table not Connected to Records in Another (SQL Newbie)

12. Update record with data from another record in the same table


 
Powered by phpBB® Forum Software