This question arose as a side issue of this: Adding Many (UDF) Validation Functions to Oracle - Which Method Works Fastest
I discuss between setting a low-level function that will be used everywhere in my application in an object as CONSTRUCTOR FUNCTION , so it is encapsulated with the type that it returns, or simply by creating a STAND-ALONE . I know that most of the results of these tests would be inconsequential because the number of cycles is so great. But in our application, this function can be used to check multiple columns in each large iteration of the loop. Thus, it would not be easy for us to achieve 3 million checks in one process. And all this returns to the web page, so the user is waiting for these results, so each mssec matters.
So, I had the results, and here is what I found ...
/***** isValid is a CONSTRUCTOR FUNCTION of an OBJECT and it is deterministic passing in: 'blah' -> 12 seconds x -> 464 msecs changing x to varchar2(7) x -> 2 seconds (this is how we would use it in most cases within loops) *****/ declare x number; --x varchar2(7); begin for i in 1 .. 3000000 loop x := x + 1; if (isValid(x,'number').result = 1) then null; end if; end loop; end; / /***** isValid2 is a STAND-ALONE FUNCTION and it is deterministic passing in: 'blah' -> 407 msecs x -> 4 seconds changing x to varchar2(7) x -> 4 seconds (this is how we would use it in most cases within loops) *****/ declare x number; --x varchar2(7); begin for i in 1 .. 3000000 loop x := x + 1; if (isValid2(x,'number').result = 1) then null; end if; end loop; end; /
Based on these results, I think I'm going to go with the CONSTRUCTOR FUNCTION of an OBJECT approach.
So my question is: why is the result between 'blah' and x completely reversed between two different methods?
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for 64-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production PLSQL_OPTIMIZE_LEVEL = 2 (I've never read up on this, this is a test box, I'm not sure what it *should* be set to yet but I will read up on it) PLSQL_CODE_TYPE = INTERPRETED (I know NATIVE is faster but they won't change it.)
Here is a standalone function ...
create or replace type valObj as object ( result number(1), resulttext varchar2(32000) ); / create or replace function isValid2(v in varchar2, f in varchar2) return valObj deterministic is test PLS_INTEGER; begin if f = 'number' then begin test := to_number(v); return valObj(1,null); exception when VALUE_ERROR then return valObj(0,'Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...'); end; elsif f = 'phone' then null; --TO DO elsif f = 'integer' then null; --TO DO elsif f = 'email' then null; --TO DO elsif f = 'IPv4' then null; --TO DO elsif f = 'IPv6' then null; --TO DO end if; --dozens of others to follow.... end; /
And here is the object / function ...
create or replace type isValid as object ( result number(1), resulttext varchar2(32000), constructor function isValid(v varchar, f varchar) return self as result deterministic ); / create or replace type body isValid as constructor function isValid(v varchar, f varchar) return self as result deterministic is test PLS_INTEGER; begin if f = 'number' then begin test := to_number(v); self.result := 1; self.resulttext := null; return; exception when VALUE_ERROR then self.result := 0; self.resulttext := 'Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...'; return; end; elsif f = 'phone' then null;