Char vs Varchar2 and NULL

From: <stevec_at_zimmer.csufresno.edu>
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

Original text of this message