Converting Sybase SP to Oracle procedure

From: Tim Slattery <Slattery_T_at_bls.gov>
Date: Wed, 13 Aug 2008 10:47:40 -0400
Message-ID: <ims5a4hhm4u9eqhdnkj6uqu8edd5sfdbjc@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
Received on Wed Aug 13 2008 - 09:47:40 CDT

Original text of this message