Re: How to get multiple row via a stored procedure

You can do one of two things:
1) Pass a PL/SQL table into the packaged procedure and populate the table, then pass this table back. The big drawback with this is loss of control. You're expecting the entire data set returned by the query to be exhaustively fetched; plus, if you're not running 7.3 or 8 your PL/SQL tables can only store one kind of scalar value. If you're running 7.3, the PL/SQL table can be based on a record type. 2) Create a persistent, packaged cursor in the package body (so it's hidden), then manage it with a packaged procedure. This procedure must detect when to open, close or reopen the cursor, and fetch repetitively from it, returning a record type back as well as the status of the cursor. This is how I'd do it. For example:
CREATE OR REPLACE PACKAGE emp_pkg IS

• define global constants
• valid cursor operations OP_OPEN CONSTANT VARCHAR2(1) := 'O'; -- open a cursor OP_CLOSE CONSTANT VARCHAR2(1) := 'C'; -- close a cursor OP_FETCH CONSTANT VARCHAR2(1) := 'F'; -- fetch from an open a cursor
• define all operations on employee table PROCEDURE get_employee_by_state( Prec IN OUT employee%ROWTYPE, -- input is a key, output is whole record Poperation IN VARCHAR2, -- OP_OPEN, OP_CLOSE, OP_FETCH Pstatus IN OUT NUMERIC); -- success or failure of operation END emp_pkg; /

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

• define all global, hidden, persistent cursors
• get all employees from a particular state (i.e., CO, CT, PA) CURSOR get_emps_by_state(Pstate employee.state%TYPE) IS SELECT * FROM employee WHERE state = Pstate;
• define all operations on employee table PROCEDURE get_employee_by_state( Prec IN OUT employee%ROWTYPE, -- input is a key, output is whole record Poperation IN VARCHAR2, -- 'O' for Open, 'C' for Close, 'F' for Fetch Pstatus IN OUT NUMERIC) IS -- success or failure of operation BEGIN
  • perform the requested operation IF (Poperation = OP_FETCH) THEN FETCH get_emps_by_state INTO Prec; ELSIF (Poperation = OP_OPEN) THEN OPEN get_emps_by_state(Prec.state); -- open the cursor using state ELSIF (Poperation = OP_CLOSE) THEN CLOSE get_emps_by_state; ELSE -- invalid operation NULL; -- do some kind of error handling here (maybe raise exception) END IF; -- perform an operation Pstatus := SQLCODE; -- capture success or failure EXCEPTION WHEN OTHERS THEN Pstatus := SQLCODE; NULL; -- do some kind of error handling here too END get_employee_by_state; END emp_pkg; /

This is a bare bones example of how to manage a persistent cursor. You could do more logic in the procedure to handle ROW_NOT_FOUND and other events, instead of leaving it to the client program (like closing the cursor immediately after exhausting the data set). If you knew the various keys on the table were of different types or could be distinguished by the types and number of columns involved, you could name the procedures all the same (overloading) with different parameter lists (listing the key columns instead of passing them in through the record parameter). This makes them easier for an applications programmer to use. In order to design these procedures correctly, you need to enumerate all the access methods on a particular table (probably by every index defined on the table) an applications programmer will need, and what level of support (i.e., automatically closing the cursor, testing for an invalid cursor state, etc.) the procedure must provide.

• Dan Clamage

Price Waterhouse LLP <user_at_msn.com> wrote in article <01bcd813$0cf6a440$eb322299_at_nakhtar.us.pw.com>...
> I am developing an application in VC++ and accessing the Oracle database
> via ODBC. I have to access the data via stored procedure. I created a
> stored procedure in Oracle as:
> Create stored procedure....
> and in the body
> ()
> is empname employee.name%TYPE;
> Begin
> select name into empname from employee;
> end;
> since this will return multiple records the sqlplus complains and can't
> execute it and if I try to execute it via ODBC, I get the same error
> message.
> Is this possible at all in Oracle. I have to make my application
 compatible
> with Access and Oracle. This method does work in Access. Although
 Access
> stored procedure are Predefined queries.
> n_akhtar_at_msn.com
Received on Tue Oct 14 1997 - 00:00:00 CDT