Link to home
Start Free TrialLog in
Avatar of mona_saxena27
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.PKID_;
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, SQLERRM,true);
            END;
            BEGIN
            StoO_error   := 0;
            StoO_rowcnt  := 0;
            DELETE FROM sa.CLIENTCONTACTCUSTOMFIELDSVALUE
                  WHERE InstanceSpecificId  IN (
            SELECT  pkid
                   FROM sa.ClientTeammembers
                  WHERE clientid = SP_ADMIN_DELETE_CLIENT.pkid_              );
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, SQLERRM,true);
            END;
/*Delete from CompanyCustomFieldsLookUp where InstanceSpecificId = @PKID

Delete from CompanyEmployeeContactCustomFieldsLookUp 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.PKID_;
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, SQLERRM,true);
            END;
            BEGIN
            StoO_error   := 0;
            StoO_rowcnt  := 0;
            DELETE FROM sa.ClientTeammembers
                  WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKID_;
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, SQLERRM,true);
            END;
            BEGIN
            StoO_error   := 0;
            StoO_rowcnt  := 0;
            DELETE FROM sa.ClientLocations
                  WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKID_;
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, SQLERRM,true);
            END;
            BEGIN
            StoO_error   := 0;
            StoO_rowcnt  := 0;
            DELETE FROM sa.Clients
                  WHERE
                  (PKID = SP_ADMIN_DELETE_CLIENT.PKID_);
            StoO_rowcnt := SQL%ROWCOUNT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                        NULL;
                  WHEN OTHERS THEN
                        StoO_error := SQLCODE;
                        StoO_errmsg := SQLERRM;
                        raise_application_error(SQLCODE, 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
Avatar of catchmeifuwant
catchmeifuwant

whats the significance of set nocount off?
ASKER CERTIFIED SOLUTION
Avatar of catchmeifuwant
catchmeifuwant

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial