Solved

exception handling in stored procedure..............................

Posted on 2006-07-05
5
727 Views
Last Modified: 2008-02-01
hai,
     I tried to get exception handling as per requirement given below but was not able to compile .Can anyone please correct it for me.Thanks




 Create a stored procedure that will read the datstagcont table and populate contract price group product history(apphist) table with following approach
o        Identify prod_num by querying prodid table using product id and id type
o        Identify cont_num  from cont table using contract id
o        Identify cpgrp_num  using price group id
o        Check and ensure that the row with the same product and price group does not exist
o        Check to see that dates are within contract start and end date
o        Ensure that the product exists in ctprod.  If not create it with appropriate dates
For all errors assign new error numbers starting with 40000.  I just sent out scripts to create error description rows in errcd table.  Modify those with the ones you will need and load them in the errcd table.
--  For all exception handling use scc_error_handling procedure.  Also part of the scripts I just sent out.
--   For every SELECT INTO you must capture  the following
                 --  when no_data_found
         --  when too_many_rows
                 --  when others
-- In case of an error, you should not be inserting the row in cpphist, move to the next row.  For that you should make use of error flag.



CREATE OR REPLACE PROCEDURE scc_create_cpphist IS


CURSOR ic_datstagcont is
   SELECT line
   FROM DATSTAGCONT;
   
ll_prod_num   NUMBER(22);
ll_cont_num  NUMBER(22);
ll_ctprod_num NUMBER(22);
ll_cpphist_num NUMBER(22);
ll_cpgrp_num   NUMBER(22);
ls_prodid      VARCHAR2(20);
ls_cont_id      VARCHAR2(20);
ls_cpgrp_id     VARCHAR2(20);
ls_line       VARCHAR2(200);
BEGIN

  OPEN ic_datstagcont;
    LOOP
     FETCH ic_datstagcont INTO ls_line;
      EXIT WHEN ic_datstagcont%NOTFOUND;
   
        --Read from parse table for parse info
        --Get contid, prodid, PG ID
            
            select substr(ls_line,1,16) into ls_contid from ls_line;
            select substr(ls_line,27,10) into ls_proid from ls_line;
            select substr(ls_line,53,10) into ls_pgid from ls_line;
       
        --  convert prodid into prod_num
          BEGIN
            SELECT  prod_num
            INTO    ll_prod_num
            FROM prodid
            WHERE prodid_identifier = ls_prodid  
            AND  piden_id = 'NDC-11';
             
          EXCEPTION
              WHEN NO_DATA_FOUND  THEN
              ll_errorflg:='Y';
            LL_ERRORCD_NUM:=40000;
              WHEN T00_MANY_ROWS then
              ll_errorflg:='Y';
            LL_ERRORCD_NUM:=40000;
            WHEN  OTHERS THEN
              ll_errorflg:='Y';
            LL_ERRORCD_NUM:=40000;
            
            
            
          END;
      IF ls_errorflg = 'Y' THEN
      SCC_ERROR_HANDLING(
         ll_errorcd_num,      
         ll_line_no,
              ls_line,
           ls_formattyp,             
           ls_recordtyp,          
           ls_trantyp,
           ls_tickler_flg);
      end if;
         
      -- convert cont id into cont_num
            BEGIN
            SELECT  cont_num
            INTO    ll_cont_num
            FROM cont
            WHERE cont_internal_id = ls_cont_id;
           
            EXCEPTION
              WHEN NO_DATA_FOUND OR TOO_MANY_ROWS OR OTHERS THEN
              ll_errorflg:='Y';
            ll_errorcd_num:=40001;
                       
          END;
      IF ls_errorflg = 'Y' THEN
      SCC_ERROR_HANDLING(
         ll_errorcd_num,      
         ll_line_no,
              ls_line,
           ls_formattyp,             
           ls_recordtyp,          
           ls_trantyp,
           ls_tickler_flg );
      end if;
     
      -- convert PG id into cpgrp_num
            BEGIN
            SELECT  cpgrp_num
            INTO    ll_cpgrp_num
            FROM cpgrp
            WHERE  cont_cpgrp_id_alias = ls_cpgrp_id;
           
            EXCEPTION
              WHEN NO_DATA_FOUND OR TOO_MANY_ROWS OR OTHERS THEN
              ll_errorflg:='Y';
            ll_errorcd_num:=40002;
            
            
          END;
      IF ls_errorflg = 'Y' THEN
      SCC_ERROR_HANDLING(
         ll_errorcd_num,      
         ll_line_no,
              ls_line,
           ls_formattyp,             
           ls_recordtyp,          
           ls_trantyp,
           ls_tickler_flg);
     
      end if;
       -- Get ctprod_num
       BEGIN
           SELECT ctprod_num
           INTO  ll_ctprod_num
           FROM ctprod
           WHERE  prod_num = ll_prod_num
           AND   cont_num = ll_cont_num;
           
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
            
               
             
              --use ctprod_seq for generating the PK
                  
                         
                      INSERT INTO CTPROD (linenum,ctprod_num,prod_num,cont_num)
                         values(&ct_prod_seq.nextval,&ll_ctprod_num,&ll_prod_num,&ll_cont_num);
                                     
             
             END;
             --Check for dups in cpphist
        --IF NO dup exist
         -- DUP criteria  = prod_num, cont_num, cpgrp_num
             begin
                    
                            select count(1) into cnt from cpphist where prod_num = ll_prod_num and cont_num =ll_cont_num and cpgrp_num =ll_cpgrp_num;

                                      if cnt = 0 then
                                            insert into cpphist (pk,prod_num,cont_num,cpgrp_num) values (cpphist_seq.nextval,ll_prod_num,ll_cont_num,ll_cpgrp_num);
                                    end if;

             end;

             
     
    END LOOP;
  CLOSE ic_datstagcont;




END;

0
Comment
Question by:mullagururameshnaidu
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17045301
Waht is the error your getting.

Type show error after you compile. Should show the error messages
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17045330
I think your problem is with

            EXCEPTION
              WHEN NO_DATA_FOUND OR TOO_MANY_ROWS OR OTHERS THEN

should be

            EXCEPTION
              WHEN OTHERS THEN

or

            EXCEPTION
              WHEN NO_DATA_FOUND  THEN
             // code logic
              WHEN TOO_MANY_ROWS THEN
            //Code
            WHEN OTHER THEN
            // Code

Same with  
             
0
 

Author Comment

by:mullagururameshnaidu
ID: 17045366
SELECT INTO you must capture  the following
                 --  when no_data_found
         --  when too_many_rows
                 --  when others
  For that you should make use of error flag
The above is the requirement and i tried to do this as above.But its giving errors.Can u please go through it and make the required changes.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17045431
Try this

CREATE OR REPLACE PROCEDURE scc_create_cpphist IS


CURSOR ic_datstagcont is
   SELECT line
   FROM DATSTAGCONT;
   
ll_prod_num   NUMBER(22);
ll_cont_num  NUMBER(22);
ll_ctprod_num NUMBER(22);
ll_cpphist_num NUMBER(22);
ll_cpgrp_num   NUMBER(22);
ls_prodid      VARCHAR2(20);
ls_cont_id      VARCHAR2(20);
ls_cpgrp_id     VARCHAR2(20);
ls_line       VARCHAR2(200);
ll_errorflg   VARCHAR2(1);
LL_ERRORCD_NUM NUMBER;
BEGIN
      -- You don't need to do this in a loop I guess
      BEGIN
                select substr(ls_line,1,16) into ls_contid from ls_line;
                select substr(ls_line,27,10) into ls_proid from ls_line;
                select substr(ls_line,53,10) into ls_pgid from ls_line;
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
       // Error handling logic
       END;

  OPEN ic_datstagcont;
    LOOP
     FETCH ic_datstagcont INTO ls_line;
      EXIT WHEN ic_datstagcont%NOTFOUND;
   
   
        --  convert prodid into prod_num
          BEGIN
            SELECT  prod_num
            INTO    ll_prod_num
            FROM prodid
            WHERE prodid_identifier = ls_prodid  
            AND  piden_id = 'NDC-11';
             
          EXCEPTION
              WHEN NO_DATA_FOUND  THEN
              ll_errorflg:='Y';
              LL_ERRORCD_NUM:=40000;
              WHEN T00_MANY_ROWS then
              ll_errorflg:='Y';
              LL_ERRORCD_NUM:=40000;
              WHEN  OTHERS THEN
              ll_errorflg:='Y';
              LL_ERRORCD_NUM:=40000;
          END;
         
      IF ll_errorflg = 'Y' THEN
      ll_errorflg := NULL;
      SCC_ERROR_HANDLING(
        ll_errorcd_num,    
        ll_line_no,
             ls_line,
           ls_formattyp,            
           ls_recordtyp,        
           ls_trantyp,
           ls_tickler_flg);
       ll_errorflg := NULL;    
      end if;
         
     -- convert cont id into cont_num
            BEGIN
            SELECT  cont_num
            INTO    ll_cont_num
            FROM cont
            WHERE cont_internal_id = ls_cont_id;
           
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
              ll_errorflg:='Y';
              ll_errorcd_num:=40001;
              WHEN TOO_MANY_ROWS THEN
              ll_errorflg:='Y';
            ll_errorcd_num:=40001;
            WHEN OTHERS THEN
            ll_errorflg:='Y';
            ll_errorcd_num:=40001;
             
          END;
      IF ll_errorflg = 'Y' THEN
      ll_errorflg := NULL;    
      SCC_ERROR_HANDLING(
        ll_errorcd_num,    
        ll_line_no,
             ls_line,
           ls_formattyp,            
           ls_recordtyp,        
           ls_trantyp,
           ls_tickler_flg );
      end if;
     
      -- convert PG id into cpgrp_num
            BEGIN
            SELECT  cpgrp_num
            INTO    ll_cpgrp_num
            FROM cpgrp
            WHERE  cont_cpgrp_id_alias = ls_cpgrp_id;
           
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
              ll_errorflg:='Y';
              ll_errorcd_num:=40002;
              WHEN TOO_MANY_ROWS THEN
              ll_errorflg:='Y';
            ll_errorcd_num:=40002;
            WHEN OTHERS THEN
            ll_errorflg:='Y';
            ll_errorcd_num:=40002;
           
           
           
          END;
      IF ll_errorflg = 'Y' THEN
      ll_errorflg := NULL;    
      SCC_ERROR_HANDLING(
        ll_errorcd_num,    
        ll_line_no,
             ls_line,
           ls_formattyp,            
           ls_recordtyp,        
           ls_trantyp,
           ls_tickler_flg);
     
      end if;
       -- Get ctprod_num
       BEGIN
           SELECT ctprod_num
           INTO  ll_ctprod_num
           FROM ctprod
           WHERE  prod_num = ll_prod_num
           AND   cont_num = ll_cont_num;
           
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 
            --use ctprod_seq for generating the PK
                         
                    INSERT INTO CTPROD (linenum,ctprod_num,prod_num,cont_num)
                     values(&ct_prod_seq.nextval,&ll_ctprod_num,&ll_prod_num,&ll_cont_num);
                         
           
           END;
            --Check for dups in cpphist
        --IF NO dup exist
         -- DUP criteria  = prod_num, cont_num, cpgrp_num
           begin
                 
                        select count(1) into cnt from cpphist where prod_num = ll_prod_num and cont_num =ll_cont_num and cpgrp_num =ll_cpgrp_num;

                                if cnt = 0 then
                                     insert into cpphist (pk,prod_num,cont_num,cpgrp_num) values (cpphist_seq.nextval,ll_prod_num,ll_cont_num,ll_cpgrp_num);
                              end if;

           end;

             
     
    END LOOP;
  CLOSE ic_datstagcont;


0
 

Author Comment

by:mullagururameshnaidu
ID: 17062363
Hai,
      Iam getting problem with the exception .MY doubts are
1.Do we need  to give ll_errorcd_num =40002 same for all the conditions i.e NO_DATA_FOUND,TOO_MANY_ROWS,OTHERS
2.When T))_MANY_ROWS then how can we pass those values into scc_error_handling.
EXCEPTION
              WHEN NO_DATA_FOUND THEN
              ll_errorflg:='Y';
              ll_errorcd_num:=40002;
              WHEN TOO_MANY_ROWS THEN
              ll_errorflg:='Y';
           ll_errorcd_num:=40002;
           WHEN OTHERS THEN
           ll_errorflg:='Y';
           ll_errorcd_num:=40002;
           
           
           
          END;
      IF ll_errorflg = 'Y' THEN
      ll_errorflg := NULL;    
      SCC_ERROR_HANDLING(
        ll_errorcd_num,    
        ll_line_no,
             ls_line,
           ls_formattyp,            
           ls_recordtyp,        
           ls_trantyp,
           ls_tickler_flg);
     
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now