Char vs Varchar2 and NULL
Date: 1998/09/08
Message-ID: <6t3l9u$nvd$1_at_nnrp1.dejanews.com>#1/1
I found a little wrinkle in PL/SQL. When you set the value of a CHAR field from a VARCHAR2 field, you get different results depending on how the VARCHAR2 field was set. Try the following in SQL Plus:
Set serveroutput on size 20000
Declare
V Varchar2(3);
C Char(3);
Begin
V := Null;
C := V;
[Quoted] DBMS_Output.Put_Line
('Char = |' || C || '| (Set from NULL varchar2');
V := '';
C := V;
DBMS_Output.Put_Line
('Char = |' || C || '| (Set from zero-length varchar2');
End;
/
The output looks like this:
Char = || (Set from NULL varchar2
Char = | | (Set from zero-length varchar2
The second value is three spaces!
Apparently, there is a difference between NULL and Zero-Length values in Varchar2 fields.
The problem also shows up when setting a CHAR field to the results of RTRIM, LTRIM and SUBSTR functions. If these functions return a null (actually zero-length) string, the CHAR variable is set to all spaces, and not null as you might expect.
This problem only shows up within PL/SQL variables. If you use SQL to set the values of a CHAR database column to a zero-length string, the column is correctly set to null, and not all spaces.
So beware!
Steve Cosner
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Sep 08 1998 - 00:00:00 CEST