Re: Passing parameters to PL/SQL Procedure

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Tue, 07 Dec 1999 01:07:53 -0600
Message-ID: <384CB249.34A1DF84@ntsource.com>

You could check if the value of the parameter "is null" as in the example below.

The length of a null should be null. If a null input is tested for equality to the empty string, the result of the comparison should also be null. That is, neither true nor false.

SQL> create or replace procedure testnull (a in varchar2)   2 is

  3     b boolean;
  4     mesg varchar2(10);
  5  begin
  6     b := (a = '');
  7     if b is null then mesg := 'Null';
  8     elsif b = false then mesg := 'False';
  9     else mesg := 'True';

 10 end if;
 11 if a is null then

 12        dbms_output.put_line('It is null');
 13        dbms_output.put_line('The length of a null string is ' ||
to_char(length(a)));
 14        dbms_output.put_line('The value of a compared with an empty string

is ' || mesg);
 15 else
 16 dbms_output.put_line('It is not null');  17 end if;
 18 end;
 19 /

Procedure created.

SQL> exec testnull(null)
It is null
The length of a null string is
The value of a compared with an empty string is Null

PL/SQL procedure successfully completed.

Frank Hubeny
WExcel (http://www.wexcel.com)

Tim Kang wrote:

> Hi
>
> I am trying to replace the value of a parameter with '%' if nothing is
> returned. However, I am not sure what value is assigned to the parameter.
> I've tried to detect the value with LENGTH(). Also, I've checked to see if
> the value is an empty string (i.e. ''). Neither of these methods actually
> detects the passed parameter with no value.
>
> Thanks
Received on Tue Dec 07 1999 - 01:07:53 CST