oracle11g - Oracle DB: Leading zeros are disappeared for newly added attribute in type

Question

I have added a attribute by altering the TYPE with char datatype for existing type in oracle DB as

ALTER TYPE MDPHEADEROBJ ADD ATTRIBUTE EMPNBR  char(10) CASCADE


TYPE       "MDPHEADEROBJ"                                          IS OBJECT (
   POSTXNDATE    DATE,
   LOCNBR        NUMBER (13),
   TRMNNBR       CHAR (3),
   POSTXNNBR     NUMBER (13),
   TOTSLSAMT     NUMBER (13, 2),
   VDDRGTXNIND   CHAR (1),
   TXNVDITMIND   CHAR (1),
   CSHRNBR       CHAR (6),
   SPRNNBR       CHAR (10),
   ITEMCNT       NUMBER,
   TAXCNT        NUMBER,
   TENDERCNT     NUMBER,
   DSCNTCNT      NUMBER
)

DDL of the table looks like

CREATE TABLE "POS"."SLS_TXN_T" 
   (    "SLS_TXN_MO_NBR" NUMBER(3,0) NOT NULL ENABLE, 
    "POS_TXN_TM_DT" DATE NOT NULL ENABLE, 
    "LOC_NBR" NUMBER(13,0) NOT NULL ENABLE, 
    "TRMN_NBR" CHAR(3) NOT NULL ENABLE, 
    "POS_TXN_NBR" NUMBER(13,0) NOT NULL ENABLE, 
    "PVOID_TXN_TM_DT" DATE, 
    "PVOID_LOC_NBR" NUMBER(13,0), 
    "PVOID_TRMN_NBR" CHAR(3), 
    "PVOID_POS_TXN_NBR" NUMBER(13,0), 
    "PVOID_RSN_CD" CHAR(2), 
    "TOT_SLS_AMT" NUMBER(13,2), 
    "VD_DRG_TXN_IND" CHAR(1), 
    "VD_ITM_IND" CHAR(1), 
    "CSHR_NBR" CHAR(6), 
    "SPRN_NBR" CHAR(10), 
    "SLS_UPDT_DT" DATE DEFAULT sysdate NOT NULL ENABLE, 
    "EMP_NBR" CHAR(10) DEFAULT null, 
     CONSTRAINT "TXN_T_LOC_NBR_CK" CHECK ("LOC_NBR" IS NOT NULL) ENABLE NOVALIDATE, 
     CONSTRAINT "TXN_T_TRMN_NBR_CK" CHECK ("TRMN_NBR" IS NOT NULL) ENABLE NOVALIDATE, 
     CONSTRAINT "TXN_T_TXN_NBR_CK" CHECK ("POS_TXN_NBR" IS NOT NULL) ENABLE NOVALIDATE, 
     CONSTRAINT "TXN_T_TXN_TM_DT_CK" CHECK ("POS_TXN_TM_DT" IS NOT NULL) ENABLE NOVALIDATE
   )

Store proc select query looks like

SELECT pos_txn_tm_dt, loc_nbr, trmn_nbr, pos_txn_nbr, tot_sls_amt,
             vd_drg_txn_ind, vd_itm_ind, cshr_nbr , sprn_nbr, emp_nbr
        
        FROM SLS_TXN_T
       WHERE 
          loc_nbr = '5548'
         AND trmn_nbr = '060'
         AND pos_txn_nbr = '261'
         AND (vd_drg_txn_ind IS NULL OR vd_drg_txn_ind <> 'Y');

sample data how it looks int the table

CSHNBR SPRNNBR      EMPNBR
000000  0000        01234567  

Here I've a quick question:

From the above fields we many char declared variables CSHRNBR, SPRNNBR, EMPNBR etc., For Example: if CSHRNBR is saved as 0012356 while fetching the data through store proc I'm able to get the actual value from the DB as same 0012356. But for the newly added attribute in type empNbr if the value is saved as 0012356 while fetching it is coming as 12356 leading zero's are disappeared.

Looks strange both are declared with same datatype char. Please help me why this is happening?

question from:https://stackoverflow.com/questions/65846810/oracle-db-leading-zeros-are-disappeared-for-newly-added-attribute-in-type

Answer 1

Without more information about what you are doing/seeing and what errors are being produced we can not provide much more assistance than whats currently in the comments. Running a test that I believe to be close to what you are doing does not show the problem mentioned:

SQL> drop table ttch purge;

Table TTCH dropped.

SQL> drop type tch;

Type TCH dropped.

SQL> /
SQL> create or replace type tch as object (empnbr number, empchr char(5));
2 /

Type TCH compiled

SQL> 
SQL> create table ttch (empl tch);

Table TTCH created.

SQL> 
SQL> insert into ttch values (tch(1,'00001'));

1 row inserted.

SQL> 
SQL> select t.empl.empnbr, t.empl.empchr from ttch t;

EMPL.EMPNBR EMPL.
----------- -----
1           00001

SQL> 
SQL> alter type tch add attribute nemp char(4) cascade;

Type TCH altered.

SQL> 
SQL> insert into ttch values (tch(2,'00002','0020'));

1 row inserted.

SQL> 
SQL> 
SQL> select t.empl.empnbr, t.empl.empchr, t.empl.nemp from ttch t;

EMPL.EMPNBR EMPL. EMPL
----------- ----- ----
1           00001 
2           00002 0020