stored proc returning array

[Michael Trosen]

Hi Everyone,

I hope someone can help, I'm pretty new to pro*c programming.

I have the following application setup:

a pro*c program calls a stored procedure and recieves a cursor back:

the cursor is defined as: SQL_CURSOR delpt_cursor

it's assigned by:
:delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)

So, now I have all the data that was retrieved in the stored procedure
in a cursor, and I can loop through it:

for (;;)
{
get each piece of data
put data in flat file
}

The problem with this approach is that it is too slow..

So, i'm looking at instead of returning a cursor, returning a host
array... does it make sense to do this instead? If so, how do you get
the data out of the host array in the Pro*C code?

Thank you for any help!!

Michael

[Mark D Powell]

mi***********@yahoo.com (Michael Trosen) wrote in message news:<54**************************@posting.google. com>...

Hi Everyone,

I hope someone can help, I'm pretty new to pro*c programming.

I have the following application setup:

a pro*c program calls a stored procedure and recieves a cursor back:

the cursor is defined as: SQL_CURSOR delpt_cursor

it's assigned by:
:delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)

So, now I have all the data that was retrieved in the stored procedure
in a cursor, and I can loop through it:

for (;;)
{
get each piece of data
put data in flat file
}

The problem with this approach is that it is too slow..

So, i'm looking at instead of returning a cursor, returning a host
array... does it make sense to do this instead? If so, how do you get
the data out of the host array in the Pro*C code?

Thank you for any help!!

Michael

First, is it really necessary to get the data via a stored procedure
rather than just querying it directly?

Either way make sure the problem is in passing the data back to the
program and not in the performance of the query that builds the
cursor. In order words make sure you do not have a quuery statement
tuning issue instead of a retrieval issue.

If you can query the data directly you should be able to replace your
single row processing loop with a much faster array fetch. Oracle
will shove the data into a C language array and you print it from
there.

HTH -- Mark D Powell --

[Michael Trosen]

Mark,

thank you for your reply.

The reason I want the pro*c program to call the stored procedure
instead of just getting a cursor with a select in the pro*c program is
because I want to keep all the business rules for getting the data in
a 'centralized' location. There are many different applications that
need to get data from the database, and rather than having a bunch of
business rules scattered throughout various programs, they're
'centralized' in the database and stored procedures.

Is there a way to recieve in the pro*C program an array of structures
returned from the stored procedure? For example, a new version of my
stored procedure works like this:

1. get the data
2. eliminate some of the data and put non eliminated data into a
record
with the following definition:
TYPE mm_addrs_rec IS RECORD (
walk_seq NUMBER (9),
dpbc_dgts NUMBER (2),
dpbc_chk_dgt NUMBER (1),
drop_seq NUMBER (3),
st_num VARCHAR (10),
st_pre_direct VARCHAR (2),
st_name VARCHAR2 (28),
st_suffix VARCHAR2 (4),
st_post_direct VARCHAR2 (2),
sud VARCHAR2 (4),
sun VARCHAR2 (8),
plus4 VARCHAR2 (4),
seas_ind VARCHAR2 (1),
atz VARCHAR2 (2),
profile_type_cd NUMBER,
addrs_id NUMBER (10),
supr_delv_ind VARCHAR2 (1)
);

3. each record is thrown into a varray, defined as:
TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;

4. The stored procedure returns the array to the pro*c
program

The problem I have is I don't know how to get the array in the pro*c
program
and use the data within it..

Any suggestions?

Thank you!
Michael Trosen
Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

mi***********@yahoo.com (Michael Trosen) wrote in message news:<54**************************@posting.google. com>...

Hi Everyone,

I hope someone can help, I'm pretty new to pro*c programming.

I have the following application setup:

a pro*c program calls a stored procedure and recieves a cursor back:

the cursor is defined as: SQL_CURSOR delpt_cursor

it's assigned by:
:delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)

So, now I have all the data that was retrieved in the stored procedure
in a cursor, and I can loop through it:

for (;;)
{
get each piece of data
put data in flat file
}

The problem with this approach is that it is too slow..

So, i'm looking at instead of returning a cursor, returning a host
array... does it make sense to do this instead? If so, how do you get
the data out of the host array in the Pro*C code?

Thank you for any help!!

Michael

First, is it really necessary to get the data via a stored procedure
rather than just querying it directly?

Either way make sure the problem is in passing the data back to the
program and not in the performance of the query that builds the
cursor. In order words make sure you do not have a quuery statement
tuning issue instead of a retrieval issue.

If you can query the data directly you should be able to replace your
single row processing loop with a much faster array fetch. Oracle
will shove the data into a C language array and you print it from
there.

HTH -- Mark D Powell --

[Jim Kennedy]

"Michael Trosen" <mi***********@yahoo.com> wrote in message
news:54*************************@posting.google.co m...

Mark,

thank you for your reply.

The reason I want the pro*c program to call the stored procedure
instead of just getting a cursor with a select in the pro*c program is
because I want to keep all the business rules for getting the data in
a 'centralized' location. There are many different applications that
need to get data from the database, and rather than having a bunch of
business rules scattered throughout various programs, they're
'centralized' in the database and stored procedures.

Is there a way to recieve in the pro*C program an array of structures
returned from the stored procedure? For example, a new version of my
stored procedure works like this:

1. get the data
2. eliminate some of the data and put non eliminated data into a
record
with the following definition:
TYPE mm_addrs_rec IS RECORD (
walk_seq NUMBER (9),
dpbc_dgts NUMBER (2),
dpbc_chk_dgt NUMBER (1),
drop_seq NUMBER (3),
st_num VARCHAR (10),
st_pre_direct VARCHAR (2),
st_name VARCHAR2 (28),
st_suffix VARCHAR2 (4),
st_post_direct VARCHAR2 (2),
sud VARCHAR2 (4),
sun VARCHAR2 (8),
plus4 VARCHAR2 (4),
seas_ind VARCHAR2 (1),
atz VARCHAR2 (2),
profile_type_cd NUMBER,
addrs_id NUMBER (10),
supr_delv_ind VARCHAR2 (1)
);

3. each record is thrown into a varray, defined as:
TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;

4. The stored procedure returns the array to the pro*c
program

The problem I have is I don't know how to get the array in the pro*c
program
and use the data within it..

Any suggestions?

Thank you!
Michael Trosen
Ma*********@eds.com (Mark D Powell) wrote in message

news:<26**************************@posting.google. com>...

mi***********@yahoo.com (Michael Trosen) wrote in message news:<54**************************@posting.google. com>...

Hi Everyone,

I hope someone can help, I'm pretty new to pro*c programming.

I have the following application setup:

a pro*c program calls a stored procedure and recieves a cursor back:

the cursor is defined as: SQL_CURSOR delpt_cursor

it's assigned by:
:delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)

So, now I have all the data that was retrieved in the stored procedure
in a cursor, and I can loop through it:

for (;;)
{
get each piece of data
put data in flat file
}

The problem with this approach is that it is too slow..

So, i'm looking at instead of returning a cursor, returning a host
array... does it make sense to do this instead? If so, how do you get
the data out of the host array in the Pro*C code?

Thank you for any help!!

Michael

First, is it really necessary to get the data via a stored procedure
rather than just querying it directly?

Either way make sure the problem is in passing the data back to the
program and not in the performance of the query that builds the
cursor. In order words make sure you do not have a quuery statement
tuning issue instead of a retrieval issue.

If you can query the data directly you should be able to replace your
single row processing loop with a much faster array fetch. Oracle
will shove the data into a C language array and you print it from
there.

HTH -- Mark D Powell --

What you are describing is a cursor. (using an array interface to retrieve
more than 1 row at a time) You can retrieve a cursor from a stored
procedure(ref cursor). You can certainly get the information from a stored
procedure and several tables.
Jim

[Mark D Powell]

"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<rrI0c.94547$4o.117648@attbi_s52>...

"Michael Trosen" <mi***********@yahoo.com> wrote in message
news:54*************************@posting.google.co m...

Mark,

thank you for your reply.

The reason I want the pro*c program to call the stored procedure
instead of just getting a cursor with a select in the pro*c program is
because I want to keep all the business rules for getting the data in
a 'centralized' location. There are many different applications that
need to get data from the database, and rather than having a bunch of
business rules scattered throughout various programs, they're
'centralized' in the database and stored procedures.

Is there a way to recieve in the pro*C program an array of structures
returned from the stored procedure? For example, a new version of my
stored procedure works like this:

1. get the data
2. eliminate some of the data and put non eliminated data into a
record
with the following definition:
TYPE mm_addrs_rec IS RECORD (
walk_seq NUMBER (9),
dpbc_dgts NUMBER (2),
dpbc_chk_dgt NUMBER (1),
drop_seq NUMBER (3),
st_num VARCHAR (10),
st_pre_direct VARCHAR (2),
st_name VARCHAR2 (28),
st_suffix VARCHAR2 (4),
st_post_direct VARCHAR2 (2),
sud VARCHAR2 (4),
sun VARCHAR2 (8),
plus4 VARCHAR2 (4),
seas_ind VARCHAR2 (1),
atz VARCHAR2 (2),
profile_type_cd NUMBER,
addrs_id NUMBER (10),
supr_delv_ind VARCHAR2 (1)
);

3. each record is thrown into a varray, defined as:
TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;

4. The stored procedure returns the array to the pro*c
program

The problem I have is I don't know how to get the array in the pro*c
program
and use the data within it..

Any suggestions?

Thank you!
Michael Trosen
Ma*********@eds.com (Mark D Powell) wrote in message

news:<26**************************@posting.google. com>...

mi***********@yahoo.com (Michael Trosen) wrote in message news:<54**************************@posting.google. com>... > Hi Everyone,
>
> I hope someone can help, I'm pretty new to pro*c programming.
>
> I have the following application setup:
>
> a pro*c program calls a stored procedure and recieves a cursor back:
>
> the cursor is defined as: SQL_CURSOR delpt_cursor
>
> it's assigned by:
> :delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)
>
> So, now I have all the data that was retrieved in the stored procedure
> in a cursor, and I can loop through it:
>
> for (;;)
> {
> get each piece of data
> put data in flat file
> }
>
> The problem with this approach is that it is too slow..
>
> So, i'm looking at instead of returning a cursor, returning a host
> array... does it make sense to do this instead? If so, how do you get
> the data out of the host array in the Pro*C code?
>
> Thank you for any help!!
>
> Michael

First, is it really necessary to get the data via a stored procedure
rather than just querying it directly?

Either way make sure the problem is in passing the data back to the
program and not in the performance of the query that builds the
cursor. In order words make sure you do not have a quuery statement
tuning issue instead of a retrieval issue.

If you can query the data directly you should be able to replace your
single row processing loop with a much faster array fetch. Oracle
will shove the data into a C language array and you print it from
there.

HTH -- Mark D Powell --

What you are describing is a cursor. (using an array interface to retrieve
more than 1 row at a time) You can retrieve a cursor from a stored
procedure(ref cursor). You can certainly get the information from a stored
procedure and several tables.
Jim

Mike, sorry I missed your response till now. I have never used the
varray datatype. I would have probably just used a pl/sql table
(array) of records.

And while using packaged or stored code is great for update processes
I would not use these for selecting data if a view or direct select
could be done.

HTH -- Mark D Powell --