Re: Stored Procedure Question?

On Sun, 08 Apr 2001, RVONHOLLEN_at_pacbell.net wrote:

> I find, with my limited knowledge in Oracle, that one would
> have to perform a Select Mycol into MyNewCol from Dual;. Is
> there any workaround on this to simply cause a select statement
> to be performed?

There is a workaround, but one cannot do a simple select and have a stored proc return rows.

A search on "procedure select" came up with this thread and therefore no typing involved. You can thank Daniel Morgan.

    > 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

-- 
I don't want to be the rock.  Yeah, okay, what do you want to be?
I want to be the piece of glass.