Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Answered: neither waits for resource in forall statement, nor raises error!!!

    Hi,

    I am using Oracle Database 11g Release 11.2.0.4.0 - 64bit Production.
    I want to raise an error in scenario:

    In session 1 I update a row and do not commit.
    In session 2 I run procedure where cursor selects the same row and updates it.


    However in session 2 it neither updates the row, nor it raises an exception of deadlock.

    In procedure in session 2, I have used forall update.
    When I use simple for loop, it waits till i commit session 1 and then updates. I NEED this to be IMPLEMENTED in case of FORALL statement, as we use FORALL statement for insert and update in our project as a general rule.




    Can you please help me in doing this... ?


    My code using for loop :

    create or replace procedure Proc_Ptemp is

    Cursor c1 is
    select * from ptemp;
    m_SqlErrm VARCHAR2(150);
    begin
    for v1 in c1 LOOP
    UPDATE ptemp SET d=add_MonthS(d,1);
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    m_SqlErrm:=SUBSTR(SQLERRM,1,200);
    end Proc_Ptemp;



    My code using forall loop:

    create or replace procedure Proc_Ptemp_forall is

    Cursor c1 is
    select * from ptemp;
    arr_msg STR_ARRAY;
    arr_d DATE_ARRAY;
    m_SqlErrm VARCHAR2(150);
    Err_Cnt NUMBER;
    m_Err_index VARCHAR2(150);
    m_Err_Code VARCHAR2(150);
    begin
    FEtCH C1 BULK COLLECT INTO
    arr_msg,arr_d;
    BEGIN
    FORALL I IN arr_msg.FIRST..arr_msg.LAST SAVE EXCEPTIONS
    UPDATE ptemp SET d=add_MonthS(d,1)
    WHERE msg=arr_msg(i);
    EXCEPTION
    WHEN OTHERS THEN
    Err_Cnt:=SQL%BULK_EXCEPTIONS.COUNT;
    FOR j IN 1..Err_Cnt LOOP
    m_Err_index:=SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
    m_Err_Code:= SQL%BULK_EXCEPTIONS(j).ERROR_CODE;
    DBMS_OUTPUT.PUT_LINE(m_Err_Code);
    END LOOP;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    m_SqlErrm:=SUBSTR(SQLERRM,1,200);
    end Proc_Ptemp_forall;


    Table I update:
    select * from ptemp for update
    MSG D
    1 11/1/2015
    2 11/2/2015
    3 11/3/2015
    4 11/4/2015
    5 11/5/2015
    6 11/6/2015
    7 11/7/2015

    Please help me in solving this

    Thanks and regards
    P J S

  2. Best Answer
    Posted by Littlefoot

    "Well, session 2 could
    Code:
    lock table <table_name> in exclusive mode nowait;
    and get
    Code:
    ORA-00054: resource busy and acquire with NOWAIT specified
    if it is locked by session 1.

    Note that I'm not saying that you *should* do it, but *could* do it."


  3. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I dont think you get a solution for this. Process1 hasn't committed yet, so the row it is inserting, technically, doesn't exist yet. So process2 can't see it or do any updates to it.
    Dave

  4. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, session 2 could
    Code:
    lock table <table_name> in exclusive mode nowait;
    and get
    Code:
    ORA-00054: resource busy and acquire with NOWAIT specified
    if it is locked by session 1.

    Note that I'm not saying that you *should* do it, but *could* do it.

  5. #4
    Join Date
    Jul 2013
    Posts
    24
    Thanks.

    I will try it.

    Thanks again for your help.

Posting Permissions

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