Oracle-L: Re: V7 VARCHAR2/CHAR usage

From: Nick Price <nick_at_INNET.BE>
Date: Tue, 14 Mar 1995 08:50:50 +0000
Message-Id: <9503141220.AA30735@alice.jcc.com>

According to Reinhold Broeker:
>
> Hello,
>
> up to ORACLE V6 there was only a variable-length string datatype (CHAR). Now
> there is VARCHAR2 and CHAR. For known fixed-length values this might be an
> improvement in disk usage. On the other hand I now have to consider
> different comparison rules, etc. I was pretty satisfied with one string
> type. Is it worth using two?
> Any opinions or experiences?
>
> Thanks,
> Reinhold Broeker, Frankfurt/Germany
> reinhold_at_f-1.de.contrib.net
>
>

I came accross a strange situation a few days ago concerning CHAR/VARCHAR2 in the SQL*Menu 5 table MENU_B_APPL.

A friend called me up to say that he had problems building a menu on a newly installed Oracle system. It seemed that the character strings in MENU_B_APPL were being padded to a fixed length. This caused errors in the menu build.

On my system (v7.0.16/UNIX) if I DESCRIBE this table I get:

SQL> describe menu_b_appl

 Name                            Null?    Type
 ------------------------------- -------- ----
 APPLICATION_NAME                NOT NULL VARCHAR2(30)
 SHORT_NAME                      NOT NULL VARCHAR2(15)
 FILE_NAME                       NOT NULL VARCHAR2(30)
 CREATION_DATE                   NOT NULL DATE
 CREATOR                         NOT NULL VARCHAR2(30)
 VERSION_RELEASE_NR              NOT NULL NUMBER
 LAST_RELEASE_DATE                        DATE
 MENU_DIRECTORY                           VARCHAR2(50)
 IDENTIFICATION                  NOT NULL VARCHAR2(40)

I DESCRIBED his MENU_B_APPL. All the VARCHAR2's were CHARS. This seemed like the problem. I looked in $ORACLE_HOME/menu5/admin/menutabs.sql expecting to find VARCHAR2's, thinking that someone had used a different script from a previous Version 6 Oracle. But here on my 7.0.16 system I found:

CREATE TABLE MENU_B_APPL (

 APPLICATION_NAME                CHAR(30)  NOT NULL,
 SHORT_NAME                      CHAR(15)  NOT NULL,
 FILE_NAME                       CHAR(30)  NOT NULL,
 CREATION_DATE                   DATE      NOT NULL,
 CREATOR                         CHAR(30)  NOT NULL,
 VERSION_RELEASE_NR              NUMBER    NOT NULL,
 LAST_RELEASE_DATE               DATE,
 MENU_DIRECTORY                  CHAR(50),
 IDENTIFICATION                  CHAR(40)  NOT NULL );

When I create a new database by hand on my system, I always add product database objects through the ORACLE Installer. My friend, however, had manually run the Menu 5 SQL scripts.

So, does the installer use a different set of scripts with VARCHAR2's, or does it manage to convert a CHAR to a VARCHAR2 ?

Nick
--

+==============================================================================+
+    Nick Price                                           Avenue Coghen 169    +
+    Internet: nprice_at_innet.be                            1180 Brussels        +
+    Tel/Fax : +32 2 347 1101                             Belgium              +
+==============================================================================+

Received on Tue Mar 14 1995 - 07:20:33 CST