Re: Oracle Stored Procedures

Carlos:
  I don't know about multiple rows of data, but Oracle stored procedures can return a single, full row of table data. The OUT parameter can be of type %ROWTYPE or a function can return a value of type %ROWTYPE.

  Here's an example:

        /* This routine returns a PRD row.  If there was an error or the
        product is not found, p_o_err will be non-zero and PRD in the
        returned row will be NULL. */

        FUNCTION lcl_read_prd (p_prd IN prd.prd%TYPE,
                               p_o_err OUT NUMBER,
                               p_o_msg OUT VARCHAR2,
                               p_sql_err OUT NUMBER,
                               p_sql_msg OUT VARCHAR2) RETURN prd%

            v_prd_rec                       prd%ROWTYPE;

        BEGIN  /* lcl_read_prd */
            p_o_err := 0;
            p_o_msg := NULL;
            p_sql_err := 0;
            p_sql_msg := NULL;

            SELECT * INTO v_prd_rec FROM prd WHERE prd = p_prd;
            RETURN v_prd_rec;

        EXCEPTION  /* lcl_read_prd */
            WHEN OTHERS THEN
                v_prd_rec.prd := NULL;
                p_o_err := -1;
                p_o_msg := 'Error in lcl_read_prd.';
                p_sql_err := SQLCODE;
                p_sql_msg := SQLERRM;
                RETURN v_prd_rec;
        END;  /* lcl_read_prd */

In article <8tkind$20l$1_at_nnrp1.deja.com>,   Carlos Pereyra <cpereyra_at_pacbell.net> wrote:
> I have searched high and low for the answer to this question.
>
> Can Oracle stored procedures return a result set?
> To clarify: In MS SQL Server and Sybase, you can write a stored
> procedure that behaves just like a standard query returning a record
> set to the executor.
> The Oracle ODBC help documentation references a way to accomplish the
> same result by using ref cursors (?) They include a sample C program
> that shows how to do it. I have tried to emulate the functionality of
> the C program using perl but with no success.
> We have dozens of SQL Server stored procs that do select statements
> that we will need to migrate to Oracle when we switch.
> Any help appreciated.
>
> Carlos.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 21:21:58 CET