Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Unanswered: locking object problem

    Hi,

    I'm new developing for oracle, and I find myself trapped with this error:

    ORA-04021: timeout occurred while waiting to lock object DE.NRMVERSIONNKB
    ORA-06512: at line 18

    The code of the script I'm running is this:

    DECLARE
    funcname CHAR(15);
    BEGIN
    EXECUTE IMMEDIATE 'UPDATE NPublic SET Format=''.url.txt'' WHERE Format=''.url''';
    EXECUTE IMMEDIATE 'UPDATE NStates SET Format=''.url.txt'' WHERE Format=''.url''';
    SELECT OBJECT_NAME INTO funcname FROM USER_OBJECTS WHERE OBJECT_NAME = 'NRMVERSIONNKB';
    IF SQL%FOUND THEN
    IF nrmVersionNKB <= 33751044 THEN
    EXECUTE IMMEDIATE 'ALTER TRIGGER public_Update_Parent_Stt DISABLE';
    EXECUTE IMMEDIATE 'ALTER TRIGGER Public_Update_Child_Stt DISABLE';
    UPDATE NPublic SET Parent=Child,Child=Parent WHERE Class='2E666C7700000'
    UPDATE NStates SET Parent=Child,Child=Parent WHERE Class='2E666C7700000'
    EXECUTE IMMEDIATE 'ALTER TRIGGER Public_Update_Parent_Stt ENABLE';
    EXECUTE IMMEDIATE 'ALTER TRIGGER Public_Update_Child_Stt ENABLE';
    END IF;
    END IF;
    NIndex.nrmPublicIndex;
    EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION nrmVersionNKB RETURN INT IS BEGIN RETURN 33751044; END;';
    END;

    I don't know what's going worng with this script...it "hangs" for a while and after that the error message appears.
    I would really appreciate your help,

    thanks

    Federico
    Last edited by fmilano; 09-02-04 at 17:14.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /
    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
    Apr 2004
    Posts
    246
    1st - why are the first 2 updates using execute immediate? They don't need to be dynamic - just use regular updates.

    2nd - it's not "hanging" - it's working, and then waiting for a lock which it can't get.

    3rd - unless you pasted wrong, the "create or replace function" is invalid. It's missing the semicolon after the return.

    4th - the reason your pl/sql can't lock nrmVersionNKB (create/replace needs to lock it, so it can replace the function without anyone accessing it) is because the very same pl/sql has executed nrmVersionNKB just a few lines above.

    5th - rethink your process. recreating a function after executing is seems wrong.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26
    I've just found what was happening; if I separate the create or replace stuff in another block, it works fine.

    Thanks a lot anyways!

    F.

Posting Permissions

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