Re: Oracle Dope needs help with retrieving records from stored procedure

spawncamper wrote:

> I'm fairly new to Oracle, so I apologize in advance if this question
> makes you shake your head. However, I'm trying to create a stored
> procedure in Oracle 8i to return a value that would be called using
> SELECT EMPLOYEEID FROM EMPLOYEELASTNAME('JONES') where
> EMPLOYEELAASTNAME would be the name of the procedure and would
> obviously return the id.
>
> When I create a proc to do a select, I get a "an INTO clause is
> expected in this SELECT statement" error. Doing research on the
> internet has not been fruitful. Any assistance for would be greatly
> appreciated.
>
> Thanks,
>
> An Oracle Dope

A sample of the code in question and Oracle version information would be nice... You need to start with the error message... what is it telling you?

To get data out of a procedure, you have to put it somewhere (in a variable) where are you storing the results of your query? Based on the information you did provide, a simple google search for 'oracle procedure select into' should be sufficient to find what you are looking for...

like this from: << http://www.ilook.fsnet.co.uk/ora_sql/sqlplus11.htm >>

CREATE OR REPLACE PROCEDURE MYPROC1
(TEMP_COST NUMBER(10,2))
IS BEGIN
    SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21;     IF TEMP_COST > 0 THEN
       UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21;     ELSE
       UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;     END IF;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, ‘ISBN 21 NOT FOUND’);
END MYPROC1; Michael Austin. Received on Thu Jun 17 2004 - 21:15:05 CDT