Re: Converting Sybase SP to Oracle procedure

Tim Slattery wrote:
> 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?

You are making a very common and fatal mistake ... thinking you can treat Oracle like another brand of whatever product you know. This strategy will lead to substantial use of colorful language but never to success. So start by reading the concept books at http://tahiti.oracle.com and understand how Oracle works. Oracle is not an outgrowth of the UC Berkeley Ingress project and thus has a very different way of doing things.

Another resource that will help you is Morgan's Library at www.psoug.org.

There you will find working demos of the following that will help you:

ANONYMOUS BLOCKS
STORED PROCEDURES
REF CURSORS And review them in this order.

But until you understand why, in Oracle, reads don't block writes and writes don't block reads, system change numbers, and MVCC you should not even consider working on your procedure. The code you provided contains two fatal errors and at least one other that should be of concern.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org