• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

oralc sql - is it correct?

can you see if the attached script is sound.  I have no sytax errors.
But some one mentioned I have too many begin and end.  A script should only have one begin and end.  I am not sure whtat the norm is. I was just trying to handle the exception errors correctly.
Please see if it ok or it can be improved?
Thank you
Anil
IM653872-ChangeBranch---Revised.sql
0
Anil_Lad
Asked:
Anil_Lad
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>> A script should only have one begin and end

If someone said that, they are incorrect.  As far as to many:  We cannot answer that.

You should have a begin and end for each sesiont that needs it's own exception handler.

On a quick scan, I would agree that you do have to many but I also don't know your requirement.


Ulness you have a requiremtn to do different things at different times with different exceptions, I would probably go with something like:

--loop

BEGIN
IF r_rec.new_branchid <> r_rec.broker_branch
         THEN
             -- first update
ELSE
    other update
end if;

exception
...
END;

EXCEPTION ... -- global exception handler

--rest of code
0
 
Anil_LadAuthor Commented:
can u fix the code so that it will reflect what you have suggested?  thx
0
 
slightwv (䄆 Netminder) Commented:
I can only take a 'guess' at what I would do because I don't have your setup to actually test anything and I don't fully understand your exact requirements.

Hopefully you will get the idea about where I am going from the untested code below.

The only thing you might need to tweak is the final update of alad.change_branch.  IT is outside the final exception handler.  It might throw an error if that fails but if it fails, there is no way to trap it since it would just be another update of the same table.

I'm also not seeing the need for the -4 error and the -5 error.  If the call to                simdata.pck_branch_conversion.run_one_policy_conversion fails, the revised code doesn't actually execute anything so I don't see the need for the -5 code.

Let me know if I am way off base here and I'll see what I need to tweak.

-- -------------------------------------------------------
-- Requested by:    Anil Lad
----------------------------------------------------------
-- 
-- Database Name:   SMXP 
--
-- SCHEMA NAME:     SIMDATA

-- Please run as simdata

-- Incident Items:   IM653872
--
-- policies:        102
--
-- Temp table       alad.change_branch  
-- 
-- Application:     PMS
-- 
-- Date:            Aug 20, 2012
-- 
-- Objective:       Change Policy branch to new branch as 
--                  shown in alad.changebranch table
--
-- Note:
--          Prior to running this script  ensure 
--           1) Data is manually loaded from excel sheet to change_branch table.
--           2) Other required fields are populated e.g new branch id, broker branch id
--           3) Ensure correct Reference_ID is used in this script as it may change
--              for every run.
--  
--          REFERENCE_ID:  IM653872
--                         This is required for each run.
--                         Change for each run.
--          COMPANYID:     G
--                         Default value is 'G'. change if req'd
-- -------------------------------------------------------

ALTER SESSION SET nls_territory = america;
ALTER SESSION SET nls_language = american;

DECLARE
               V_SQLCODE  VARCHAR2(100);
               V_SQLERRM  VARCHAR2(1000);
	v_status number;
	v_comments varchar2(4000);
BEGIN

   FOR r_rec IN (SELECT *
                   FROM alad.change_branch
                  WHERE status = 0 AND  reference_id = 'IM653872')
   LOOP
      BEGIN

         --********************************************************
         --1) Validation before the Branch Conversion
         --
         --     Ensure new branch id = Broker branch id
         --********************************************************
         IF r_rec.new_branchid <> r_rec.broker_branch
         THEN
            -- Report failed status         
		v_status := -1;
                v_comments = 'Broker Branch mismatch';
          ELSE
            -- ok to do conversion
            BEGIN
            --********************************************************
            --* 2) RUN Main routine to do Branch Conversion
            --********************************************************
               simdata.pck_branch_conversion.run_one_policy_conversion
                                                          (r_rec.policyid,
                                                           r_rec.companyid,
                                                           r_rec.new_branchid
                                                          );

            --Update our local table and report success status=1 with date stamp.
            v_status := 1;
                  
            EXCEPTION
               WHEN OTHERS
               THEN
               	-- Report failed status
                  V_SQLCODE:= SQLCODE;
                  V_SQLERRM:= SQLERRM;
		v_status := -4;
                v_comments := SUBSTR (   'ERROR!:'
                                    || V_SQLCODE
                                    || ' -ERROR- '
                                    || V_SQLERRM,
                                    1,
                                    99
                                   );
            END;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
                 V2_SQLCODE:= SQLCODE;
                 V2_SQLERRM:= SQLERRM;
		v_status := -5;
                v_comments := SUBSTR (   'ERROR!:'
                                    || V2_SQLCODE
                                    || ' -ERROR- '
                                    || V2_SQLERRM,
                                    1,
                                    99
                                   );
         END;


         UPDATE alad.change_branch
              SET status = v_status,
                  status_date = sysdate,
                  comments = v_comments
                   WHERE policyid = r_rec.policyid
                     AND reference_id = r_rec.reference_id;


   END LOOP;
END;
/

Open in new window

0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now