Re: varchar2 behaviour

In article <56j4od$d0m_at_newton.pacific.net.sg>,

   N Prabhakar <prabhs_at_po.pacific.net.sg> wrote:
>Werner Menges <wernerm_at_hpbbn.bbn.hp.com> wrote:
>>Hello erverybody,
>>
>> I want to insert or update an emtpy string into a column defined
>> as varchar2(n) ( Oracle 7.1.6 or 7.2.3 ).
>> If there is no NOT NULL constraint then it works fine (insert/update)
>> but if I execute a query the db returns a null value for this column.
>>
>> In our application we have to distinguish between null and ''
>> therefore I should have a solution to get back an empty string.

Sorry. An empty string is a NULL to Oracle.

>> We use the C-interface from Oracle ( Pro*C/C++ 2.1 ).
 

>To my knowledge, the VARCHAR2 datatype does not entertain trailing blanks
>in a string. Ie. if you have a value "employee name ", varchar2 datatype
>stores as "employee name".

Wrong! Forms truncates trailing blanks, but the database does not! It is stored as "employee name " - with the trailing blank! Your Pro*C program may be written so as to truncate it, but the database does not. SQL*Forms/OracleForms does truncate it before sending it to the database.

>To avoid this, you can change the datatype of the field as CHAR.

This has absolutely no bearing on the issue.

>I hope the CHAR datatype can differentiate between NULL and BLANKS.

It does. So does VARCHAR2. The difference is that char is fixed length (ie. it is padded out with blanks and stored that way. Varchar2 does not.

Try this is sqlplus: (*NOT* Pro*C or Forms!)

create table junk (x varchar2(10) not null); insert into junk values (' ');
select '''' || x || '''' from junk;

See? OK Now try it with a char(10). See the difference? Received on Wed Nov 20 1996 - 00:00:00 CET