Re: stored proc returning array

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 4 Mar 2004 08:05:47 -0800
Message-ID: <2687bb95.0403040805.56f4525_at_posting.google.com>


"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<rrI0c.94547$4o.117648_at_attbi_s52>...
> "Michael Trosen" <michaeltrosen_at_yahoo.com> wrote in message
> news:54ff5605.0403010553.a4414c6_at_posting.google.com...
> > 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 --
>
> 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 -- Received on Thu Mar 04 2004 - 17:05:47 CET

Original text of this message