DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: To find out the object(s) created by particular session_id

  1. #1
    Join Date
    Dec 2000
    Posts
    87

    Question

    Hi all,

    Does any body know how to find out all the objects created by particular session id in current session?

    This is what I'm trying to achieve. I would like to bypass a trigger (ie. stop the trigger to be fired) for the current session (ie. doesn't affect other session from using the trigger).

    Then if I find out a temp table is created. Then I would like the temp table to be deleted as soon as the session is ended.
    Anyone have any idea how a procedure like this can be written or is there a better way I can bypass a trigger?

    Thanks a lot!


  2. #2
    Join Date
    Dec 2000
    Posts
    87
    I'd like to provide more information on my question.
    I'm trying to bypass the trigger by checking if a temp table is created( or could be any other way, but the idea is if something is true, the trigger is bypassed), if so, then stop the trigger from firing, and at the same time delete the temp table since it is created only for this purpose.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The control whether the trigger will fire for a particular session is most elegantly achieved by using the supplied package DBMS_APPLICATION_INFO. The two procedures that are of interest for thios task are SET_CLIENT_INFO and READ_CLIENT_INFO.

    If you don't want the trigger to be fired for your session, set a special info string by using SET_CLIENT_INFO procedure. Now in your trigger check if this special infostring is set by using READ_CLIENT_INFO. If you find the particular string to be set, skip ower the trigger logic, othervise execute the trigger normaly.

    As far as the second part of your question is concerned, I don't understand exactly what you are tryin to achive, but maybe you should consider usin GLOBAL TEMPORARY TABLEs.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2000
    Posts
    87

    Unhappy

    Jmodic,

    It sounds like exactly what I need, but can you make an example of how it is used. What kind of parameter does it need to be passed in? Thanks.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It would be something like:

    CREATE OR REPLACE my_trigger BEFORE INSERT ON my_table FOR EACH ROW
    DECLARE
    msg_var VARCHAR2(2000);
    BEGIN
    DBMS_APPLICATION_INFO.READ_CLIENT_INFO(msg_var);
    IF msg_var = 'Skip my_trigger' THEN NULL
    ELSE
    <trigger logic>
    END IF;
    END;

    Now in your program, if you don't want the trigger to be fired, do something like:

    BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO( 'Skip my_trigger');
    INSERT INTO my_table VALUES(.....); -- Trigger won't be fired for this inserts
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
    INSERT INTO my_table VALUES(.....); -- Trigger will be fired for this inserts
    .....
    END;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Damn, I've forgot that this forum doesn't like some of the characters, so some of my code was filtered out. The trigger body should be (using leading "-" to make it more readable):

    CREATE OR REPLACE my_trigger BEFORE INSERT ON my_table FOR EACH ROW
    DECLARE
    --msg_var VARCHAR2(2000);
    BEGIN
    --DBMS_APPLICATION_INFO.READ_CLIENT_INFO(msg_var);
    --IF msg_var = 'Skip my_trigger' THEN
    ----NULL;
    --ELSE
    ---- ...trigger logic....
    --END IF;
    END;
    /
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2000
    Posts
    87
    Thanks jmodic. It's helpful, I'll definitely try it out.

Posting Permissions

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


Click Here to Expand Forum to Full Width