Re: Query in a Stored Procedure

From: Jeff <jeff_at_work.com>
Date: Thu, 30 Aug 2001 13:32:10 GMT
Message-ID: <9mlf8q$od0$2@cronkite.cc.uga.edu>

Here's a simple conversion to PL/SQL (adjust to your datatypes and needs): CREATE OR REPLACE FUNCTION qryGetDirections( JobNum IN VARCHAR2 ) RETURN VARCHAR2
AS
v_directions tblDirections.directions_desc%TYPE; BEGIN

        SELECT directions_desc
                INTO v_directions
                FROM tblDirections
                WHERE job = JobNum;
        RETURN v_directions;
EXCEPTION
        WHEN no_data_found
                RETURN NULL;

END; HTH In article <9mlchf$8qh$1_at_slb2.atl.mindspring.net>, "Roman Sochan" <rsochan_at_removeme.ix.netcom.com> wrote:
>I'm new to Oracle stored procedures (my experience is with MS SQL Server
>stored procedures) and I can't find a way to store a SELECT sql statement
>that will return the result of a query.
>
>In SQL Server, it's okay to have a stored procedure in the form:
>
>CREATE PROCEDURE qryGetDirections
>@JobNum as varchar(20)
>As
>SELECT job, directions_desc
>FROM tblDirections
>WHERE job = @JobNum
>
>When it is run, it simply returns a different result set based on the value
>of JobNum. In Oracle, a similar procedure would give me an error saying
>that I'm missing an INTO clause.
>
>How would I convert the above into PL/SQL and store it as a procedure, or am
>I not able to store queries in an Oracle stored procedure?
>
>
Received on Thu Aug 30 2001 - 08:32:10 CDT