Re: stored proc returning array
Date: Mon, 01 Mar 2004 15:14:31 GMT
Message-ID: <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 --