Re: stored proc returning array

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   

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0402281610.4ad97bbb_at_posting.google.com>...
> michaeltrosen_at_yahoo.com (Michael Trosen) wrote in message news:<54ff5605.0402271201.474391bc_at_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 --
Received on Mon Mar 01 2004 - 07:53:51 CST