Home » SQL & PL/SQL » SQL & PL/SQL » Performing TYPE 2 operation using Merge in 10g (Oracle 10g)
Performing TYPE 2 operation using Merge in 10g [message #447102] Thu, 11 March 2010 22:26 Go to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi ,

I have a type to table where only few columns are SCD 2. Like address, city, zip.....

I wrote a code using cursor.

But now the requirment is to replace the cursor .

So i need advice how to perform type 2 using Merge in oracle 10g or any better way to perform it with out using cursor.


Look the code which i have attached with cursor.


Thanks in advance.

CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
  l_fun_ret_value            NUMBER;
  v_src_id                   NUMBER;
  rec_jci_contract_dim       jci_contract_dim%ROWTYPE;
  rec_jci_contract_dim_hist  jci_contract_dim_hist%ROWTYPE;
  err_row                    VARCHAR2(4000);
  ind                        NUMBER(1) := 1;
  /***************************************************************************************************************************

* PROGRAM NAME           : PROC_JCI_CONTRACT_DIM_HIST

* AUTHOR                       : ARUN KUMAR

* CREATION DATE           : 09-FEB-2010

* DESCRIPTION               : STORED PROCEDURE TO LOAD DATA TO CONTRACT HISTORY TABLE FROM THE CONTRACT DIMENSION TABLE

* CALLING PROGRAM       : NONE

* CALLED PROGRAM        : NONE

* INPUT PARAMETERS     : NONE

* OUTPUT PARAMETERS   : NONE

* INPUTS FILE(S)            : NONE

* OUTPUT FILE(S)           : NONE

* MODIFICATIONS           : NONE

*        

*

***************************************************************************************************************************/
  CURSOR contract_dim_cur IS
    SELECT contract_st,
           branch_number,
           status,
           contract_st_type,
           sequence_number,
           Trunc(creation_date)      creation_date,
           created_by,
           Trunc(last_updated_date),
           last_updated_by,
           address_1,
           address_2,
           address_3,
           city,
           state,
           country,
           postal_code,
           attribute1,
           attribute2,
           attribute3,
           tech_reporting_700,
           report_generated_dt,
           final_flg,
           num_copies,
           notarized,
           federal_id,
           jci_sub_labor,
           mail_id,
           Trunc(project_start_date) project_start_date,
           state_flg,
           regenerate_flg,
           Trunc(contract_close_dt)  contract_close_dt,
           source_application_id,
           created_dt,
           created_user,
           modified_dt,
           modified_user
    FROM   jci_contract_dim;
  FUNCTION Fun_jci_contract_dim_hist
       (in_contract_st         IN jci_contract_dim.contract_st%TYPE,
        in_address_1           IN jci_contract_dim.address_1%TYPE,
        in_address_2           IN jci_contract_dim.address_2%TYPE,
        in_address_3           IN jci_contract_dim.address_3%TYPE,
        in_city                IN jci_contract_dim.city%TYPE,
        in_state               IN jci_contract_dim.state%TYPE,
        in_country             IN jci_contract_dim.country%TYPE,
        in_postal_code         IN jci_contract_dim.postal_code%TYPE,
        in_tech_reporting_700  IN jci_contract_dim.tech_reporting_700%TYPE,
        in_num_copies          IN jci_contract_dim.num_copies%TYPE,
        op_contract_key        OUT NUMBER)
  RETURN NUMBER
  IS
  BEGIN
    IF (ind = 1) THEN
      BEGIN
        Jci_cp_job_insert('PROC_CP_JCI_CONTRACT_DIM_HIST');
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      
      ind := 0;
    END IF;
    
    SELECT Nvl((SELECT source_application_id
                FROM   jci_source_dim
                WHERE  Upper(source_name) = 'JCI_CONTRACT_DIM'),
               -999)
    INTO   v_src_id
    FROM   dual;
    
    SELECT contract_key,
           contract_st,
           contract_name,
           project_start_dt,
           status,
           contract_close_dt,
           address_1,
           address_2,
           address_3,
           city,
           state,
           country,
           postal_code,
           tech_reporting_700,
           state_flg,
           num_copies,
           report_generated_dt,
           final_flg,
           sequence_number,
           regenerate_flg,
           created_dt,
           created_user,
           modified_dt,
           modified_user,
           branch_number,
           source_application_id,
           cp_current_flg
    INTO   rec_jci_contract_dim_hist
    FROM   jci_contract_dim_hist a
    WHERE  a.contract_st = in_contract_st
           AND cp_current_flg = 'Y';
    
    IF Nvl(Upper(in_address_1),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.address_1),
                                             'NULL')
       AND Nvl(Upper(in_address_2),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.address_2),
                                                 'NULL')
       AND Nvl(Upper(in_address_3),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.address_3),
                                                 'NULL')
       AND Nvl(Upper(in_city),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.city),'NULL')
       AND Nvl(Upper(in_state),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.state),'NULL')
       AND Nvl(Upper(in_country),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.country),
                                               'NULL')
       AND Nvl(Upper(in_postal_code),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.postal_code),
                                                   'NULL')
       AND Nvl(Upper(in_tech_reporting_700),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.tech_reporting_700),
                                                          'NULL')
       AND Nvl(Upper(in_num_copies),'NULL') = Nvl(Upper(rec_jci_contract_dim_hist.num_copies),
                                                  'NULL') THEN
      op_contract_key := -1;
      
      RETURN -1; -- update
    ELSE
      -- type2 columns have changed
      op_contract_key := rec_jci_contract_dim_hist.contract_key; --1 will be added while inserting
      
      UPDATE jci_contract_dim_hist b
      SET    b.cp_current_flg = 'N',
             b.modified_dt = SYSDATE,
             b.modified_user = USER
      WHERE  b.contract_st = in_contract_st
             AND Nvl(Upper(Trim(b.cp_current_flg)),'NA') = 'Y';
      
      COMMIT;
      
      RETURN 1;
    END IF;
  EXCEPTION
    WHEN no_data_found THEN
      Jci_cp_exception_track('PROC_CP_JCI_CONTRACT_DIM_HIST',SQLCODE,sqlerrm,
                             'KNOWN',err_row);
      
      op_contract_key := 0;
      
      RETURN 0; --new insert
  
  END fun_jci_contract_dim_hist;
BEGIN
  FOR rec_jci_contract_dim IN contract_dim_cur LOOP
    l_fun_ret_value := Fun_jci_contract_dim_hist(rec_jci_contract_dim.contract_st,rec_jci_contract_dim.address_1,
                                                 rec_jci_contract_dim.address_2,rec_jci_contract_dim.address_3,
                                                 rec_jci_contract_dim.city,rec_jci_contract_dim.state,
                                                 rec_jci_contract_dim.country,rec_jci_contract_dim.postal_code,
                                                 rec_jci_contract_dim.tech_reporting_700,rec_jci_contract_dim.num_copies,
                                                 rec_jci_contract_dim_hist.contract_key);
    
    IF l_fun_ret_value = 0 THEN
      --new insert
      INSERT INTO jci_contract_dim_hist
                 (contract_key,
                  contract_st,
                  contract_name,
                  project_start_dt,
                  status,
                  contract_close_dt,
                  address_1,
                  address_2,
                  address_3,
                  city,
                  state,
                  country,
                  postal_code,
                  tech_reporting_700,
                  state_flg,
                  num_copies,
                  report_generated_dt,
                  final_flg,
                  sequence_number,
                  regenerate_flg,
                  branch_number,
                  created_user,
                  created_dt,
                  modified_dt,
                  modified_user,
                  source_application_id,
                  cp_current_flg)
      VALUES     (jci_contract_dim_hist_seq.nextval,
                  rec_jci_contract_dim.contract_st,
                  rec_jci_contract_dim.attribute1,
                  rec_jci_contract_dim.project_start_date,
                  rec_jci_contract_dim.status,
                  rec_jci_contract_dim.contract_close_dt,
                  rec_jci_contract_dim.address_1,
                  rec_jci_contract_dim.address_2,
                  rec_jci_contract_dim.address_3,
                  rec_jci_contract_dim.city,
                  rec_jci_contract_dim.state,
                  rec_jci_contract_dim.country,
                  rec_jci_contract_dim.postal_code,
                  rec_jci_contract_dim.tech_reporting_700,
                  rec_jci_contract_dim.state_flg,
                  rec_jci_contract_dim.num_copies,
                  NULL,
                  rec_jci_contract_dim.final_flg,
                  rec_jci_contract_dim.sequence_number,
                  rec_jci_contract_dim.regenerate_flg,
                  rec_jci_contract_dim.branch_number,
                  USER,
                  SYSDATE,
                  NULL,
                  NULL,
                  v_src_id,
                  'Y');
    ELSIF l_fun_ret_value = -1 THEN
      --update
      UPDATE jci_contract_dim_hist
      SET    contract_st = rec_jci_contract_dim.contract_st,
             contract_name = rec_jci_contract_dim.attribute1,
             project_start_dt = rec_jci_contract_dim.project_start_date,
             status = rec_jci_contract_dim.status,
             contract_close_dt = rec_jci_contract_dim.contract_close_dt,
             address_1 = rec_jci_contract_dim.address_1,
             address_2 = rec_jci_contract_dim.address_2,
             address_3 = rec_jci_contract_dim.address_3,
             city = rec_jci_contract_dim.city,
             state = rec_jci_contract_dim.state,
             country = rec_jci_contract_dim.country,
             postal_code = rec_jci_contract_dim.postal_code,
             tech_reporting_700 = rec_jci_contract_dim.tech_reporting_700,
             state_flg = rec_jci_contract_dim.state_flg,
             num_copies = rec_jci_contract_dim.num_copies,
             report_generated_dt = NULL,
             final_flg = rec_jci_contract_dim.final_flg,
             sequence_number = rec_jci_contract_dim.sequence_number,
             regenerate_flg = rec_jci_contract_dim.regenerate_flg,
             branch_number = rec_jci_contract_dim.branch_number,
             source_application_id = v_src_id,
             modified_dt = SYSDATE,
             modified_user = USER
      WHERE  contract_key = rec_jci_contract_dim_hist.contract_key
             AND Upper(Trim(cp_current_flg)) = 'Y';
    ELSIF l_fun_ret_value = 1 THEN
      --type2 insert
      INSERT INTO jci_contract_dim_hist
                 (contract_key,
                  contract_st,
                  contract_name,
                  project_start_dt,
                  status,
                  contract_close_dt,
                  address_1,
                  address_2,
                  address_3,
                  city,
                  state,
                  country,
                  postal_code,
                  tech_reporting_700,
                  state_flg,
                  num_copies,
                  report_generated_dt,
                  final_flg,
                  sequence_number,
                  regenerate_flg,
                  branch_number,
                  created_user,
                  created_dt,
                  modified_dt,
                  modified_user,
                  source_application_id,
                  cp_current_flg)
      VALUES     (jci_contract_dim_hist_seq.nextval,
                  rec_jci_contract_dim.contract_st,
                  rec_jci_contract_dim.attribute1,
                  rec_jci_contract_dim.project_start_date,
                  rec_jci_contract_dim.status,
                  rec_jci_contract_dim.contract_close_dt,
                  rec_jci_contract_dim.address_1,
                  rec_jci_contract_dim.address_2,
                  rec_jci_contract_dim.address_3,
                  rec_jci_contract_dim.city,
                  rec_jci_contract_dim.state,
                  rec_jci_contract_dim.country,
                  rec_jci_contract_dim.postal_code,
                  rec_jci_contract_dim.tech_reporting_700,
                  rec_jci_contract_dim.state_flg,
                  rec_jci_contract_dim.num_copies,
                  NULL,
                  rec_jci_contract_dim.final_flg,
                  rec_jci_contract_dim.sequence_number,
                  rec_jci_contract_dim.regenerate_flg,
                  rec_jci_contract_dim.branch_number,
                  USER,
                  SYSDATE,
                  NULL,
                  NULL,
                  v_src_id,
                  'Y');
    END IF;
  END LOOP;
  
  COMMIT;
  
  Jci_cp_job_track('PROC_CP_JCI_CONTRACT_DIM_HIST','SUCCESS',
                   0,0);
  
  EXECUTE IMMEDIATE 'ANALYZE TABLE JCI_CONTRACT_DIM_HIST ESTIMATE STATISTICS ';
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    
    Jci_cp_exception_track('PROC_CP_JCI_CONTRACT_DIM_HIST',SQLCODE,sqlerrm,
                           'UNKNOWN');
    
    Jci_cp_job_track('PROC_CP_JCI_CONTRACT_DIM_HIST','FAILED',
                     0,0);
END proc_cp_jci_contract_dim_hist; 


CODE in tags added inline by BlackSwan

[Updated on: Thu, 11 March 2010 22:44] by Moderator

Report message to a moderator

Re: Performing TYPE 2 operation using Merge in 10g [message #447204 is a reply to message #447102] Fri, 12 March 2010 05:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For a Type 2 SCD, you need to be able to make a single input both update an existing row AND insert a new row. MERGE cannot do that.

The best you could do would be to write a SELECT that emitted rows twice - once that could be used to perform the update and once for the insert - and plug that SELECT into the MERGE. That would be hard to understand though.

Why can't you use a cursor? Is it because cursors are supposedly slow? If so, that is a falsehood. Cursors are not slow - it's what you do inside cursor loops that is slow.

If you are really serious about learning a useful technique rather than blindly following the orders of someone who doesn't understand PL/SQL (whoever it was that asked you not to use a cursor), then you should take a look at the sections in the PL/SQL manual on BULK COLLECT and FORALL. These techniques are discussed in the following article: http://www.orafaq.com/node/1399

Ross Leishman
Re: Performing TYPE 2 operation using Merge in 10g [message #447207 is a reply to message #447204] Fri, 12 March 2010 06:15 Go to previous message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Thanks rleishman

I will try your technique and revert back the status.


The thing about cursor ... you r wright. I will convince them to go with cursor.

Thanks again.
Previous Topic: Why we can not use the Oracle Procedure in an SQL Statement?
Next Topic: to left align the number fields
Goto Forum:
  


Current Time: Mon Sep 26 14:33:31 CDT 2016

Total time taken to generate the page: 0.10471 seconds