Re: How to get multiple rows via a stored procedure

From: Ari Kaplan <akaplan_at_interaccess.com>
Date: 1997/10/14
Message-ID: <61vsal$9pt$1@nntp3.interaccess.com>#1/1

It is no problem to manipulate several records in PL/SQL. Your code is an implicit cursor. You need to create an explicit cursor. Follow the example below:

1 DECLARE

2     EMPNAME EMPLOYEE.NAME%TYPE;
3     CURSOR c_employee IS
4            select name into empname from employee;
5  BEGIN
6     OPEN c_employee;
7     LOOP
8        FETCH c_employee INTO empname;
9        EXIT WHEN c_employee%NOTFOUND;
10          DBMS_OUTPUT.PUT_LINE('The employee name is:'||empname);

11 END LOOP;
12 CLOSE c_employee;
13 END; Lines 3-4 create an explicit cursor, "c_employee" that will be used to process the SQL query, one record at a time. Line 6 "Open"s the cursor for use, and executes the SQL query. Line 8 "FETCH"es a record into the empname variable. Line 9 EXITS the loop if there was no more data to be retrieved Line 10 outputs the info on the record. At this point, you may do what you will with the data. This example simply prints it to the screen. Line 11 points to the end of the loop, at which point processing returns to line 8.
Line 12 closes the cursor

Hope this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 115+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Price Waterhouse LLP (user_at_msn.com) wrote:
: Hello,
:
: 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.
: Thanks in advance.
: n_akhtar_at_msn.com
:
Received on Tue Oct 14 1997 - 00:00:00 CDT