RE: Complex Integrity Checking

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 [mailto:Waleed.Khedr_at_FMR.COM]
> 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 <ORACLE-L_at_fatcity.com>
> 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 <my 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).