Using User Defined functions or not
Hi, This question is with reference to tuning.Developers are repeatdely using same code ( for example if they want to add as second to date ther are using like date+1/86400) and they want to use something like following function so that they can write addsecond(date) in their code... function AddSecond(RefDate date) return date as begin return RefDate + 1/86400; end; Issus is now of performance...it looks like using function is degrading the performance ..Does it advisable to use extensive use of UDF in code Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Using User Defined functions or not
Harvinder Singh wrote: Hi, This question is with reference to tuning.Developers are repeatdely using same code ( for example if they want to add as second to date ther are using like date+1/86400) and they want to use something like following function so that they can write addsecond(date) in their code... function AddSecond(RefDate date) return date as begin return RefDate + 1/86400; end; Issus is now of performance...it looks like using function is degrading the performance ..Does it advisable to use extensive use of UDF in code Thanks --Harvinder Harvinder, Functions are used for relatively complicated code. You could say 'well, we use a lot of loops, so why not using function inc(i) which returns i + 1'. Calling a function means stacking up a few things (return address, parameters), jumping to an address (which may be outside of physical memory at this time), popping things out of the stack, processing, jumping back ... Complicated stuff. Must be worth the trouble. Do not believe that by making everything a function it will become more maintainable. It's very difficult to follow the logic of a program when every two instructions you have a function call. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: using User defined functions in Check constraint
Title: using User defined functions in Check constraint Hussain While you can use any Oracle supplied function within a check constraint, you cannot use a self-written function. But you can perform the same thing by using a database table trigger on the table itself. You can basically validate anything you want from any schema you wish using a database trigger (with some exceptions). A pre-insert trigger on the table would work just fine. Check them out in the documentation. hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: Arslan Dar [mailto:[EMAIL PROTECTED]]Sent: Tuesday, June 19, 2001 1:14 PMTo: Multiple recipients of list ORACLE-LSubject: using User defined functions in Check constraint Hello All Can we use user made functions in the check constraints?. Like In the check constraitns, we can use oracle functions, like LENGTH(NAME)=9. What we want to do is to check a value, which before being saved in the database(when we press save, registration no. of a patient, gets prefixed with the area code where the patient is being registrered), checks whether the values prefixed with the data are present in a table in another schema, for this purpose we want to use a function(selfmade). This we do to get a unique value irrespective of the area. If not, is there a work around, to check such a value from a different schematable in a check constraint.Oracle 8.1.7 on winnt 4 TIAHussainDBA SKMCH RC
Re[2]: using User defined functions in Check constraint
I agree w/Tom. Use the raise_application_error function when you want to make the triggering statement fail. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org Tuesday, June 19, 2001, 1:59:34 PM, Tom Mercadente wrote: MTF While you can use any Oracle supplied function within a check constraint, MTF you cannot use a self-written function. MTF But you can perform the same thing by using a database table trigger on MTF the MTF table itself. You can basically validate anything you want from any MTF schema MTF you wish using a database trigger (with some exceptions). MTF A pre-insert trigger on the table would work just fine. Check them out MTF in MTF the documentation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
