Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in Null and Empty String
icon5.gif  Doubt in Null and Empty String [message #249634] Thu, 05 July 2007 07:16 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
I'm running 10.2.0.2. Note the difference in behavior between passing and explicit NULL in the parameter vice passing an empty string (''):

CREATE OR REPLACE PROCEDURE NULL_ES_TEST(PARAM1 IN VARCHAR2) IS

VAR1 CHAR(1);

BEGIN

IF PARAM1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('PARAM1 IS NULL');
END IF;

VAR1 := PARAM1;

IF VAR1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('VAR1 IS NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('VAR1 IS NOT NULL');
END IF;

DBMS_OUTPUT.PUT_LINE('THE LENGTH OF VAR1 IS '||TO_CHAR(LENGTH(VAR1)));

END NULL_ES_TEST;

EXEC NULL_ES_TEST(PARAM1 => '');

PARAM1 IS NULL
VAR1 IS NOT NULL
THE LENGTH OF VAR1 IS 1

(var1 now holds a single space)

EXEC NULL_ES_TEST(PARAM1 => NULL);

PARAM1 IS NULL
VAR1 IS NULL
THE LENGTH OF VAR1 IS

Any Comments or Insights are welcome.
Re: Doubt in Null and Empty String [message #249635 is a reply to message #249634] Thu, 05 July 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any Comments or Insights are welcome.

Why don't you format you post?

Regards
Michel

Re: Doubt in Null and Empty String [message #249636 is a reply to message #249634] Thu, 05 July 2007 07:23 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

This is the basic difference between CHAR and VARCHAR2.
If you use CHAR to define any variable then it always shows the define length of that variable.
VAR1 CHAR(1);

Now try to declare the variable using VARCHAR2 then it will give you the correct answer.
VAR1 VARCHAR2(1);


Regards
Sanka

[Updated on: Thu, 05 July 2007 07:24]

Report message to a moderator

Re: Doubt in Null and Empty String [message #250267 is a reply to message #249636] Mon, 09 July 2007 06:28 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Tom Kyte addresses the same subject here.

MHE
Previous Topic: after delete, how we can "release space" like truncate
Next Topic: I want to get output of my procedure which is a string in Bold Can any one can help me
Goto Forum:
  


Current Time: Thu Dec 08 00:31:31 CST 2016

Total time taken to generate the page: 0.11357 seconds