Converting PL/SQL to T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting PL/SQL to T-SQL

Author

Topic

red2003xlt
Starting Member

4 Posts

Posted - 2008-08-11 : 10:52:46

Hello,

I'm new to SQL Server but I have a MS Access background.
Unfortunelty, I was never that much into stored procedures.

Heres my problem.

I'm trying to convert a working piece of PL/SQL into T-SQL(?)

The Oracle developer created the procedure and it works in Oracle.

The the developer (apparently) ran it thru a conversion program (AdventNet SwisSQL (Oracle To SQL Server)).

I uploaded into SQL Server, and it runs.
Doesn't affect any rows and despite have differently tables name SQL Server doesn't see to flag them (as errors).

Can anybody care to share commonsense tips?
The non-flagging of table names, What???????????

Would it help to post the code?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:54:39

Yes, it would certainly help.

E 12°55'05.25"
N 56°04'39.16"

red2003xlt
Starting Member

4 Posts

Posted - 2008-08-11 : 11:19:10

Here is the Modified Procedure.

Should I've posted the Orginal Procedure?
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Test_Requirement_id] Script Date: 08/05/2008 18:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Test_Requirement_id]
AS

DECLARE @ls_curr_process_group VARCHAR(200)
SET @ls_curr_process_group = '''?'''

DECLARE @ln_group_row_number FLOAT
SET @ln_group_row_number = 0

DECLARE @ls_system_req1 VARCHAR(200)

DECLARE @ls_system_req2 VARCHAR(200)

DECLARE @ls_system_req3 VARCHAR(200)

DECLARE @ls_system_req4 VARCHAR(200)

DECLARE @ls_system_req5 VARCHAR(200)

DECLARE @ls_system_req6 VARCHAR(200)

DECLARE @ls_system_req7 VARCHAR(200)

DECLARE @ls_system_req8 VARCHAR(200)

DECLARE @ls_system_req9 VARCHAR(200)

DECLARE @ls_system_req10 VARCHAR(200)
declare @sys_req_1 varchar(5000)
declare @sys_req_2 varchar(5000)
declare @sys_req_3 varchar(5000)
declare @sys_req_4 varchar(5000)
declare @sys_req_5 varchar(5000)
declare @sys_req_6 varchar(5000)
declare @sys_req_7 varchar(5000)
declare @sys_req_8 varchar(5000)
declare @sys_req_9 varchar(5000)
declare @sys_req_10 varchar(5000)
declare @new_process_grp varchar(5000)
declare @PROCESS_GRP_ABV varchar(5000)

BEGIN

DECLARE cur_process_group CURSOR Global FOR
SELECT
SUBSTRING(process_grp_abv, 1, CASE dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 3)
WHEN 0 THEN LEN(PROCESS_GRP_ABV) + 1
ELSE dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 3)
END - 1) process_group_new,
a.PROCESS_GRP_ABV,
a.Sys_Req_1,
a.Sys_Req_2,
a.Sys_Req_3,
a.Sys_Req_4,
a.Sys_Req_5,
a.Sys_Req_6,
a.Sys_Req_7,
a.Sys_Req_8,
a.Sys_Req_9,
a.Sys_Req_10
FROM dbo_Process_SubProcess a (UPDLOCK)
WHERE 1 = 1
AND dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 2) > 0
AND (sys_req_1 IS NOT NULL
OR sys_req_2 IS NOT NULL
OR sys_req_3 IS NOT NULL
OR sys_req_4 IS NOT NULL
OR sys_req_5 IS NOT NULL
OR sys_req_6 IS NOT NULL
OR sys_req_7 IS NOT NULL
OR sys_req_8 IS NOT NULL
OR sys_req_9 IS NOT NULL
OR sys_req_10 IS NOT NULL)
ORDER BY process_grp_abv

OPEN cur_process_group

FETCH NEXT FROM cur_process_group INTO @new_process_grp, @PROCESS_GRP_ABV, @sys_req_1
, @sys_req_2
,@sys_req_3
, @sys_req_4
, @sys_req_5
, @sys_req_6
, @sys_req_7
, @sys_req_8
, @sys_req_9
, @sys_req_10
print @new_process_grp

WHILE (@@FETCH_STATUS <> -1)

BEGIN
IF @ls_curr_process_group != @new_process_grp -- /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ cur_process_group_rec.process_group_new
BEGIN
SELECT @ls_curr_process_group =@new_process_grp -- /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ cur_process_group_rec.process_group_new
SELECT @ln_group_row_number = 0
print 'Inside while loop'
print @ls_curr_process_group
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_1 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req1 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
print @ls_system_req1
END
ELSE
BEGIN
SELECT @ls_system_req1 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_2 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req2 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req2 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_3 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req3 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req3 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_4 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req4 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req4 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_5 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req5 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req5 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_6 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req6 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req6 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_7 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req7 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req7 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_8 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req8 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req8 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_9 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req9 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req9 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_10 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req10 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req10 = null
END

UPDATE dbo_Sys_Req_ids
SET sys_req_id_1 = @ls_system_req1,
sys_req_id_2 = @ls_system_req2,
sys_req_id_3 = @ls_system_req3,
sys_req_id_4 = @ls_system_req4,
sys_req_id_5 = @ls_system_req5,
sys_req_id_6 = @ls_system_req6,
sys_req_id_7 = @ls_system_req7,
sys_req_id_8 = @ls_system_req8,
sys_req_id_9 = @ls_system_req9,
sys_req_id_10 = @ls_system_req10,
Process_IP_Code = @PROCESS_GRP_ABV
-- WHERE CURRENT OF cur_process_group
--SELECT @adv_sql_rowcount = @@ROWCOUNT
--commit

FETCH NEXT FROM cur_process_group INTO @new_process_grp, @PROCESS_GRP_ABV, @sys_req_1
, @sys_req_2
,@sys_req_3
, @sys_req_4
, @sys_req_5
, @sys_req_6
, @sys_req_7
, @sys_req_8
, @sys_req_9
, @sys_req_10

END

CLOSE cur_process_group
DEALLOCATE cur_process_group

END

red2003xlt
Starting Member

4 Posts

Posted - 2008-08-11 : 12:13:49

Here the Orignal Procedure; If it helps.
* Formatted on 2008/07/25 08:18 (Formatter Plus v4.8.8) */
DECLARE
ls_curr_process_group VARCHAR2 (200) := '?';
ln_group_row_number NUMBER := 0;
ls_system_req1 VARCHAR2 (200);
ls_system_req2 VARCHAR2 (200);
ls_system_req3 VARCHAR2 (200);
ls_system_req4 VARCHAR2 (200);
ls_system_req5 VARCHAR2 (200);
ls_system_req6 VARCHAR2 (200);
ls_system_req7 VARCHAR2 (200);
ls_system_req8 VARCHAR2 (200);
ls_system_req9 VARCHAR2 (200);
ls_system_req10 VARCHAR2 (200);

CURSOR cur_process_group
IS
SELECT SUBSTR (process_grp_abv,
1,
DECODE(INSTR (process_grp_abv, '-', 1, 3), 0, LENGTH(PROCESS_GRP_ABV)+1, INSTR (process_grp_abv, '-', 1, 3)) - 1
) process_group_new,
a.*
FROM qry_b201 a
WHERE 1 = 1
AND INSTR (process_grp_abv, '-', 1, 2) > 0
AND ( sys_req_1 IS NOT NULL
OR sys_req_2 IS NOT NULL
OR sys_req_3 IS NOT NULL
OR sys_req_4 IS NOT NULL
OR sys_req_5 IS NOT NULL
OR sys_req_6 IS NOT NULL
OR sys_req_7 IS NOT NULL
OR sys_req_8 IS NOT NULL
OR sys_req_9 IS NOT NULL
OR sys_req_10 IS NOT NULL
)
ORDER BY process_grp_abv
FOR UPDATE OF sys_req_id_1;
BEGIN
FOR cur_process_group_rec IN cur_process_group
LOOP
IF ls_curr_process_group != cur_process_group_rec.process_group_new
THEN
ls_curr_process_group := cur_process_group_rec.process_group_new;
ln_group_row_number := 0;
END IF;

IF cur_process_group_rec.sys_req_1 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req1 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req1 := NULL;
END IF;

IF cur_process_group_rec.sys_req_2 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req2 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req2 := NULL;
END IF;

IF cur_process_group_rec.sys_req_3 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req3 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req3 := NULL;
END IF;

IF cur_process_group_rec.sys_req_4 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req4 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req4 := NULL;
END IF;

IF cur_process_group_rec.sys_req_5 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req5 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req5 := NULL;
END IF;

IF cur_process_group_rec.sys_req_6 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req6 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req6 := NULL;
END IF;

IF cur_process_group_rec.sys_req_7 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req7 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req7 := NULL;
END IF;

IF cur_process_group_rec.sys_req_8 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req8 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req8 := NULL;
END IF;

IF cur_process_group_rec.sys_req_9 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req9 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req9 := NULL;
END IF;

IF cur_process_group_rec.sys_req_10 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req10 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req10 := NULL;
END IF;

UPDATE qry_b201
SET sys_req_id_1 = ls_system_req1,
sys_req_id_2 = ls_system_req2,
sys_req_id_3 = ls_system_req3,
sys_req_id_4 = ls_system_req4,
sys_req_id_5 = ls_system_req5,
sys_req_id_6 = ls_system_req6,
sys_req_id_7 = ls_system_req7,
sys_req_id_8 = ls_system_req8,
sys_req_id_9 = ls_system_req9,
sys_req_id_10 = ls_system_req10
WHERE CURRENT OF cur_process_group;
END LOOP;

commit;
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources