Price Waterhouse LLP ( 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;
: 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.

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

3     CURSOR c_employee IS
4            select name into empname from employee;
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:                      <->
<->                                                               <->
<->                                    <->
<->                                                               <->
<->             email:                    <->

Back to Ari Kaplan's Home Page ">Back to Ari Kaplan's Home Page