October 30, 2009 at 3:35 pm
Hi friends,
I'm here to see if you anyone of can provide me with some help on migrating a few sql server stored procedures to Oracle? May be some good conversion tools that you can suggest, will be great...
Thank you so much
October 30, 2009 at 4:25 pm
I've personally converted hundres of SQL Server storedprocs to Oracle.
Don't waste your time, rewrite them from scratch.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 31, 2009 at 8:08 am
Thanks Paul..
I'm trying to start with a simple conversion.. Here are 2 small blocks from the procedure that I'm converting..
CREATE PROC [dbo].[Wrd](@BID int, @Bidno int, @ref_no decimal(3,0)) as
Declare @FRMETH decimal(4,0)
SET @FRMETH = 0
Declare @BILLCODE decimal(1,0)
SET @BILLCODE = 0
Declare @MCKEY decimal(9,0)
SET @MCKEY = 0
Declare @REGION nvarchar(4)
SET @REGION = ' '
Declare @SCODE_TO nvarchar(10)
SET @SCODE_TO = ' '
Declare..... /* lot other declarations */
begin try
Select
@FRMETH=FRMETH,
@BILLCODE=BILLCODE,
@MCKEY=MCKEY,
@REGION=REGION,
from SR1..BEW.COTCT where SCODE= @SCODE_TO
END TRY
/* coding continues */
TO Oracle:
CREATE OR REPLACE PROCEDURE WRD(BID number(9), Bidno number(9), ref_no number(3,0)) as
declare
FRMETH number(4,0) :=0;
BILLCODE number(1,0) :=0;
MCKEY number(9,0) :=0;
REGION varchar2(4) := ' ';
SCODE_TO varchar2(10) :=' ';
/* lot other declarations */
begin
Select FRMETH,BILLCODE,MCKEY,REGION from
BEW.COTCT where SCODE= @SCODE_TO
END TRY
/* coding continues */
Please help me out if I need to change something...
Thank you so much
November 4, 2009 at 9:44 am
newbie00001 (10/31/2009)
Thanks Paul..I'm trying to start with a simple conversion.. Here are 2 small blocks from the procedure that I'm converting..
CREATE PROC [dbo].[Wrd](@BID int, @Bidno int, @ref_no decimal(3,0)) as
Declare @FRMETH decimal(4,0)
SET @FRMETH = 0
Declare @BILLCODE decimal(1,0)
SET @BILLCODE = 0
Declare @MCKEY decimal(9,0)
SET @MCKEY = 0
Declare @REGION nvarchar(4)
SET @REGION = ' '
Declare @SCODE_TO nvarchar(10)
SET @SCODE_TO = ' '
Declare..... /* lot other declarations */
begin try
Select
@FRMETH=FRMETH,
@BILLCODE=BILLCODE,
@MCKEY=MCKEY,
@REGION=REGION,
from SR1..BEW.COTCT where SCODE= @SCODE_TO
END TRY
/* coding continues */
TO Oracle:
CREATE OR REPLACE PROCEDURE WRD(BID number(9), Bidno number(9), ref_no number(3,0)) as
declare
FRMETH number(4,0) :=0;
BILLCODE number(1,0) :=0;
MCKEY number(9,0) :=0;
REGION varchar2(4) := ' ';
SCODE_TO varchar2(10) :=' ';
/* lot other declarations */
begin
Select FRMETH,BILLCODE,MCKEY,REGION from
BEW.COTCT where SCODE= @SCODE_TO
END TRY
/* coding continues */
Please help me out if I need to change something...
Thank you so much
What are you trying to acheive by this sp. Firstly, the SQL Server procedure is wrong. BEGIN TRY ... END TRY must be followed by BEGIN CATCH ... END CATCH
How can it be converted to Oracle.
Secondly, what are input variables @BID int, @Bidno int, @ref_no doing in the stored procedure.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
November 4, 2009 at 10:34 am
You do realize that he was not posting the entire proc, right?
November 6, 2009 at 10:08 am
in oracle you reference variables using : not @
Hope this helps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply