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

    Unanswered: update using collection

    I need to update a table over a link and I'm using a pl/sql collection for this.

    Amt_list is a pl/sql collection

    CREATE OR REPLACE TYPE AMT_TYPE AS OBJECT (
    INTRS_EXPENSE_AMT NUMBER,
    period NUMBER
    )

    CREATE OR REPLACE TYPE AMT_LIST as table of AMT_TYPE;

    When I update the table over the dblink, it says compiled but its not getting updated. Am I missing something here?
    Can anyone please guide me through this.

    PROCEDURE update_int_exp
    (
    acctg_prd IN NUMBER,
    amt_tab IN OUT NOCOPY amt_list
    )
    IS

    BEGIN


    IF amt_tab.count>0 THEN
    FOR i in amt_tab.first..amt_tab.last
    loop

    UPDATE int_exp_table@db_link a
    SET a.cap_intrs_amt = amt_tab(i).intrs_expense_amt
    ,a.insrt_ts = SYSDATE
    ,a.insrt_user = 'ABC'
    ,a.rec_updt_ts = SYSDATE
    ,a.rec_updt_user = 'ABC'
    WHERE a.period = amt_tab(i).period ;
    END LOOP;
    COMMIT;
    END IF;

    END;

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    When I update the table over the dblink, it says compiled but its not getting updated. Am I missing something here?
    You showed the procedure code. You did not show the procedure call (if you did it).
    From your post it seems that you do not know the difference between creating the procedure and executing the procedure. The first only creates the procedure on the server. The second really runs it.

    If you executed it though, there might be these reasons for not updating the rows:
    - the collection you passed to the procedure might have been empty
    - there might not be any matching row in the updated table

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I compiled and then executed it. I checked after execution and the table was not updated.

    - the collection you passed to the procedure might have been empty
    There is data in the collection what I passed- 6 records.
    - there might not be any matching row in the updated table
    I'm expecting to see one row updated for 200603.

    This is how I execute the procedure below-
    declare

    v_amt_tab amt_list := amt_list();

    begin
    PKG_INTRS_EXPNS_AMT.p_sum_amt
    (
    amt_tab => v_amt_tab,
    acctg_prd => 200603
    );

    pkg_intrs_expns_tt.update_int_exp
    (
    amt_tab => v_amt_tab,
    acctg_prd => 200603
    );
    end;
    Please find below the results after executing it.

    Executing--> p_sum_amt
    200603 184788.734505

    update complete
    I printed UPDATE COMPLETE after the COMMIT in the loop.
    When I select from int_exp_table@db_link, it shows the same rows as before.

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    Now it gets updated. My mistake, I defined the collection as expns_amt, period but when I was bulk collecting it into the amt_tab in PKG_INTRS_EXPNS_AMT.p_sum_amt i used a reverse order and hence it was not recognising it. As I declared both as Number type it didnt throw an error when compiling PKG_INTRS_EXPNS_AMT.p_sum_amt.

    Thanks,
    Nandinir

Posting Permissions

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