Re: Oracle NULL vs '' revisited

On Aug 20, 9:19 pm, Matthew Harrison <m.harris...@xxxxxxxxxxx> 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.


.

Relevant Pages