Hi Tom,

I'm exploring updating some very large tables using Create Table As Select (CTAS). Each of the tables has a field that I wish to add a '00' before the existing character value (we're increasing our store number range from three digits to five digits and want them to sort correctly...). When I use code to "pre-pend" the '00', it is changing the format of the column from CHAR to VARCHAR2. I suspect this might cause issues with indexing and other efficiency down the road.
Here's the SQL for the CTAS:

create table
fivedig_scp_drp_msg
nologging as
select /*+ full parallel */
item_id, '00' || substr(loc_id,1,6) as loc_id, seq_nbr,
drp_msg_nbr, scp_seq_nbr, drp_msg_qty, drp_msg_due_date, drp_msg_ref_id,
drp_msg_mfg_type, drp_msg_frm_date, drp_msg_frm_qty, drp_msg_proc_ind,
drp_msg_plnr_id
from scp_drp_msg;

See below and please notice that the LOC_ID column of new table is an 8 byte VARCHAR2 when in the original table, it was a CHAR field. Is there a way to force the new field to be a CHAR? If so, what is the syntax, please. Also, the "NOT NULL" parm is missing. Probably need to fix that too?

Here's the code for original table creation (as reported by TOAD):
CREATE TABLE LOGILITY.SCP_DRP_MSG
(
ITEM_ID CHAR(18 BYTE) NOT NULL,
LOC_ID CHAR(8 BYTE) NOT NULL,
SEQ_NBR NUMBER(3) NOT NULL,
DRP_MSG_NBR NUMBER(2) NOT NULL,
SCP_SEQ_NBR NUMBER(9) NOT NULL,
DRP_MSG_QTY NUMBER(9) NOT NULL,
DRP_MSG_DUE_DATE DATE NOT NULL,
DRP_MSG_REF_ID VARCHAR2(16 BYTE) DEFAULT ' ',
DRP_MSG_MFG_TYPE CHAR(1 BYTE) DEFAULT ' ',
DRP_MSG_FRM_DATE DATE NOT NULL,
DRP_MSG_FRM_QTY NUMBER(9) NOT NULL,
DRP_MSG_PROC_IND CHAR(1 BYTE) DEFAULT ' ',
DRP_MSG_PLNR_ID VARCHAR2(10 BYTE) DEFAULT ' '

Here's the code for new table creation (as reported by TOAD):
CREATE TABLE LOGILITY.FIVEDIG_SCP_DRP_MSG
(
ITEM_ID CHAR(18 BYTE) NOT NULL,
LOC_ID VARCHAR2(8 BYTE),
SEQ_NBR NUMBER(3) NOT NULL,
DRP_MSG_NBR NUMBER(2) NOT NULL,
SCP_SEQ_NBR NUMBER(9) NOT NULL,
DRP_MSG_QTY NUMBER(9) NOT NULL,
DRP_MSG_DUE_DATE DATE NOT NULL,
DRP_MSG_REF_ID VARCHAR2(16 BYTE),
DRP_MSG_MFG_TYPE CHAR(1 BYTE),
DRP_MSG_FRM_DATE DATE NOT NULL,
DRP_MSG_FRM_QTY NUMBER(9) NOT NULL,
DRP_MSG_PROC_IND CHAR(1 BYTE),
DRP_MSG_PLNR_ID VARCHAR2(10 BYTE)
)

SQL> create table t ( x char(10), y number(4) );

Table created.

SQL> insert into t values ('x',0);

1 row created.

SQL>
SQL> create table t1
  2  as select * from t;

Table created.

SQL>
SQL> desc t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      CHAR(10)
 Y                                      NUMBER(4)

SQL>
SQL> create table t2
  2  as select rtrim(x)||'suffix' x, y+50 y from t;

Table created.

SQL>
SQL> desc t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      VARCHAR2(16)
 Y                                      NUMBER

SQL>
SQL> create table t3
  2  as select cast(rtrim(x)||'suffix' as char(10)) x, cast(y+50 as number(4)) y from t;

Table created.

SQL>
SQL> desc t3
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      CHAR(10)
 Y                                      NUMBER(4)

SQL>
SQL>