Cursor in Sql server - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor in Sql server

Author

Topic

bhushanhegde
Starting Member

14 Posts

Posted - 2008-11-27 : 05:38:03

Hi,
Once again I need to convert a Oracle procedure into sql server 2005.
My procedure has Curso and also xml codes.
Please help me to convert this procedure.
Procedure is as follows:

CREATE OR REPLACE PROCEDURE fm_save_profilemast(STRXML IN LONG,P_PMPK IN NUMBER,P_TYPE IN VARCHAR2, PKEY OUT NUMBER)
AS

xml XMLType;
v_pmpk NUMBER;
change_by VARCHAR2(20);
v2 NUMBER;
lngstr LONG;

CURSOR Cur_synt_dtls is
SELECT ldr_syntx_dtls_pkey
FROM fm_profile_cfile
WHERE sub_line_seq=0;
BEGIN

lngstr:= REPLACE(strxml,'\',''); -- removing '\' from xml file
XML:=XMLTYPE(lngstr);

IF P_TYPE='ADD' THEN -- IF ADDING NEW PROFILE
SELECT FM_PKEY_PROFILEMAST.NEXTVAL INTO V_PMPK FROM DUAL;
ELSIF P_TYPE='EDIT' THEN -- IF UPDATING EXISTED PROFILE
V_PMPK :=P_PMPK;
DELETE from fm_profile_cfile a
WHERE a.profilemaster_pkey=V_PMPK;

DELETE from fm_profiledet b
WHERE b.profilemaster_pkey=V_PMPK;

DELETE FROM fm_profilemast a
WHERE a.profilemaster_pkey=V_PMPK;

DELETE FROM fm_condition a
WHERE a.profilemaster_pkey=V_PMPK;
END IF;

SELECT extract(xml, 'ROOT/CHANGE/@CHANGEBY').getstringval()
INTO CHANGE_BY
FROM DUAL;

INSERT ALL
WHEN existsnode (xml, '/PROFILE') = 1 THEN INTO FM_PROFILEMAST --INSERTING INTO PROFILEMASTER
VALUES
(V_PMPK,
extract(xml, 'PROFILE/@DB_MSTR_PKEY').getnumberval(),
extract(xml, 'PROFILE/@PROFILE_NAME').getstringval(),
extract(xml, 'PROFILE/@FILE_TYPE').getstringval(),
extract(xml, 'PROFILE/@DELIMETER').getstringval(),
extract(xml, 'PROFILE/@PRE_PROCESSOR').getstringval(),
extract(xml, 'PROFILE/@POST_PROCESSOR').getstringval(),
extract(xml, 'PROFILE/@PRE_PROCESSOR_TYPE').getstringval(),
extract(xml, 'PROFILE/@POST_PROCESSOR_TYPE').getstringval(),
extract(xml, 'PROFILE/@GROUPID').getstringval(),
DEFAULT,
change_by,
DEFAULT,
DEFAULT,
extract(xml, 'PROFILE/@PROFILE_TYPE').getnumberval(),
extract(xml, 'PROFILE/@PROFILE_MODE').getstringval(),
extract(xml, 'PROFILE/@FILE_MODE').getstringval())

WHEN existsnode (xml, 'OPTIONS') = 1 THEN INTO fm_profile_cfile --INSERTING INTO PROFILE CFILE
VALUES
(v_pmpk,
extract(xml, '/OPTIONS/@LDR_SYNTX_DTLS_PKEY').getstringval(),
extract(xml, '/OPTIONS/@VAL').getstringval(),
change_by,
DEFAULT,
fm_pkey_opt_ldr.nextval,
0,
DEFAULT,
DEFAULT)

WHEN existsnode (xml, 'WHEN_CLAUSE') = 1 THEN INTO FM_CONDITION --INSERTING INTO CONDITION TABLE
VALUES
(V_PMPK,
extract(xml, '/WHEN_CLAUSE/@TARGET_SOURCE_TABLE').getstringval(),
--replace(extract(xml, '/WHEN_CLAUSE/@WHEN_CLAUSE').getstringval(),';',''''),
--replace(extract(xml, '/WHEN_CLAUSE/@WHEN_CLAUSE').getstringval(),''',''''),
extract(xml, '/WHEN_CLAUSE/@WHEN_CLAUSE').getstringval(),
DEFAULT,
change_by,
DEFAULT,
DEFAULT)

SELECT column_value AS xml FROM table
(SELECT xmlsequence(extract(xml, '/ROOT/*')) FROM dual);

-- To update special charecters into symbol

UPDATE FM_PROFILEMAST SET PRE_PROCESSOR=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PRE_PROCESSOR,'<','<'),'>','>'),'"','"'),'&','&'),''','''')
WHERE PROFILEMASTER_PKEY=V_PMPK;

UPDATE FM_PROFILEMAST SET POST_PROCESSOR=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(POST_PROCESSOR,'<','<'),'>','>'),'"','"'),'&','&'),''','''')
WHERE PROFILEMASTER_PKEY=V_PMPK;

UPDATE FM_CONDITION SET WHEN_CLAUSE=
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(WHEN_CLAUSE,'<','<'),'>','>'),'"','"'),'&','&'),''','''')
WHERE PROFILEMASTER_PKEY=V_PMPK;

UPDATE FM_PROFILE_CFILE SET VAL=
REPLACE(REPLACE(VAL,'"','"'),''','''')
WHERE PROFILEMASTER_PKEY=V_PMPK;

FOR v2 IN Cur_synt_dtls -- Opening Cursor
LOOP
UPDATE fm_profile_cfile -- Updates Sub_Line_Seq column fm_ldr_syntx_dtls
SET sub_line_seq=
(select sub_line_seq FROM fm_ldr_syntx_dtls WHERE syntx_dtls_pkey=v2.ldr_syntx_dtls_pkey)
WHERE sub_line_seq=0
AND ldr_syntx_dtls_pkey=v2.ldr_syntx_dtls_pkey ;
END LOOP; -- Closing the Cursor

PKEY:=V_PMPK; --Returning Pkey through OUT Parameter
COMMIT; -- Commiting all the changes

EXCEPTION
WHEN OTHERS THEN
ROLLBACK; --Rollback
SYS_DB_LOG_ERROR ('FM_SAVE_PROFILEMAST',P_PMPK || ';' || P_TYPE,SQLERRM);
RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END fm_save_profilemast;

thanks and regards
bhushan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 05:52:24

You want us convert your complete application for you?

E 12°55'05.63"
N 56°04'39.26"

bhushanhegde
Starting Member

14 Posts

Posted - 2008-11-27 : 05:54:53

Hi,
It's not like that.
I am new to sql server.
sorry for troubling you!!!!!

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-27 : 05:57:26

Looking at the code, i think the cursor is not needed and can be replaced by set based code. Why don't you join fm_ldr_syntx_dtls and fm_profile_cfile to get the desired values?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 05:58:33

First, get aquinted with handling XML in SQL Server.
Then read and learn about CURSORs.

Maybe you should hire an consultant to do the conversion and during the conversion the consultant can tell you what he is doing.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources