You Asked

I am trying to create a simple stored procedure that will return a result set. i.e.
PROCEDURE sp_products (prod_name IN VARCHAR)
IS
BEGIN
SELECT * FROM TBL_PRODUCTS
WHERE P_NAME = prod_name;
END;

but I keep getting the error PLS-00428: an INTO clause is expected in this SELECT statement. I am a SQL Server 7 user and dont understand what I am doing wrong (I dont want to SELECT INTO any table, I just want to return the result set from the TBL_PRODUCTS table). I am new to Oracle so would appreciate a detailed explanation.

and Tom said...

see
</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

for all of the details on this.

followup to comment

someone please explain how:

create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
/

is *harder* then

create or replace function sp_ListEmp return types.cursortype
as
begin
select ename, empno from emp order by ename;
end;
/

???

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Result Sets

sridhar, August 16, 2001 - 12:38 pm UTC

Hi TOM,
Yeah the response was helpfull.
But i don't know why Oracle has implemented it in this way.
Sybase database has much easier way to return Result sets.
And i also have a question about how good it is to leave cursors open?
bye

the link was helpful but

Schesser, April 24, 2002 - 2:40 pm UTC

/*******************************************************************************************/
' /* Create a packaged procedure that accepts a department number and returns the employees. */
' /* [Note: A standalone procedure will not work, it must be a packaged procedure!] */
' /*******************************************************************************************/
'
' CREATE OR REPLACE
' PACKAGE DEPARTMENT AS
' TYPE CURSOR_TYPE IS REF CURSOR;
' PROCEDURE GET_EMPS (I_DEPTNO IN NUMBER,
' O_RESULT_SET OUT CURSOR_TYPE);
' END;
' /
' CREATE OR REPLACE
' PACKAGE BODY DEPARTMENT AS
' PROCEDURE GET_EMPS (I_DEPTNO IN NUMBER,
' O_RESULT_SET OUT CURSOR_TYPE)
' AS
' BEGIN
' OPEN O_RESULT_SET FOR
' SELECT EMPNO, ENAME
' FROM EMP
' WHERE DEPTNO = I_DEPTNO;
' END;
' END;
' /

a.Why will a standalone procedure not work for procedures returning result sets using ref cursors.

b.I want to execute the procedure above and print the results on te screen using sqlplus is that not possible , why so . That was possible using a function , why is it not possible using a procedure like above.

April 24, 2002 - 4:27 pm UTC

a) they will. they do. In fact the very first example on the referenced page is STANDALONE. this example WISELY chose not to. standalone procedures and functions should be banned in real systems. they are good for demos.

b) re-read the link. the first example on that page is:

REM SQL*Plus commands to use a cursor variable

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Result Sets

the link was helpful but

More to Explore

PL/SQL demos

PL/SQL docs

Using SELECT statement in stored procedures -- returning result sets from stored procedures.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Result Sets

the link was helpful but

More to Explore

PL/SQL demos

PL/SQL docs