mona_saxena27
asked on
Issue while migrating from SQL server to Oracle database
I have migrated SQL server database to Oracle database using Oracle Migration Workbench.
There is an sp in SQL server with the statement "Set nocount off"
The sp in Oracle after migration in oracle has the following code
(
PKID IN NUMBER DEFAULT NULL)
AS
PKID_ NUMBER(10,0) := PKID;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
NULL;
/*[SPCONV-ERR(5)]:(set nocount) Manual conversion required*/
BEGIN
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientCustomFieldvalues
WHERE InstanceSpecificId = SP_ADMIN_DELETE_CLIENT.PKI D_;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.CLIENTCONTACTCUSTOMFIEL DSVALUE
WHERE InstanceSpecificId IN (
SELECT pkid
FROM sa.ClientTeammembers
WHERE clientid = SP_ADMIN_DELETE_CLIENT.pki d_ );
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
/*Delete from CompanyCustomFieldsLookUp where InstanceSpecificId = @PKID
Delete from CompanyEmployeeContactCust omFieldsLo okUp where InstanceSpecificId in (select pkid from CompanyEmployees where companyId = @pkid)*/
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientContacts
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI D_;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientTeammembers
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI D_;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientLocations
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI D_;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.Clients
WHERE
(PKID = SP_ADMIN_DELETE_CLIENT.PKI D_);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ LCODE, SQLERRM,true);
END;
END;
END SP_ADMIN_DELETE_CLIENT;
Is there any manual conversion required for this SP to get corrected, though its compiling successfully and also deleting the records the same way deletion is taking place in SQL server SP
There is an sp in SQL server with the statement "Set nocount off"
The sp in Oracle after migration in oracle has the following code
(
PKID IN NUMBER DEFAULT NULL)
AS
PKID_ NUMBER(10,0) := PKID;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
NULL;
/*[SPCONV-ERR(5)]:(set nocount) Manual conversion required*/
BEGIN
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientCustomFieldvalues
WHERE InstanceSpecificId = SP_ADMIN_DELETE_CLIENT.PKI
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.CLIENTCONTACTCUSTOMFIEL
WHERE InstanceSpecificId IN (
SELECT pkid
FROM sa.ClientTeammembers
WHERE clientid = SP_ADMIN_DELETE_CLIENT.pki
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
/*Delete from CompanyCustomFieldsLookUp where InstanceSpecificId = @PKID
Delete from CompanyEmployeeContactCust
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientContacts
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientTeammembers
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.ClientLocations
WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKI
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
DELETE FROM sa.Clients
WHERE
(PKID = SP_ADMIN_DELETE_CLIENT.PKI
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQ
END;
END;
END SP_ADMIN_DELETE_CLIENT;
Is there any manual conversion required for this SP to get corrected, though its compiling successfully and also deleting the records the same way deletion is taking place in SQL server SP
whats the significance of set nocount off?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.