Re: Converting Sybase SP to Oracle procedure

"Tim Slattery" <Slattery_T_at_bls.gov> wrote in message news:ims5a4hhm4u9eqhdnkj6uqu8edd5sfdbjc_at_4ax.com...
> I'm converting a Sybase database to Oracle. Part of this involves
> stored procedures. I have a number of very simple Sybase SPs that
> consist of a single select statement:
>
> CREATE PROCEDURE dbo.getContactData
> @resp char(9)
>
> as
> begin
>
> select Respondent, Contact_Name, Contact_Company_Name_1,
> Contact_Company_Name_2, Contact_Address, Contact_City,
> Contact_State, Contact_Zip, Contact_Zip_Extension,
> Contact_EMail,
> Contact_Telephone, Contact_Telephone_Suffix, Contact_Fax
> from registry
> where respondent = @resp
> end
>
> This simply returns a number of rows to the caller.
>
> But when I convert it to this Oracle procedure:
>
> create or replace procedure getContactData
> (p_resp char)
>
> IS
> BEGIN
>
> select Respondent, Contact_Name, Contact_Company_Name_1,
> Contact_Company_Name_2, Contact_Address, Contact_City,
> Contact_State, Contact_Zip, Contact_Zip_Extension,
> Contact_EMail,
> Contact_Telephone, Contact_Telephone_Suffix, Contact_Fax
> from registry
> where respondent = p_resp;
> END;
> /
>
>
> it tells me that I need an "into" clause on the "select" statement.
> But I don't want to capture the data within the procedure, I want to
> return it to the caller.
>
> What do I need to do?
>
> --
> Tim Slattery
> Slattery_T_at_bls.gov
> http://members.cox.net/slatteryt

You can return a ref cursor to the caller as an out parameter. See ref cursor in the docs. Received on Wed Aug 13 2008 - 10:48:34 CDT