Converting Sybase SP to Oracle procedure

#1

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 (DOT) gov
http://members.cox.net/slatteryt

#2

"Tim Slattery" <Slattery_T (AT) bls (DOT) gov> wrote

Quote:

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 (DOT) 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.

#3

"Tim Slattery" <Slattery_T (AT) bls (DOT) gov> wrote

Quote:

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 (DOT) 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.

#4

"Tim Slattery" <Slattery_T (AT) bls (DOT) gov> wrote

Quote:

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 (DOT) 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.

#5

"Tim Slattery" <Slattery_T (AT) bls (DOT) gov> wrote

Quote:

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 (DOT) 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.

#6

Tim Slattery wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#7

Tim Slattery wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#8

Tim Slattery wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#9

Tim Slattery wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Converting Sybase SP to Oracle procedure

comp.databases.oracle.misc comp.databases.oracle.misc

Converting Sybase SP to Oracle procedure - 08-13-2008 , 09:47 AM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 10:48 AM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 10:48 AM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 10:48 AM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 10:48 AM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 03:10 PM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 03:10 PM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 03:10 PM

Re: Converting Sybase SP to Oracle procedure - 08-13-2008 , 03:10 PM