Re: Datatype in stored functions

Implicit conversions will cause problems sooner or later. Guaranteed.
What date is "01/02/03"? 1st Feb 2003? 2nd Jan 2003? Or is it 1903? Is "0123456789" my phone number or my salary? Is 123,456,789 a list of three 3-digit numbers or a single 9-digit number? (Or in some countries, a badly-formed number?)

Even without this approach, you may find that oracle tries to use the wrong function and an implicit conversion rather than the correct function (I found this in 8.0.? a while ago and spent ages working round it). In my case I was calling the NUMBER function from within the VARCHAR2 function and oracle wrongly called the VARCHAR2 one with an implicit conversion. Be careful with anything approaching recursion with overloaded functions and procedures.

The only thing to do is a big load of testing to cover all eventualities!

Good luck.

ETA Kenneth Koenraadt wrote in message news:<3f412898.3127386_at_news.inet.tele.dk>...
> On Mon, 18 Aug 2003 15:38:29 +0200, "Hans Sauren"
> <hsauren_at_gruen-ag.de> wrote:
>
> >Hi all,
> >we have to write a stored function which tests whether a value is NULL or 0
> >or spaces in charfields. We want to give the function a parameter with the
> >content of the field (or possibly the result of a term). Therefore the
> >datatype of the parameter is undetermined or like SQL_VARIANT in Microsoft
> >SQL Server). How can we implement this in ORACLE 8.1.7?
> >
> >Thanks for any suggestion
> >Hans Sauren
> >
> >
>
> Hi Hans,
>
> Nuno has already shown you the correct and safest way to do it, but :
>
> You *could* also take advantage of Oracle's abilities regarding
> implicit conversions :
>
> SQL> create or replace function nullcheck(par varchar2) return boolean
> 2 is
> 3 begin
> 4
> 5 return (par is null);
> 6
> 7 end nullcheck;
> 8 /
>
> Funktion er oprettet.
>
> SQL>
> SQL> set serveroutput on size 1000000;
> SQL> begin
> 2 -- Checking for different datatypes
> 3
> 4 if nullcheck('as') then
> 5 dbms_output.put_line('as is null');
> 6 end if;
> 7
> 8 if nullcheck(1) then
> 9 dbms_output.put_line('1 is null');
> 10 end if;
> 11
> 12 if nullcheck(null) then
> 13 dbms_output.put_line('only null is null');
> 14 end if;
> 15
> 16
> 17 end;
> 18 /
>
> only null is null
>
>
>
> - Kenneth Koenraadt
Received on Wed Aug 20 2003 - 05:12:44 CDT