Re: Datatype in stored functions

From: <Kenneth>
Date: Mon, 18 Aug 2003 19:27:24 GMT
Message-ID: <3f412898.3127386@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 Mon Aug 18 2003 - 14:27:24 CDT