Re: How to create Stored Procedure to return rows

> I'm a newbie in Oracle and want to create a stored procedure to return rows
> just as "Select * from equipment".
>
> Could anyone kindly give me the sample script or for it?
>
> Do I need to use PL/SQL and PACKAGE?

Yes. A stored procedure will take an action but SELECT * from equipment is not one of them as a stored procedure can not directly write to the screen. The result of a stored procedure executing your example is that nothing would happen.

If you want to see the results you do need to use a PL/SQL package called DBMS_OUTPUT. And to see the output you would need to issue the command SET SERVEROUTPUT ON at the SQL> prompt in SQL*Plus before executing the package (one time for each login).

But in a stored procedure you would still not be able to directly do SELECT *. You would need to select into a variable of some type for DBMS_OUTPUT to display. For example

SELECT myfield
INTO myvariable
FROM equipment;

DBMS_OUTPUT.PUT_LINE(myvariable);

Daniel A. Morgan Received on Sun Mar 25 2001 - 09:58:53 CST