Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Procedure with cursors

    Hi All,

    I have this procedure where I'm trying to update the records in the target_tab with rows from src_tab. There are 200 records in src_tab for the specific period and 5000 records in the target tab. The procedure what I wrote is updating 5000*200 times (sql%rowcount) in the target table and with only the 200th record from the source tab. Cant figure out whats wrong here. Can any of you please help me figure this out.

    CREATE OR REPLACE PROCEDURE p_abc_hstry (
    period IN number
    )
    IS

    TYPE tbl_data IS RECORD (
    id DBMS_SQL.VARCHAR2_TABLE,
    cptl_amt DBMS_SQL.number_table,
    risk_amt DBMS_SQL.number_table,
    non_cr_risk_amt DBMS_SQL.number_table
    );

    l_a tbl_data;

    CURSOR cur_data
    IS
    SELECT id, cptl_amt,
    risk_amt, non_cr_risk_amt
    FROM src_tab WHERE prd_nbr = pi_prd;

    l_rdata cur_data%ROWTYPE;
    l_nfetchsize NUMBER := 50000;
    l_nsqlrowcount NUMBER := 0;

    BEGIN

    po_bexitcode := FALSE;

    OPEN cur_data;
    LOOP
    FETCH cur_data
    BULK COLLECT INTO l_a.id,
    l_a.cptl_amt,
    l_a.cr_risk_amt,l_a.non_cr_risk_amt
    LIMIT l_nfetchsize;

    FORALL i IN 1..l_a.id.count
    UPDATE /*+INDEX (target_tab tab_ix) */ target_tab
    SET economic_capital = l_a.cptl_amt(i),
    eco_cptl_cr_risk_amt = l_a.cr_risk_amt(i),
    eco_cptl_non_cr_risk_amt = l_a.non_cr_risk_amt(i),
    insrt_dt = sysdate
    WHERE period = period;

    l_nsqlrowcount := l_nsqlrowcount + SQL%ROWCOUNT;

    EXIT WHEN cur_data%NOTFOUND;

    COMMIT;

    END LOOP;

    CLOSE cur_data;
    COMMIT;

    dbms_output.put_line('Rows updated for period '||period||' are :: '|| l_nsqlrowcount);

    po_bexitcode := TRUE;
    RETURN;

    EXCEPTION
    WHEN OTHERS
    THEN RAISE;

    END p_abc_hstry;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have this procedure where I'm trying to update the records in the target_tab with rows from src_tab.
    >There are 200 records in src_tab for the specific period and 5000 records in the target tab.
    What is the criteria for deciding which row from SRC_TAB updates which row in TARGET_TAB?

    post DDL for both tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    The criteria would be based on ID and period.

    CREATE TABLE target_tab(ID varchar2(10),period number(6), economic_capital number(10,2),
    eco_cptl_cr_risk_amt number(10,2),eco_cptl_non_cr_risk_amt number(10,2),insrt_dt date);

    CREATE TABLE source_tab(ID varchar2(10),prd_nbr number(6), cptl_amt number(10,2), risk_amt number(10,2), non_cr_risk_amt number(10,2));

    INSERT INTO SOURCE_TAB
    (ID, PRD_NBR, CPTL_AMT, RISK_AMT, NON_CR_RISK_AMT)
    VALUES
    ('101', 201101, 800, 45.23, 33);
    INSERT INTO SOURCE_TAB
    (ID, PRD_NBR, CPTL_AMT, RISK_AMT, NON_CR_RISK_AMT)
    VALUES
    ('102', 201101, 600, 78.45, 66);
    INSERT INTO SOURCE_TAB
    (ID, PRD_NBR, CPTL_AMT, RISK_AMT, NON_CR_RISK_AMT)
    VALUES
    ('103', 201101, 500, 15.01, 89);
    INSERT INTO SOURCE_TAB
    (ID, PRD_NBR, CPTL_AMT, RISK_AMT, NON_CR_RISK_AMT)
    VALUES
    ('104', 201101, 100, 200, 300.12);
    COMMIT;

    SET DEFINE OFF;
    INSERT INTO TARGET_TAB
    (ID, PERIOD, ECONOMIC_CAPITAL, ECO_CPTL_CR_RISK_AMT, ECO_CPTL_NON_CR_RISK_AMT,
    INSRT_DT)
    VALUES
    ('101', 201101, 100.12, NULL, NULL,
    TO_DATE('02/24/2011 13:15:29', 'MM/DD/YYYY HH24:MIS'));
    INSERT INTO TARGET_TAB
    (ID, PERIOD, ECONOMIC_CAPITAL, ECO_CPTL_CR_RISK_AMT, ECO_CPTL_NON_CR_RISK_AMT,
    INSRT_DT)
    VALUES
    ('102', 201101, NULL, NULL, 45,
    TO_DATE('02/24/2011 13:15:26', 'MM/DD/YYYY HH24:MIS'));
    INSERT INTO TARGET_TAB
    (ID, PERIOD, ECONOMIC_CAPITAL, ECO_CPTL_CR_RISK_AMT, ECO_CPTL_NON_CR_RISK_AMT,
    INSRT_DT)
    VALUES
    ('103', 201101, NULL, NULL, NULL,
    TO_DATE('02/24/2011 13:15:24', 'MM/DD/YYYY HH24:MIS'));
    INSERT INTO TARGET_TAB
    (ID, PERIOD, ECONOMIC_CAPITAL, ECO_CPTL_CR_RISK_AMT, ECO_CPTL_NON_CR_RISK_AMT,
    INSRT_DT)
    VALUES
    ('104', 201101, 56, NULL, 89,
    TO_DATE('02/24/2011 13:15:20', 'MM/DD/YYYY HH24:MIS'));
    COMMIT;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so much for posting tested & functioning SQL
    SQL got mangled by forum.
    :-(
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    WHERE period = period
    Is that correct? What happens if you rename procedure's parameter to PAR_PERIOD and modify WHERE clause to
    Code:
    WHERE period = par_period
    ?

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    it is where period = pi_prd in the actual procedure. I was renaming the procedure and changed this parameter as well.

    CREATE OR REPLACE PROCEDURE p_abc_hstry (
    pi_prd IN number
    )
    IS
    .....
    ......
    UPDATE /*+INDEX (target_tab tab_ix) */ target_tab
    SET economic_capital = l_a.cptl_amt(i),
    eco_cptl_cr_risk_amt = l_a.cr_risk_amt(i),
    eco_cptl_non_cr_risk_amt = l_a.non_cr_risk_amt(i),
    insrt_dt = sysdate
    WHERE period = pi_prd;
    ..
    ..

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE target_tab tt
    SET    ( economic_capital, eco_cptl_cr_risk_amt, eco_cptl_non_cr_risk_amt, insrt_dt ) =
                  (SELECT cptl_amt,
                  risk_amt,
                  non_cr_risk_amt,
                  SYSDATE
                  FROM   source_tab st
                  WHERE  st.prd_nbr = tt.period
                         AND st.id = tt.id)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jul 2005
    Posts
    276
    Didnt realize, i forgot to add the ID in the where clause of the update statement. It works now.

    ..
    ..
    UPDATE /*+INDEX (target_tab tab_ix) */ target_tab
    SET economic_capital = l_a.cptl_amt(i),
    eco_cptl_cr_risk_amt = l_a.cr_risk_amt(i),
    eco_cptl_non_cr_risk_amt = l_a.non_cr_risk_amt(i),
    insrt_dt = sysdate
    WHERE period = period
    AND id = l_a.id (i);

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR replace PROCEDURE P_abc_hstry (period_arg IN NUMBER)
    IS
    BEGIN
      UPDATE target_tab tt
      SET    ( economic_capital, eco_cptl_cr_risk_amt, eco_cptl_non_cr_risk_amt,
               insrt_dt ) =
                    (SELECT cptl_amt,
                    risk_amt,
                    non_cr_risk_amt,
                    SYSDATE
                    FROM   src_tbl st
                    WHERE  st.period = tt.period
                           AND st.id = tt.id
                           AND period_arg = st.period);
    END p_abc_hstry;
    Last edited by anacedent; 02-24-11 at 15:10.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2005
    Posts
    276
    Thank you everone for your replies.

    I wanted to use Bulk collect cause I'm dealing with Millions of rows here. It will run approx 6 million records for every period.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I wanted to use Bulk collect cause I'm dealing with Millions of rows here. It will run approx 6 million records for every period.
    6 million is NOT big number.

    which is faster?
    I bet mine is!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jul 2005
    Posts
    276
    Unfortunately, i dont have the volume to test the performance in Dev.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Just export the two tables from Production & test away
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    I wont have such access to it as its Production data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •