Home » SQL & PL/SQL » SQL & PL/SQL » Should I Clean Up My Collections? (11g)
Should I Clean Up My Collections? [message #665007] Thu, 17 August 2017 10:19 Go to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
I'm using an associative array in a compound trigger.

Q: Do I need to somehow clean up the array at the end of the trigger or does the DBMS do that for me when it goes out of scope?

Thanks.

Wayne
Re: Should I Clean Up My Collections? [message #665008 is a reply to message #665007] Thu, 17 August 2017 10:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just check - put a dbms_output in at the start and see if there's anything there the 2nd time you do something.
Re: Should I Clean Up My Collections? [message #665009 is a reply to message #665008] Thu, 17 August 2017 10:39 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
Hmmmm. I'm not sure I can assume that the 2nd time around the trigger will assume the same memory locations rather than just creating them new. If new, I'm sucking up memory unless Oracle cleans up by default.

Or am I not understanding?

Wayne
Re: Should I Clean Up My Collections? [message #665010 is a reply to message #665007] Thu, 17 August 2017 11:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to understand object scope. Trigger, procedure, function are instantiated each time they are called. Package global variables are instantiated once per session (unless package is created with PRAGMA SERIALLY_REUSABLE). Anyway, data in collection created in compound trigger will persist through the duration of the statement, so depending on particular trigger logic you might need to delete from collection if collection data shouldn't be spanning across row level actions.

SY.

[Updated on: Thu, 17 August 2017 11:32]

Report message to a moderator

Re: Should I Clean Up My Collections? [message #665011 is a reply to message #665010] Thu, 17 August 2017 11:57 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
OK, thanks for clarifying.

In my compound trigger, I have a collection.DELETE at the beginning of the trigger execution (the collection is declared in the same trigger). Based on what you've said, it seems that this is unnecessary, since it can't inherit any data from a previous instance.

Thanks.

Wayne
Re: Should I Clean Up My Collections? [message #665013 is a reply to message #665011] Thu, 17 August 2017 12:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
What section of compound trigger collection.delete is in? It might be needed if it is in BEFORE/AFTER EACH ROW section.

SY.
Re: Should I Clean Up My Collections? [message #665014 is a reply to message #665013] Thu, 17 August 2017 13:12 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
Yeah, I should have clarified. It's in the BEFORE STATEMENT section:

   BEFORE STATEMENT
   IS
   BEGIN
      -- Initialize
      fUnitLocked := FALSE;
      tblNewAsnMaster.DELETE;
   END
   BEFORE STATEMENT;

Wayne
Re: Should I Clean Up My Collections? [message #665031 is a reply to message #665014] Fri, 18 August 2017 06:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, tblNewAsnMaster.DELETE isn't needed.

SY.
Re: Should I Clean Up My Collections? [message #665037 is a reply to message #665031] Fri, 18 August 2017 09:37 Go to previous message
whdyck
Messages: 25
Registered: May 2017
Junior Member
Thank you for the help.

Wayne
Previous Topic: Triggers on editioning views inheriting trigger events from NEWER editions
Next Topic: function
Goto Forum:
  


Current Time: Thu Mar 28 16:15:47 CDT 2024