Solved

Issue while migrating from SQL server to Oracle database

Posted on 2003-11-10
5
684 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:mona_saxena27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9720630
whats the significance of set nocount off?
0
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 25 total points
ID: 9720642
Well on googling,I found the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_3ed0.asp

This is just displaying the numbers of rows affected.

Oracle,does not display these results in a SP.However if you want to know how many rows were affected for every delete in the sp...you can do...After this statement in your SP ->

StoO_rowcnt := SQL%ROWCOUNT;
dbms_output.put_line('Rows Deleted:'||StoO_rowcnt);

To turn on this Server output option,in your session you need to execute the command :

set serveroutput on


HTH

0
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 25 total points
ID: 9721886
That Migration Workbench seems to create a lot of unnecessary code!  The procedure above can be simplified to this:

     BEGIN
          DELETE FROM sa.ClientCustomFieldvalues
               WHERE InstanceSpecificId = SP_ADMIN_DELETE_CLIENT.PKID_;
          StoO_rowcnt := SQL%ROWCOUNT;
          DELETE FROM sa.CLIENTCONTACTCUSTOMFIELDSVALUE
               WHERE InstanceSpecificId  IN (
          SELECT  pkid
                FROM sa.ClientTeammembers
               WHERE clientid = SP_ADMIN_DELETE_CLIENT.pkid_            );
          StoO_rowcnt := SQL%ROWCOUNT;
          DELETE FROM sa.ClientContacts
               WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKID_;
          StoO_rowcnt := SQL%ROWCOUNT;
          DELETE FROM sa.ClientTeammembers
               WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKID_;
          StoO_rowcnt := SQL%ROWCOUNT;
          DELETE FROM sa.ClientLocations
               WHERE ClientId = SP_ADMIN_DELETE_CLIENT.PKID_;
          StoO_rowcnt := SQL%ROWCOUNT;
          DELETE FROM sa.Clients
               WHERE
               (PKID = SP_ADMIN_DELETE_CLIENT.PKID_);
          StoO_rowcnt := SQL%ROWCOUNT;
     END;

Quite why it was handling the NO_DATA_FOUND exception for DELETE statements that will never raise it baffles me.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question