Re: Oracle NULL vs '' revisited

On Aug 20, 9:19 pm, Matthew Harrison <m.harris..._at_craznar.com> wrote:
> William Robertson wrote:
>
> > myproc()
>
> > and
>
> > myproc(NULL)
>
> more between
>
> myproc('')
>
> and
>
> myproc(NULL)
>
> However, myproc(NULL) never gets called ... only ... there is never the
> need to tell the system a message has not been sent.
>
> --
> Pinging self [127.0.0.1] with 32 bites of banana cake:
>
> Ping statistics for 127.0.0.1:
> Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),

To distinguish between null and '' parameter values, try something like this:

CREATE OR REPLACE PROCEDURE distinguish_emptiness

   ( p_msg VARCHAR2 )
AS

   v_msg CHAR(50) := p_msg;
BEGIN
   IF v_msg IS NULL THEN

      DBMS_OUTPUT.PUT_LINE('NULL');
   ELSE
      DBMS_OUTPUT.PUT_LINE('String with length ' || LENGTH(v_msg));    END IF;
END;
/

SQL> exec distinguish_emptiness(NULL);
NULL PL/SQL procedure successfully completed.

SQL> exec distinguish_emptiness('')
String with length 50

PL/SQL procedure successfully completed. Received on Tue Aug 21 2007 - 09:05:53 CDT