Solved

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

Posted on 2006-07-05
5
753 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
[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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

691 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