Re: equivalent for isdate, isnumeric

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Thu, 25 Sep 2003 18:39:36 -0800
Message-ID: <F001.005D116B.20030925183936@fatcity.com>

Before the "deterministic" qualifications, there was only one way to use a user defined function in the select list of a query: it was
PRAGMA RESTRICT_REFERENCES(WNDS,WNPS) The only place where you could do it was in a package. In other words, if a user defined function was to be used in the select list, it should have been part of a package. Every man is a part of the continent, a piece of the whole, but not every function should be a part of a package, unless absolutely necessary. That's why our friendly Oracle Corporation has provided DETERMINISTIC clause, among other things. Deterministic functions can be used in in select list and function based indexes. Non-deterministic functions can not. Here is a possible use use:
 1 begin
  2 if (isnumeric('&&item'))
  3 then dbms_output.put_line('&&tem'||' is numeric string.');   4 else dbms_output.put_line('&&tem'||' is non-numeric string.');   5 end if;
  6* end;
SQL> / Enter value for item: 1234

old   2: if (isnumeric('&&item'))
new   2: if (isnumeric('1234'))
old   3:   then dbms_output.put_line('&&tem'||' is numeric string.');
new   3:   then dbms_output.put_line('c'||' is numeric string.');
old   4:   else dbms_output.put_line('&&tem'||' is non-numeric string.');
new   4:   else dbms_output.put_line('c'||' is non-numeric string.');

c is numeric string.

PL/SQL procedure successfully completed.

SQL> undef item
SQL> /
Enter value for item: qwerty

old   2: if (isnumeric('&&item'))
new   2: if (isnumeric('qwerty'))
old   3:   then dbms_output.put_line('&&tem'||' is numeric string.');
new   3:   then dbms_output.put_line('c'||' is numeric string.');
old   4:   else dbms_output.put_line('&&tem'||' is non-numeric string.');
new   4:   else dbms_output.put_line('c'||' is non-numeric string.');

c is non-numeric string.

On 2003.09.25 22:04, Vladimir Begun wrote:
> Tanel Poder wrote:

>> Boolean is a datatype existing and usable in Oracle.


> > Deterministic is an Oracle way to tell a function is deterministic, i.e.

> > always returning the same result on the same input. Required for FBIs for

> > example.
> > http://tahiti.oracle.com
>
> Could you please kindly provide an example of its usage in SQL. Please
> create
> an FBI using the original function below (as is, no modifications or
> wrappers).
> It would be really intersting how "deterministic" functionality would work
> for a PL/SQL function that returns BOOLEAN datatype. Thank you!

>--

> Vladimir Begun
> The statements and opinions expressed here are my own and
> do not necessarily represent those of Oracle Corporation.
>

>>> boolean is not SQL datatype and it's unclear what "deterministic"
>>> means here.
>>> 
>>> Mladen Gogala wrote:
>>> 
>>>> create or replace function
>>>> isnumeric(str varchar2) return boolean deterministic
>>>> as
>>>>  num number:=0;
>>>> begin
>>>>  num:=to_number(str);
>>>>  return(true);
>>>> exception
>>>>  when others then
>>>> return(false);
>>>> end;
>>>> /

>
>

>--

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

>--

> Author: Vladimir Begun
> INET: Vladimir.Begun_at_oracle.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).

--
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Thu Sep 25 2003 - 21:39:36 CDT