FAQ
Hi guys. Here's my problem.
I want to check the new values (when inserting&updating a table) against the
ones in the existing rows. Something like checking for duplicate values, but
using a unique constraint doesn't suit my needs.
I think of a before insert&update trigger, wherein checking my condition and
raise a error if not valid. The problem is, in case of an update statement,
I get the mutating "ORA-04091 table is mutating....".
I read a lot of doc but I didn't find any helping ideas. Can you give me
some, or maybe a new approach to this kind of problem?
Thanks in advance!


iulian

The information contained in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it. If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action in
reliance of the contents of this information is strictly prohibited and
may be unlawful. Orange Romania SA is neither liable for the proper,
complete transmission of the information contained in this communication
nor any delay in its receipt.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Iulian.ILIES_at_orange.ro

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Jack van Zanen at Jun 4, 2002 at 2:13 pm
    Hi

    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key

    Jack

    Iulian.ILIES_at_oran
    ge.ro To: Multiple recipients of list ORACLE-L
    Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex Integrity Checking

    04-06-2002 15:58
    Please respond to
    ORACLE-L

    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against
    the
    ones in the existing rows. Something like checking for duplicate values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition
    and
    raise a error if not valid. The problem is, in case of an update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this communication
    without the authority of Ernst & Young. Ernst & Young is neither liable for
    the proper and complete transmission of the information contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been maintained nor
    that the communication is free of viruses, interceptions or interference.

    If you are not the intended recipient of this communication please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms and
    conditions, which contain a clause that limits its liability. A copy of
    these terms and conditions is available on request free of charge.
    ===================================================================

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 4, 2002 at 2:23 pm
    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against the
    ones in the existing rows. Something like checking for duplicate values, but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition and
    raise a error if not valid. The problem is, in case of an update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!


    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gogala, Mladen at Jun 4, 2002 at 2:38 pm
    Normalize your application so that tables describe only one type of
    objects and do not implement row_type flags. When the design is streamlined
    you'll be able to use unique constraints.
    -----Original Message-----
    From: Iulian.ILIES_at_orange.ro
    Sent: Tuesday, June 04, 2002 9:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Complex Integrity Checking


    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a
    table) against the
    ones in the existing rows. Something like checking for
    duplicate values, but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking
    my condition and
    raise a error if not valid. The problem is, in case of an
    update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can
    you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian


    **************************************************************
    ****************
    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking
    action in
    reliance of the contents of this information is strictly
    prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **************************************************************
    ****************

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gogala, Mladen
    INET: MGogala_at_oxhp.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 4, 2002 at 2:38 pm
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi

    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key

    Jack



    Iulian.ILIES_at_oran

    ge.ro To: Multiple recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex Integrity
    Checking

    04-06-2002 15:58

    Please respond to

    ORACLE-L

    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against
    the
    ones in the existing rows. Something like checking for duplicate values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition
    and
    raise a error if not valid. The problem is, in case of an update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    **

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this communication
    without the authority of Ernst & Young. Ernst & Young is neither liable for
    the proper and complete transmission of the information contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been maintained nor
    that the communication is free of viruses, interceptions or interference.

    If you are not the intended recipient of this communication please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms and
    conditions, which contain a clause that limits its liability. A copy of
    these terms and conditions is available on request free of charge.
    ===================================================================

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Alexandre Gorbatchev at Jun 4, 2002 at 2:58 pm
    Hi iulian,

    In the row level insert, update trigger you can log somewhere (in package
    PL/SQL-table or in other table) changes you are making. Than in the
    stalement level triggers AFTER UPDATE, INSERT you may perform you checks and
    raise an exception if needed.
    You cannot access modified table from row level triggers, but can do that
    from statement level trigger. And this is the only way to go. Of course, you
    can check it on Application level, but that's another story.

    This is not a strait forward way for "checking for duplicate values". Try to
    re-state your requirements. Make use of additional calculated columns maybe,
    that you can to use regular unique constraints of Oracle. Perhaps,
    function-based indexes could help you.

    HTH,

    --
    Alexandre
    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against the
    ones in the existing rows. Something like checking for duplicate values, but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    ****************************************************************************
    **
    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.
    ****************************************************************************
    **
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Alexandre Gorbatchev
    INET: alexandre.gorbatchev_at_avermann.de

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Rachel Carmichael at Jun 4, 2002 at 3:24 pm
    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?

    Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian


    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action
    in
    reliance of the contents of this information is strictly prohibited
    and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
    &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht,
    noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat
    een
    verzonden e-mailbericht vrij is van virussen, noch dat
    e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
    derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij
    u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de
    verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
    algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen.
    De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and
    is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is neither
    liable for
    the proper and complete transmission of the information contained in
    this
    communication nor for any delay in its receipt. Ernst & Young does
    not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication please
    return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms
    and
    conditions, which contain a clause that limits its liability. A copy
    of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    === message truncated ===

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 4, 2002 at 4:08 pm
    I'm reticent about putting the checking code in the application before
    insert&update statement, although I'm not sure why. I'm just thinking about
    concurency and all the implications.
    On the other hand I wanted to put the bussiness rules on the database side.
    Anyway, I'll use this approach, you told me, after all.
    But what if after I check for overlapped intervals, but before inserting,
    another user insert another record with an interval extending over mine. I
    know it sounds crazy but I really like to know how this kind of stuff are
    implemented. That's the way the unique values constraint work, I guess, and
    I'd like to know more about it.

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 6:24 PM
    To: Multiple recipients of list ORACLE-L

    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?

    Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian


    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action
    in
    reliance of the contents of this information is strictly prohibited
    and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
    &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht,
    noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat
    een
    verzonden e-mailbericht vrij is van virussen, noch dat
    e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
    derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij
    u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de
    verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
    algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen.
    De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and
    is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is neither
    liable for
    the proper and complete transmission of the information contained in
    this
    communication nor for any delay in its receipt. Ernst & Young does
    not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication please
    return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms
    and
    conditions, which contain a clause that limits its liability. A copy
    of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    === message truncated ===

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Jun 4, 2002 at 4:23 pm
    iulian - If it makes you feel better, write a PL/SQL procedure to do the
    checking and always call it rather than the table directly. That way you've
    put your rule "in the database". If you are going to adhere to this
    philosophy, you're going to be into PL/SQL in a big way.
    Dennis Williams
    DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 11:08 AM
    To: Multiple recipients of list ORACLE-L

    I'm reticent about putting the checking code in the application before
    insert&update statement, although I'm not sure why. I'm just thinking about
    concurency and all the implications.
    On the other hand I wanted to put the bussiness rules on the database side.
    Anyway, I'll use this approach, you told me, after all.
    But what if after I check for overlapped intervals, but before inserting,
    another user insert another record with an interval extending over mine. I
    know it sounds crazy but I really like to know how this kind of stuff are
    implemented. That's the way the unique values constraint work, I guess, and
    I'd like to know more about it.

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 6:24 PM
    To: Multiple recipients of list ORACLE-L

    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?

    Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian


    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action
    in
    reliance of the contents of this information is strictly prohibited
    and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
    &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht,
    noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat
    een
    verzonden e-mailbericht vrij is van virussen, noch dat
    e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
    derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij
    u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de
    verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
    algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen.
    De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and
    is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is neither
    liable for
    the proper and complete transmission of the information contained in
    this
    communication nor for any delay in its receipt. Ernst & Young does
    not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication please
    return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms
    and
    conditions, which contain a clause that limits its liability. A copy
    of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    === message truncated ===

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aponte, Tony at Jun 4, 2002 at 4:28 pm
    I would look into combining a before-insert row-level trigger with an autonomous transaction procedure. The procedure would execute the validating query using parameters passed by the trigger. If your new row values would cause an overlap then return a user defined exception to the trigger. The trigger should trap this exception and cause a failure in the transaction.

    I think it would work something like this:
    You attempt to insert an interval that will violate your rule.
    In the trigger on INTERVALS you pass the 2 :NEW values to the procedure.
    The procedure queries INTERVALS and sees the table as it existed before your insert but does not cause a mutating table condition (because it's defined as an autonomous transaction procedure.)
    The procedure finds that the new row will cause an overlap and returns an exception.
    The trigger receives an exception and propagates it with some meaningful message.
    The insert fails.

    Let us know how (if) this works for you.

    Tony Aponte

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 10:38 AM
    To: Multiple recipients of list ORACLE-L

    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi

    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key

    Jack



    Iulian.ILIES_at_oran

    ge.ro To: Multiple recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex Integrity
    Checking

    04-06-2002 15:58

    Please respond to

    ORACLE-L

    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against
    the
    ones in the existing rows. Something like checking for duplicate values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition
    and
    raise a error if not valid. The problem is, in case of an update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    **

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this communication
    without the authority of Ernst & Young. Ernst & Young is neither liable for
    the proper and complete transmission of the information contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been maintained nor
    that the communication is free of viruses, interceptions or interference.

    If you are not the intended recipient of this communication please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms and
    conditions, which contain a clause that limits its liability. A copy of
    these terms and conditions is available on request free of charge.
    ===================================================================

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gogala, Mladen at Jun 4, 2002 at 4:38 pm
    I don't believe that you can check for overlapping interval within a
    database trigger. For that, you'd need two tables:
    one to enter interval and one to check against. Of course,
    the second table should also be populated, most likely by
    a 'AFTER INSERT' trigger.
    -----Original Message-----
    From: Iulian.ILIES_at_orange.ro
    Sent: Tuesday, June 04, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Complex Integrity Checking


    I'm reticent about putting the checking code in the application before
    insert&update statement, although I'm not sure why. I'm just
    thinking about
    concurency and all the implications.
    On the other hand I wanted to put the bussiness rules on the
    database side.
    Anyway, I'll use this approach, you told me, after all.
    But what if after I check for overlapped intervals, but
    before inserting,
    another user insert another record with an interval extending
    over mine. I
    know it sounds crazy but I really like to know how this kind
    of stuff are
    implemented. That's the way the unique values constraint
    work, I guess, and
    I'd like to know more about it.

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 6:24 PM
    To: Multiple recipients of list ORACLE-L


    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?


    --- Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    **********************************************************************
    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.
    **********************************************************************

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas.
    Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    **************************************************************
    **************
    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others
    authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action
    in
    reliance of the contents of this information is strictly prohibited
    and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **************************************************************
    **************
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
    &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden
    e-mailbericht,
    noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet
    garanderen dat
    een
    verzonden e-mailbericht vrij is van virussen, noch dat
    e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
    derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht,
    verzoeken wij
    u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de
    verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
    algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is
    opgenomen.
    De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and
    is
    intended solely for the use of the individual or entity to
    whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is neither
    liable for
    the proper and complete transmission of the information contained in
    this
    communication nor for any delay in its receipt. Ernst & Young does
    not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication please
    return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms
    and
    conditions, which contain a clause that limits its liability. A copy
    of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    === message truncated ===


    __________________________________________________
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gogala, Mladen
    INET: MGogala_at_oxhp.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Richard Huntley at Jun 4, 2002 at 6:38 pm
    Iulian,

    The solution for ORA-4091 is to create two triggers--a row level and a
    statement level.
    In the row level you simply record the values of :new.start_time and
    :new.end_time into
    a PL/SQL table. Then in the statement level trigger you perform the check
    based on the
    recorded values in the PL/SQL table. Record the values using a PL/SQL table
    in a package,
    that way you won't have to worry about simultaneous updates by different
    sessions. Something
    like this...

    Interval

    s date
    e date

    create or replace package INTERVAL_PACKAGE as

    type t_start is table of interval.s%TYPE index by binary_integer;
    type t_end is table of interval.e%TYPE index by binary_integer;

    v_s t_start;
    v_e t_end;
    v_count binary_integer :=0;

    end INTERVAL_PACKAGE;

    create or replace trigger RINTERVAL
    before insert or update on interval
    for each row
    begin
    /* recored new time intervals in interval_package */

    interval_package.v_count := interval_package.v_count + 1;
    interval_package.v_s(interval_package.v_count) := :new.s;
    interval_package.v_e(interval_package.v_count) := :new.e;

    end RINTERVAL;

    create or replace trigger SInterval
    after insert or update on interval
    declare

    v_start interval.s%TYPE; --from pl/sql table
    v_end interval.e%TYPE;
    v_start_curr interval.s%TYPE; --from interval table
    v_end_curr interval.e%TYPE;

    begin
    /* loop through each date interval inserted or updated and verify

    no overlap */
    for v_i in 1..interval_package.v_count loop


    v_start := interval_package.v_s( v_i );
    v_end := interval_package.v_e( v_i );


    /* TEST */

    ...some test goes here...

    if ... then

    --raise_application_error(-20000,'date overlap'||v_start||v_end);
    end if;


    end loop;

    interval_package.v_count := 0;
    interval_package.v_s.DELETE;
    interval_package.v_e.DELETE;

    end SInterval;
    /

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 12:38 PM
    To: Multiple recipients of list ORACLE-L

    I don't believe that you can check for overlapping interval within a
    database trigger. For that, you'd need two tables:
    one to enter interval and one to check against. Of course,
    the second table should also be populated, most likely by
    a 'AFTER INSERT' trigger.
    -----Original Message-----
    From: Iulian.ILIES_at_orange.ro
    Sent: Tuesday, June 04, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Complex Integrity Checking


    I'm reticent about putting the checking code in the application before
    insert&update statement, although I'm not sure why. I'm just
    thinking about
    concurency and all the implications.
    On the other hand I wanted to put the bussiness rules on the
    database side.
    Anyway, I'll use this approach, you told me, after all.
    But what if after I check for overlapped intervals, but
    before inserting,
    another user insert another record with an interval extending
    over mine. I
    know it sounds crazy but I really like to know how this kind
    of stuff are
    implemented. That's the way the unique values constraint
    work, I guess, and
    I'd like to know more about it.

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 6:24 PM
    To: Multiple recipients of list ORACLE-L


    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?


    --- Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    **********************************************************************
    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.
    **********************************************************************

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L








    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas.
    Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    **************************************************************
    **************
    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others
    authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action
    in
    reliance of the contents of this information is strictly prohibited
    and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **************************************************************
    **************
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
    &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden
    e-mailbericht,
    noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet
    garanderen dat
    een
    verzonden e-mailbericht vrij is van virussen, noch dat
    e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
    derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht,
    verzoeken wij
    u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de
    verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
    algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is
    opgenomen.
    De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and
    is
    intended solely for the use of the individual or entity to
    whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is neither
    liable for
    the proper and complete transmission of the information contained in
    this
    communication nor for any delay in its receipt. Ernst & Young does
    not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication please
    return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms
    and
    conditions, which contain a clause that limits its liability. A copy
    of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing
    Lists
    === message truncated ===


    __________________________________________________
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gogala, Mladen
    INET: MGogala_at_oxhp.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Richard Huntley
    INET: rhuntley_at_mindleaders.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Jun 4, 2002 at 7:08 pm
    Create a unique function based index on your table (start_time, end_time)
    that will return a number that uniquely identify the range. If an overlap
    took place you should get a duplicate error.

    I think you should use these PRAGMA's in the function code to fool Oracle
    that the function is very pure and does not see the changes that are
    happening, something like this.

    Hope it helps,

    Waleed

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 10:38 AM
    To: Multiple recipients of list ORACLE-L

    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    Hi

    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key

    Jack



    Iulian.ILIES_at_oran

    ge.ro To: Multiple recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex Integrity
    Checking

    04-06-2002 15:58

    Please respond to

    ORACLE-L

    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table) against
    the
    ones in the existing rows. Something like checking for duplicate values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my condition
    and
    raise a error if not valid. The problem is, in case of an update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian

    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    **

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this communication
    without the authority of Ernst & Young. Ernst & Young is neither liable for
    the proper and complete transmission of the information contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been maintained nor
    that the communication is free of viruses, interceptions or interference.

    If you are not the intended recipient of this communication please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms and
    conditions, which contain a clause that limits its liability. A copy of
    these terms and conditions is available on request free of charge.
    ===================================================================

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Gogala, Mladen at Jun 4, 2002 at 8:13 pm
    Pragma restrict_references is an old mechanism. The new trick
    is to declare function as deterministic. For pragma, the function
    had to be a part of a package. Deterministic function, on the
    other hand, does not have to be a part of anything.
    -----Original Message-----
    From: Khedr, Waleed
    Sent: Tuesday, June 04, 2002 3:08 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Complex Integrity Checking


    Create a unique function based index on your table
    (start_time, end_time)
    that will return a number that uniquely identify the range.
    If an overlap
    took place you should get a duplicate error.

    I think you should use these PRAGMA's in the function code to
    fool Oracle
    that the function is very pure and does not see the changes that are
    happening, something like this.

    Hope it helps,

    Waleed

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 10:38 AM
    To: Multiple recipients of list ORACLE-L


    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    **********************************************************************
    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.
    **********************************************************************


    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To:
    Multiple recipients
    of list ORACLE-L
    Sent by: cc:
    (bcc: Jack van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject:
    Complex Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a
    table) against
    the
    ones in the existing rows. Something like checking for
    duplicate values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking
    my condition
    and
    raise a error if not valid. The problem is, in case of an
    update statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can
    you give me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian


    **************************************************************
    **************
    **

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking
    action in
    reliance of the contents of this information is strictly
    prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this
    communication
    nor any delay in its receipt.
    **************************************************************
    **************
    **


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).




    ==================================================================
    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze
    informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming
    van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden
    e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet
    garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van
    onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht,
    verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren
    aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te
    vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar
    werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is
    opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.
    =====================================================================
    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this
    communication
    without the authority of Ernst & Young. Ernst & Young is
    neither liable for
    the proper and complete transmission of the information
    contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been
    maintained nor
    that the communication is free of viruses, interceptions or
    interference.

    If you are not the intended recipient of this communication
    please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies
    general terms and
    conditions, which contain a clause that limits its liability.
    A copy of
    these terms and conditions is available on request free of charge.
    ===================================================================


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack van Zanen
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Gogala, Mladen
    INET: MGogala_at_oxhp.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Stephane Faroult at Jun 4, 2002 at 8:25 pm
    I think that something such as

    insert into my_table
    select new_start_time, new_end_time (both constants)
    from dual (well known view on x$dual)
    where not exists (select null

    from my_table
    where new_start_time between start_time and end_time
    union
    select null
    from my_table
    where new_end_time between start_time and end_time)

    should do the trick, making of course wildly optimistic assumptions
    about indices etc.
    And you can always check SQL%WHATITSNAME for the number of row
    processed if you need to keep track of rejects.

    Rachel Carmichael wrote:
    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?

    --- Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    **********************************************************************
    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.
    **********************************************************************

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian
    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mercadante, Thomas F at Jun 4, 2002 at 8:43 pm
    wouldn't an "INSTEAD OF" trigger solve the problem here? that way, he could
    program an insert/update any way he wants to...

    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 4:26 PM
    To: Multiple recipients of list ORACLE-L

    I think that something such as

    insert into my_table
    select new_start_time, new_end_time (both constants)
    from dual (well known view on x$dual)
    where not exists (select null

    from my_table
    where new_start_time between start_time and end_time
    union
    select null
    from my_table
    where new_end_time between start_time and end_time)

    should do the trick, making of course wildly optimistic assumptions
    about indices etc.
    And you can always check SQL%WHATITSNAME for the number of row
    processed if you need to keep track of rejects.

    Rachel Carmichael wrote:
    maybe I'm being simplistic and I know this will impact performance but
    why not simply do a select to see if the condition exists before the
    insert or update?

    --- Iulian.ILIES_at_orange.ro wrote:
    I said something like "the way the unique constraints work".
    Ok. Here's my context.
    I have a table say intervals and 2 columns start_time and end_time.
    I want to check for overlapped intervals.
    I know what conditions to check but I can't implement them.
    Thanks!

    iulian

    -----Original Message-----
    Sent: Tuesday, June 04, 2002 5:13 PM
    To: Multiple recipients of list ORACLE-L


    **********************************************************************
    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.
    **********************************************************************

    Hi


    if unique does not suit your need what exactly do you need to check?
    duplicates: use primary key


    Jack




    Iulian.ILIES_at_oran

    ge.ro To: Multiple
    recipients
    of list ORACLE-L
    Sent by: cc: (bcc: Jack
    van
    Zanen/nlzanen1/External/MEY/NL)
    root_at_fatcity.com Subject: Complex
    Integrity
    Checking




    04-06-2002 15:58

    Please respond to

    ORACLE-L







    Hi guys. Here's my problem.
    I want to check the new values (when inserting&updating a table)
    against
    the
    ones in the existing rows. Something like checking for duplicate
    values,
    but
    using a unique constraint doesn't suit my needs.
    I think of a before insert&update trigger, wherein checking my
    condition
    and
    raise a error if not valid. The problem is, in case of an update
    statement,
    I get the mutating "ORA-04091 table is mutating....".
    I read a lot of doc but I didn't find any helping ideas. Can you give
    me
    some, or maybe a new approach to this kind of problem?
    Thanks in advance!

    iulian
    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 5, 2002 at 8:33 am
    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jamadagni, Rajendra at Jun 5, 2002 at 1:23 pm
    no matter what you do, if you access table A inside a trigger on table A,
    oracle will give you mutating table error. What you could (and I really mean
    you have to consider your business logic here) is go ahead and insert the
    rows with a temp flag. As soon as you commit, fire up a procedure that will
    do the scan on the table and delete appropriate rows which have the temp
    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: InterScan_Disclaimer.txt
  • Khedr, Waleed at Jun 5, 2002 at 1:53 pm
    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed


    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 5, 2002 at 2:38 pm
    -----Original Message-----
    Sent: Wednesday, June 05, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed

    I'm sorry bu I can't answer to your questions because I don't see the point.

    Here's a test table:
    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Here are some statemens:

    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (3,5)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (2,3)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (7,8)

    What I want is that the integrity rule (no overlapped intervals) be
    operational even if i insert a new record or more or update one or more.
    Think of it the same way an unique key works.
    This is a simplified table for example purpose. In fact my application is a
    resource scheduler, so I want a resource not to be assigned for more than 1
    client at the same time.
    Here the start_time and end_time are of number type just for testing, but of
    course it'll be of date type.

    I'm starting to think that what I want, can be done in a simple, clean
    manner but using complex workarounds, isn't it?
    Thanks!

    iulian

    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Richard Huntley at Jun 5, 2002 at 4:43 pm
    Iulian, this is what you want, NO? (except this works for date fields not
    number fields as you've put in
    your latest posts)... This is done using two triggers.

    SQL> insert into interval
    values('01-JAN-2002','01-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-MAR-2002','26-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-FEB-2002','14-MAR-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('01-DEC-1999','01-JAN-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('05-JAN-2002','01-FEB-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 10:39 AM
    To: Multiple recipients of list ORACLE-L

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed

    I'm sorry bu I can't answer to your questions because I don't see the point.

    Here's a test table:
    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Here are some statemens:

    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (3,5)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (2,3)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (7,8)

    What I want is that the integrity rule (no overlapped intervals) be
    operational even if i insert a new record or more or update one or more.
    Think of it the same way an unique key works.
    This is a simplified table for example purpose. In fact my application is a
    resource scheduler, so I want a resource not to be assigned for more than 1
    client at the same time.
    Here the start_time and end_time are of number type just for testing, but of
    course it'll be of date type.

    I'm starting to think that what I want, can be done in a simple, clean
    manner but using complex workarounds, isn't it?
    Thanks!

    iulian

    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Richard Huntley
    INET: rhuntley_at_mindleaders.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Jun 5, 2002 at 7:18 pm
    Here is a working example:

    drop table test_intervals;

    CREATE TABLE test_intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL,primary key (start_time,end_time)

    );

    create or replace package test_mut as
    type start_time_tab_type is table of number index by binary_integer;
    start_time_tab start_time_tab_type;
    end_time_tab start_time_tab_type;
    end;
    /

    create or replace trigger testupd before update or insert on test_intervals
    for each row
    declare
    m_cnt number := 0;
    begin
    test_mut.start_time_tab(test_mut.start_time_tab.count + 1) :=
    :new.start_time;
    test_mut.end_time_tab (test_mut.end_time_tab.count + 1) :=
    :new.end_time;
    end;
    /

    create or replace trigger testupd1 after update or insert on test_intervals

    declare
    m_cnt number := 0;
    begin
    for i in 1..test_mut.start_time_tab.count loop
    dbms_output.put_line(i);
    select count(*) into m_cnt

    from test_intervals
    where (test_mut.start_time_tab(i) between start_time and end_time
    or
    start_time between test_mut.start_time_tab(i) and
    test_mut.end_time_tab(i))
    and not(start_time = test_mut.start_time_tab(i) and end_time =

    test_mut.end_time_tab(i));

    if m_cnt <> 0
    then
    test_mut.start_time_tab.delete;
    test_mut.end_time_tab.delete;
    raise_application_error (-20001,' overlap error ');
    end if;
    end loop;
    test_mut.start_time_tab.delete;
    test_mut.end_time_tab.delete;
    end;
    /

    Try different inserts/updates.

    Regards,

    Waleed

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aponte, Tony at Jun 5, 2002 at 9:59 pm
    In my suggestion you example for check_for_overlapped_intervals would have the autonomous transaction pragma, thereby avoiding the mutating table error.

    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 10:39 AM
    To: Multiple recipients of list ORACLE-L

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed

    I'm sorry bu I can't answer to your questions because I don't see the point.

    Here's a test table:
    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Here are some statemens:

    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (3,5)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (2,3)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (7,8)

    What I want is that the integrity rule (no overlapped intervals) be
    operational even if i insert a new record or more or update one or more.
    Think of it the same way an unique key works.
    This is a simplified table for example purpose. In fact my application is a
    resource scheduler, so I want a resource not to be assigned for more than 1
    client at the same time.
    Here the start_time and end_time are of number type just for testing, but of
    course it'll be of date type.

    I'm starting to think that what I want, can be done in a simple, clean
    manner but using complex workarounds, isn't it?
    Thanks!

    iulian

    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aponte, Tony at Jun 5, 2002 at 10:33 pm
    With the introduction of Autonomous Transactions this is no longer entirely true. If you call an autonomous transaction procedure, it is executed in a separate transaction context. This gives you the ability to probe the mutating table without inducing the error. A good explanation can be found in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table A,
    oracle will give you mutating table error. What you could (and I really mean
    you have to consider your business logic here) is go ahead and insert the
    rows with a temp flag. As soon as you commit, fire up a procedure that will
    do the scan on the table and delete appropriate rows which have the temp
    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Jun 6, 2002 at 1:03 am
    The problem with this solution is the Autonomous Transactions will not be
    able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of the
    transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer entirely
    true. If you call an autonomous transaction procedure, it is executed in a
    separate transaction context. This gives you the ability to probe the
    mutating table without inducing the error. A good explanation can be found
    in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous
    Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table A,
    oracle will give you mutating table error. What you could (and I really mean

    you have to consider your business logic here) is go ahead and insert the
    rows with a temp flag. As soon as you commit, fire up a procedure that will
    do the scan on the table and delete appropriate rows which have the temp
    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Iulian.ILIES_at_orange.ro at Jun 6, 2002 at 7:13 am
    Ok Richard, this seems to be what I want.
    I read carefully the message but I didn't find the trigger
    RHUNTLEY.SINTERVAL

    How did you do that?
    Thanks!


    iulian


    -----Original Message-----
    Sent: Wednesday, June 05, 2002 7:44 PM
    To: Multiple recipients of list ORACLE-L

    Iulian, this is what you want, NO? (except this works for date fields not
    number fields as you've put in
    your latest posts)... This is done using two triggers.

    SQL> insert into interval
    values('01-JAN-2002','01-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-MAR-2002','26-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-FEB-2002','14-MAR-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('01-DEC-1999','01-JAN-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('05-JAN-2002','01-FEB-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    -----Original Message-----
    ]
    Sent: Wednesday, June 05, 2002 10:39 AM
    To: Multiple recipients of list ORACLE-L

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed

    I'm sorry bu I can't answer to your questions because I don't see the point.

    Here's a test table:
    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Here are some statemens:

    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (3,5)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (2,3)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (7,8)

    What I want is that the integrity rule (no overlapped intervals) be
    operational even if i insert a new record or more or update one or more.
    Think of it the same way an unique key works.
    This is a simplified table for example purpose. In fact my application is a
    resource scheduler, so I want a resource not to be assigned for more than 1
    client at the same time.
    Here the start_time and end_time are of number type just for testing, but of

    course it'll be of date type.

    I'm starting to think that what I want, can be done in a simple, clean
    manner but using complex workarounds, isn't it?
    Thanks!

    iulian

    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jamadagni, Rajendra at Jun 6, 2002 at 2:28 pm
    Thanks Tony,

    I probably ignored autonomous transaction because on our version 8186 there
    is a bug that prohibits us from using autonomous transactions with db links.
    so that is the reason I probably missed it.

    You are correct.

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: InterScan_Disclaimer.txt
  • Aponte, Tony at Jun 6, 2002 at 3:43 pm
    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the child transaction the ability to see uncommitted changes made by the parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not be able to see any changes done within the current transaction only the committed one. So no way to enforce business logic during the context of the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer entirely true. If you call an autonomous transaction procedure, it is executed in a separate transaction context. This gives you the ability to probe the mutating table without inducing the error. A good explanation can be found in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table A,
    oracle will give you mutating table error. What you could (and I really mean
    you have to consider your business logic here) is go ahead and insert the
    rows with a temp flag. As soon as you commit, fire up a procedure that will
    do the scan on the table and delete appropriate rows which have the temp
    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Richard Huntley at Jun 6, 2002 at 3:53 pm
    Iulian, here is everything you need to recreate that, table, package, row
    level trigger, statement level trigger and test data.
    Once you've duplicated this, feel free to modify and hopefully you'll be
    able to do this for your specific case.


    -----Original Message-----
    Sent: Thursday, June 06, 2002 3:13 AM
    To: Multiple recipients of list ORACLE-L

    Ok Richard, this seems to be what I want.
    I read carefully the message but I didn't find the trigger
    RHUNTLEY.SINTERVAL

    How did you do that?
    Thanks!


    iulian


    -----Original Message-----
    Sent: Wednesday, June 05, 2002 7:44 PM
    To: Multiple recipients of list ORACLE-L

    Iulian, this is what you want, NO? (except this works for date fields not
    number fields as you've put in
    your latest posts)... This is done using two triggers.

    SQL> insert into interval
    values('01-JAN-2002','01-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-MAR-2002','26-MAR-2002');
    2
    1 row created.

    SQL> insert into interval
    values('03-FEB-2002','14-MAR-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('01-DEC-1999','01-JAN-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    SQL> insert into interval
    values('05-JAN-2002','01-FEB-2002');
    2 insert into interval

    *
    ERROR at line 1:

    ORA-20000: date overlap 03-FEB-02 14-MAR-02
    ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
    ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'

    -----Original Message-----
    ]
    Sent: Wednesday, June 05, 2002 10:39 AM
    To: Multiple recipients of list ORACLE-L

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    This email has been tested for viruses by F-Secure Antivirus
    administered by IT Network Department.

    two questions: How many records do you insert into that table before a
    commit ?

    Is the whole issue simply mutating table error when running some business
    logic in an insert/update trigger for the intervals table?

    Regards,

    Waleed

    I'm sorry bu I can't answer to your questions because I don't see the point.

    Here's a test table:
    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Here are some statemens:

    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (3,5)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (2,3)
    /
    INSERT INTO intervals
    (START_TIME,END_TIME)

    VALUES

    (7,8)

    What I want is that the integrity rule (no overlapped intervals) be
    operational even if i insert a new record or more or update one or more.
    Think of it the same way an unique key works.
    This is a simplified table for example purpose. In fact my application is a
    resource scheduler, so I want a resource not to be assigned for more than 1
    client at the same time.
    Here the start_time and end_time are of number type just for testing, but of

    course it'll be of date type.

    I'm starting to think that what I want, can be done in a simple, clean
    manner but using complex workarounds, isn't it?
    Thanks!

    iulian

    -----Original Message-----
    To: Multiple recipients of list ORACLE-L
    Sent: 6/5/02 4:33 AM

    First of all I want to thank you all for your answers.
    Let's take'em one by one:

    Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
    - I cannont use "instead of" trigger because of this error:

    ORA-25002: cannot create INSTEAD OF triggers on tables
    Cause: Only BEFORE or AFTER triggers can be created on a table.
    Action: Change the trigger type to BEFORE or AFTER.

    I have an Oracle database version 9.0.1.1.1

    Attn: Stephane Faroult [sfaroult_at_oriole.com]
    - for insert your approach works (although I have to change a bit the
    select
    in exists condirion) but what about the update statements.
    - moreover i think this will not keep my integrity rule consistent, if
    someone try to simply use typical insert&update statements.

    Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
    - Can you give me an example for your unique function based index, I
    mean
    how can you assign an unique number for various intervals.
    - anyway if this can be done I assume that would be a very nice, clean
    solution

    Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
    [MGogala_at_oxhp.com]
    - this really doesn't suit my needs, create 2 tables instead of one

    Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
    [AponteT_at_hsn.net]
    - I did make a function:

    FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER,
    p_end_time IN NUMBER)
    RETURN NUMBER

    IS

    n NUMBER;
    BEGIN

    when this select have records to count
    means that the new interval overlap an existing one
    and still is not corectly implement for update stament
    where it should not consider the current record
    SELECT COUNT(*) INTO n
    FROM intervals
    WHERE start_time < p_end_time
    AND end_time > p_start_time;
    RETURN(n);
    END;

    and use it in the trigger:

    CREATE OR REPLACE TRIGGER bi_interval
    BEFORE INSERT OR UPDATE

    ON intervals
    REFERENCING NEW AS NEW OLD AS OLD

    FOR EACH ROW

    BEGIN

    IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
    THEN

    raise_application_error(-20100, 'Overlapped intervals');
    END IF;

    END;

    but still got the same mutating table error. Am I wrong someplace.

    Thanks again. I try to test all of your solution and above are my
    answers.
    Can you still help me.
    I simplify my problem using a table INTERVALS with 2 columns START_TIME,
    END_TIME of NUMBER type.

    CREATE TABLE intervals (

    start_time NUMBER NOT NULL,
    end_time NUMBER NOT NULL

    )

    Please try to insert some data and implement an integrity system like I
    wanted
    Regards

    iulian

    The information contained in this communication is confidential and
    may be legally privileged. It is intended solely for the use of the
    individual or entity to whom it is addressed and others authorised to
    receive it. If you are not the intended recipient you are hereby
    notified that any disclosure, copying, distribution or taking action in
    reliance of the contents of this information is strictly prohibited and
    may be unlawful. Orange Romania SA is neither liable for the proper,
    complete transmission of the information contained in this communication
    nor any delay in its receipt.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: Iulian.ILIES_at_orange.ro

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Richard Huntley
    INET: rhuntley_at_mindleaders.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    application/octet-stream attachment: mutating_tables.sql
  • Khedr, Waleed at Jun 6, 2002 at 4:08 pm
    I could not find this and do not know how it could happen!

    If you can post here what you read, it will be appreciated.

    Waleed

    -----Original Message-----
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM
    Sent: 6/6/02 9:26 AM

    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the
    child transaction the ability to see uncommitted changes made by the
    parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not
    be able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of
    the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer
    entirely true. If you call an autonomous transaction procedure, it is
    executed in a separate transaction context. This gives you the ability
    to probe the mutating table without inducing the error. A good
    explanation can be found in Tom Kyte's Export One-on-one Oracle book in
    the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table
    A,
    oracle will give you mutating table error. What you could (and I really
    mean
    you have to consider your business logic here) is go ahead and insert
    the
    rows with a temp flag. As soon as you commit, fire up a procedure that
    will
    do the scan on the table and delete appropriate rows which have the temp

    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aponte, Tony at Jun 10, 2002 at 3:23 pm
    Sorry for the delayed reply (I type with 2 fingers.) The section starts on page 685.

    <<< rip >>>>>
    Database Changes
    Now, this is were things get interesting - database changes. Here, things can get a little murky. Database changes made, but not yet committed by a parent transaction are not visible to the autonomous transactions. Changes made, and already committed by the parent transaction, are always visible to the child transaction. Changes made by the autonomous transaction may or may not be visible to the parent depending on its isolation level.

    I said before though, that this is were things get murky. I was pretty clear above in saying that changes made by the parent transaction are not visible to the child but that's not 100 percent of the story. A cursor opened by the child autonomous transaction will not see uncommitted changes, but a cursor opened by the parent and fetched from the child will. The following case shows how this works. We will recreate our EMP ..................

    <<< end rip >>>>>

    The rest of the section demonstrates the voodoo magic. I confess that we didn't go this route and used the global array in a PL/SQL package, and the author discourages the use of autonomous transactions to get around mutating table errors. But it might be just right for someone's need.

    Regards,

    Tony Aponte

    -----Original Message-----
    Sent: Thursday, June 06, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    I could not find this and do not know how it could happen!

    If you can post here what you read, it will be appreciated.

    Waleed

    -----Original Message-----
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM
    Sent: 6/6/02 9:26 AM

    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the
    child transaction the ability to see uncommitted changes made by the
    parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not
    be able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of
    the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer
    entirely true. If you call an autonomous transaction procedure, it is
    executed in a separate transaction context. This gives you the ability
    to probe the mutating table without inducing the error. A good
    explanation can be found in Tom Kyte's Export One-on-one Oracle book in
    the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table
    A,
    oracle will give you mutating table error. What you could (and I really
    mean
    you have to consider your business logic here) is go ahead and insert
    the
    rows with a temp flag. As soon as you commit, fire up a procedure that
    will
    do the scan on the table and delete appropriate rows which have the temp

    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Jun 10, 2002 at 3:28 pm
    Again I do not see anything here saying that the child session (Autonomous
    TX) will see the changes made by the parent TX.


    If you implied this in your message, then we are in agreement.


    regards,


    Waleed

    -----Original Message-----
    Sent: Monday, June 10, 2002 10:16 AM
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM

    Sorry for the delayed reply (I type with 2 fingers.) The section starts on
    page 685.

    <<< rip >>>>>
    Database Changes
    Now, this is were things get interesting - database changes. Here, things
    can get a little murky. Database changes made, but not yet committed by a
    parent transaction are not visible to the autonomous transactions. Changes
    made, and already committed by the parent transaction, are always visible to
    the child transaction. Changes made by the autonomous transaction may or
    may not be visible to the parent depending on its isolation level.

    I said before though, that this is were things get murky. I was pretty
    clear above in saying that changes made by the parent transaction are not
    visible to the child but that's not 100 percent of the story. A cursor
    opened by the child autonomous transaction will not see uncommitted changes,
    but a cursor opened by the parent and fetched from the child will. The
    following case shows how this works. We will recreate our EMP

    ..................

    <<< end rip >>>>>

    The rest of the section demonstrates the voodoo magic. I confess that we
    didn't go this route and used the global array in a PL/SQL package, and the
    author discourages the use of autonomous transactions to get around mutating
    table errors. But it might be just right for someone's need.

    Regards,

    Tony Aponte

    -----Original Message-----
    Sent: Thursday, June 06, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    I could not find this and do not know how it could happen!

    If you can post here what you read, it will be appreciated.

    Waleed

    -----Original Message-----
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM
    Sent: 6/6/02 9:26 AM

    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the
    child transaction the ability to see uncommitted changes made by the
    parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not
    be able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of
    the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer
    entirely true. If you call an autonomous transaction procedure, it is
    executed in a separate transaction context. This gives you the ability
    to probe the mutating table without inducing the error. A good
    explanation can be found in Tom Kyte's Export One-on-one Oracle book in
    the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    < mailto:Rajendra.Jamadagni_at_espn.com >
    ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table
    A,
    oracle will give you mutating table error. What you could (and I really
    mean
    you have to consider your business logic here) is go ahead and insert
    the
    rows with a temp flag. As soon as you commit, fire up a procedure that
    will
    do the scan on the table and delete appropriate rows which have the temp

    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aponte, Tony at Jun 10, 2002 at 4:08 pm
    I once had an instructor that said "a test is worth a thousand pages of documentation." If you have access to the book, give the sample a try.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Monday, June 10, 2002 11:29 AM
    To: Multiple recipients of list ORACLE-L

    Again I do not see anything here saying that the child session (Autonomous TX) will see the changes made by the parent TX.


    If you implied this in your message, then we are in agreement.


    regards,


    Waleed

    -----Original Message-----
    Sent: Monday, June 10, 2002 10:16 AM
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM

    Sorry for the delayed reply (I type with 2 fingers.) The section starts on page 685.

    <<< rip >>>>>
    Database Changes
    Now, this is were things get interesting - database changes. Here, things can get a little murky. Database changes made, but not yet committed by a parent transaction are not visible to the autonomous transactions. Changes made, and already committed by the parent transaction, are always visible to the child transaction. Changes made by the autonomous transaction may or may not be visible to the parent depending on its isolation level.

    I said before though, that this is were things get murky. I was pretty clear above in saying that changes made by the parent transaction are not visible to the child but that's not 100 percent of the story. A cursor opened by the child autonomous transaction will not see uncommitted changes, but a cursor opened by the parent and fetched from the child will. The following case shows how this works. We will recreate our EMP ..................

    <<< end rip >>>>>

    The rest of the section demonstrates the voodoo magic. I confess that we didn't go this route and used the global array in a PL/SQL package, and the author discourages the use of autonomous transactions to get around mutating table errors. But it might be just right for someone's need.

    Regards,

    Tony Aponte

    -----Original Message-----
    Sent: Thursday, June 06, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    I could not find this and do not know how it could happen!

    If you can post here what you read, it will be appreciated.

    Waleed

    -----Original Message-----
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM
    Sent: 6/6/02 9:26 AM

    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the
    child transaction the ability to see uncommitted changes made by the
    parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not
    be able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of
    the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer
    entirely true. If you call an autonomous transaction procedure, it is
    executed in a separate transaction context. This gives you the ability
    to probe the mutating table without inducing the error. A good
    explanation can be found in Tom Kyte's Export One-on-one Oracle book in
    the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    < mailto:Rajendra.Jamadagni_at_espn.com> ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table
    A,
    oracle will give you mutating table error. What you could (and I really
    mean
    you have to consider your business logic here) is go ahead and insert
    the
    rows with a temp flag. As soon as you commit, fire up a procedure that
    will
    do the scan on the table and delete appropriate rows which have the temp

    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Aponte, Tony
    INET: AponteT_at_hsn.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Khedr, Waleed at Jun 10, 2002 at 5:18 pm
    I would not be happy to have such instructor!

    -----Original Message-----
    Sent: Monday, June 10, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    I once had an instructor that said "a test is worth a thousand pages of
    documentation." If you have access to the book, give the sample a try.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Monday, June 10, 2002 11:29 AM
    To: Multiple recipients of list ORACLE-L

    Again I do not see anything here saying that the child session (Autonomous
    TX) will see the changes made by the parent TX.


    If you implied this in your message, then we are in agreement.


    regards,


    Waleed

    -----Original Message-----
    Sent: Monday, June 10, 2002 10:16 AM
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM

    Sorry for the delayed reply (I type with 2 fingers.) The section starts on
    page 685.

    <<< rip >>>>>
    Database Changes
    Now, this is were things get interesting - database changes. Here, things
    can get a little murky. Database changes made, but not yet committed by a
    parent transaction are not visible to the autonomous transactions. Changes
    made, and already committed by the parent transaction, are always visible to
    the child transaction. Changes made by the autonomous transaction may or
    may not be visible to the parent depending on its isolation level.

    I said before though, that this is were things get murky. I was pretty
    clear above in saying that changes made by the parent transaction are not
    visible to the child but that's not 100 percent of the story. A cursor
    opened by the child autonomous transaction will not see uncommitted changes,
    but a cursor opened by the parent and fetched from the child will. The
    following case shows how this works. We will recreate our EMP

    ..................

    <<< end rip >>>>>

    The rest of the section demonstrates the voodoo magic. I confess that we
    didn't go this route and used the global array in a PL/SQL package, and the
    author discourages the use of autonomous transactions to get around mutating
    table errors. But it might be just right for someone's need.

    Regards,

    Tony Aponte

    -----Original Message-----
    Sent: Thursday, June 06, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    I could not find this and do not know how it could happen!

    If you can post here what you read, it will be appreciated.

    Waleed

    -----Original Message-----
    To: ORACLE-L_at_fatcity.com
    Cc: Waleed.Khedr_at_FMR.COM
    Sent: 6/6/02 9:26 AM

    Waleed,


    The chapter on Autonomous transactions demonstrates how to give the
    child transaction the ability to see uncommitted changes made by the
    parent transaction.


    Regards,


    Tony Aponte

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 9:03 PM
    To: Multiple recipients of list ORACLE-L

    The problem with this solution is the Autonomous Transactions will not
    be able to see any changes done within the current transaction only the
    committed one. So no way to enforce business logic during the context of
    the transaction.


    This is why I asked before how frequently commit happens.


    Regards,


    Waleed

    -----Original Message-----
    Sent: Wednesday, June 05, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    With the introduction of Autonomous Transactions this is no longer
    entirely true. If you call an autonomous transaction procedure, it is
    executed in a separate transaction context. This gives you the ability
    to probe the mutating table without inducing the error. A good
    explanation can be found in Tom Kyte's Export One-on-one Oracle book in
    the chapter on Autonomous Transactions.

    HTH

    Tony Aponte

    -----Original Message-----
    < mailto:Rajendra.Jamadagni_at_espn.com >
    ]
    Sent: Wednesday, June 05, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L

    no matter what you do, if you access table A inside a trigger on table
    A,
    oracle will give you mutating table error. What you could (and I really
    mean
    you have to consider your business logic here) is go ahead and insert
    the
    rows with a temp flag. As soon as you commit, fire up a procedure that
    will
    do the scan on the table and delete appropriate rows which have the temp

    status.

    BTW how big is this table? What is the frequency of inserts and updates?

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Khedr, Waleed
    INET: Waleed.Khedr_at_FMR.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

People

Translate

site design / logo © 2022 Grokbase