6 Replies Latest reply: Oct 23, 2012 3:51 AM by Billy~Verreynne RSS

    PLSQL program Total Records= 20569  from starting to 8822 records are proce

    Ramu
      HI,
      I have a problem, with my plsql code. some of records are processed, not all.Total Records= 20569 from starting to 8822 records are processing correctly , reaming 11747 not processed it's going end exception block. Could you please help me any body.
      create or replace procedure sal_info 
      is
       ln_total_records NUMBER := 0;
       ln_process_count NUMBER := 0;
       ln_error_count   NUMBER := 0;
            ln_skip_count                  NUMBER   := 0; 
           l_message                      VARCHAR2 (2500);
                 lb_inv_next_sal_date_warning   BOOLEAN;
           lb_proposed_salary_warning     BOOLEAN;
           lb_approved_warning            BOOLEAN;
           lb_payroll_warning             BOOLEAN;
              ln_proposed_salary_n            per_pay_proposals.proposed_salary_n%TYPE := '6666';
          sql_stmt                       VARCHAR2 (2500);
      
      ----cursor Salary Information
       CURSOR csr_sal_info
       IS
       select pay_proposal_id
            ,object_version_number
            ,proposed_salary_n
            ,date_to
             ,rowid
      from per_pay_proposals --where assignment_id=124
      order by pay_proposal_id;
      BEGIN
      
        -- v_csr_prson_info := NULL;
       /* Drop and Create  Temp Table "XXXX_DATA_MASK_T" in Run Time */
       --
        BEGIN
        --drop temp table XXXX_DATA_MASK_T
        EXECUTE IMMEDIATE 'drop table XXXX_DATA_MASK_T';
        --create temp Table XXXX_DATA_MASK_T
        EXECUTE IMMEDIATE 'Create  table XXXX_DATA_MASK_T(pk_column varchar2(200)
                                                         ,pk_column_id      NUMBER(25)
                                                         ,update_column1 varchar2(200)
                                                         ,update_column2 varchar2(200)
                                                         ,message varchar2(2500))';
        dbms_output.put_line('Temp Table "XXXX_DATA_MASK_T" is created  ');
        EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('Temp Table is does not exists ');
        EXECUTE IMMEDIATE 'Create  table XXXX_DATA_MASK_T(pk_column varchar2(200)
                                                         ,pk_column_id      NUMBER(25)
                                                         ,update_column1 varchar2(200)
                                                         ,update_column2 varchar2(200)
                                                         ,message varchar2(2500))';
        dbms_output.put_line(' So Temp Table "XXXX_DATA_MASK_T" is created in Exception Block ');
        END;  
       --
      ---updateing Employee Salary Information
      
      
       FOR v_csr_sal_info IN csr_sal_info
       LOOP
         ln_total_records := ln_total_records + 1;
          if v_csr_sal_info.pay_proposal_id is not null
        then 
        BEGIN 
                      l_message := NULL;
                         -- Update Employee Salary Information API
                         -- ---------------------------------
                    hr_maintain_proposal_api.cre_or_upd_salary_proposal
                       ( p_validate                   => FALSE
                     ,p_pay_proposal_id            => v_csr_sal_info.pay_proposal_id
                     ,p_object_version_number      => v_csr_sal_info.object_version_number
                        ,p_proposed_salary_n          => ln_proposed_salary_n
                        ,p_date_to                        => v_csr_sal_info.date_to
                        ,p_inv_next_sal_date_warning  => lb_inv_next_sal_date_warning
                     ,p_proposed_salary_warning      => lb_proposed_salary_warning
                     ,p_approved_warning              => lb_approved_warning
                     ,p_payroll_warning              => lb_payroll_warning
                    );
                       commit;
                        ln_process_count := ln_process_count +1;
            EXCEPTION
                      WHEN OTHERS THEN
                         l_message := SUBSTR (SQLCODE || ' - ' || SQLERRM, 1, 2500);      
                       sql_stmt := 'INSERT INTO XXXX_DATA_MASK_T (pk_column,pk_column_id,update_column1,update_column2,message) VALUES (:1,:2,:3,:4)';
                      EXECUTE IMMEDIATE sql_stmt USING 'SALARY', v_csr_sal_info.pay_proposal_id,'hr_maintain_proposal_api.cre_or_upd_salary_proposal Throwing Error is :',ln_total_records,l_message;  
                      ln_error_count := ln_error_count + 1;     
          commit;
        END;
           ELSE
                 sql_stmt :='INSERT INTO XXXX_DATA_MASK_T (pk_column,pk_column_id,message) VALUES (:1,:2,:3)';
                  EXECUTE IMMEDIATE sql_stmt USING 'SALARY', v_csr_sal_info.pay_proposal_id,'pay_proposal_id does not Exists';   
                  ln_skip_count := ln_skip_count + 1;               
                END IF; 
           END LOOP;     
       
      
        COMMIT;
         dbms_output.put_line (' XXXX Employee Salary Information Masked Total Records is : ' || ln_total_records|| '  and  Total Records Processed is :  '||ln_process_count);
           --- error records log       
             IF ln_error_count <> 0 THEN
               dbms_output.put_line (ln_error_count||' Records  " hr_maintain_proposal_api.cre_or_upd_salary_proposal" API through error for Employee Salary information is not Update Please Check Temp table 
                "SELECT * FROM XXXX_DATA_MASK_T WHERE pk_column = ''SALARY''  ');
             END IF;     
              
              ---- skip records log      
               IF ln_skip_count <> 0 THEN
               dbms_output.put_line (ln_skip_count||' Records skiped becuse this employee pay_proposal_id is not exists in per_pay_proposals table , so it''s not possible to data masking Employee salary information  please check temp tale 
                "SELECT * FROM XXXX_DATA_MASK_T WHERE pk_column = ''SALARY'' and message =''pay_proposal_id does not Exists''  ');
             END IF;          
             ---- 
       EXCEPTION
        WHEN OTHERS THEN
        -- dbms_output.put_line ('XXXX Employee National Identifier Number and Data of Birth Data Masked  is not processed');
          dbms_output.put_line (ln_error_count||' Records  " exception hr_maintain_proposal_api.cre_or_upd_salary_proposal" API through error for Employee Salary information is not Update Please Check Temp table 
                "SELECT * FROM XXXX_DATA_MASK_T WHERE pk_column = ''SALARY''  ');
       END;
      {code}
      thanks,
      Ramu.
      
      Edited by: Ramu on Oct 15, 2012 12:35 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
        • 1. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
          sb92075
          delete, remove, & eliminate all EXCEPTION code
          For reason why check these links.

          http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

          http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

          http://tkyte.blogspot.com/2007/03/challenge.html
          • 2. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
            6363
            Ramu wrote:
            reaming 11747 not processed it's going end exception block. Could you please help me any body.
            You might be able to find out why if you were not hiding all the error messages you are getting.

            You should never use when others

            http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
            http://tkyte.blogspot.com/2006/08/ouch-that-hurts.html
            http://tkyte.blogspot.com/2008/06/when-others-then-null-redux.html
            http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
            http://tkyte.blogspot.com/2012/05/pokemon-and-when-others.html

            You should never create tables in PL/SQL code.

            You should never, ever combine the two and create tables in a when others exception section.

            Whatever it is you think you are doing, stop, delete all of this code, start again, do not use when others, do not use execute immediate to perform DDL in PL/SQL
            • 3. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
              rp0428
              >
              I have a problem, with my plsql code. some of records are processed, not all.Total Records= 20569 from starting to 8822 records are processing correctly , reaming 11747 not processed it's going end exception block. Could you please help me any body.
              >
              And what do you want from us?

              Do you want us to guess which of your several exception blocks you are getting to?

              Do you want us to guess what the actual exception was that you hid by using your own exception code?

              Do you want us to guess what those procedures actually do that you call?

              When Oracle raises an exception it transfers control to an exception block. When control enters that block the ERRCODE and ERRMSG are availble to tell you what the error actuall is. The stack tract is also available to help with debugging.

              You haven't provided ANY of that information.

              So the only help we can give is to provide the exception information that Oracle gives you and what exception block you are getting it in.
              • 4. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
                Billy~Verreynne
                Ramu wrote:

                I have a problem, with my plsql code.
                The core problem is dynamically creating tables in PL/SQL application code. That is just silly.

                Solution?

                Don't. Model the business requirements and entities correctly using a proper normalised relational data model.
                • 5. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
                  Ramu
                  Hi Billy Verreynne ,

                  your correct, only problem is dynamically creating tables in PL/SQL application code. when i was remove that create table. it's working fine.
                  i was created table other script, then insert table using normal statement, then it's working fine.

                  Thank a lot.

                  Regards,
                  Ramu
                  • 6. Re: PLSQL program Total Records= 20569  from starting to 8822 records are proce
                    Billy~Verreynne
                    I suggest you look at using something like Subversion - with a source branch that has a directory for logical database scripts (creating the data model's tables and so on), and a separate directory for procedural code (creating the application code/stored procs).

                    Writing an install script is then quite easy - allowing you to run a single script to create the database model (tables) and install the application (code).