In the following PLSQL trigger, , why is everything being casted into AS VARCHAR2 in the pivot code?

Question

I am trying to wrap my head around this trigger. I have trigger code here, that I'm studying:

 INSERT INTO ONLINE_PROFILE_HISTORY.ONLINE_ACCOUNT_AVH
      SELECT ONLINE_PROFILE_HISTORY.ONLINE_ACCOUNT_AVH_SEQ.NEXTVAL
                 ,tmpVar
                 ,O.ATTR_NM
                 ,O.ATTR_OLD_VLU
                 ,CAST(NULL AS CHAR) AS ATTR_NEW_VLU 
    FROM (SELECT :OLD.ONL_ACCT_ID 
                           ,CAST(:OLD.ONL_ACCT_INTL_ID AS VARCHAR2(1024)) ONL_ACCT_INTL_ID
                           ,CAST(:OLD.ONL_ACCT_EXT_ID AS VARCHAR2(1024)) AS ONL_ACCT_EXT_ID
                           ,CAST(:OLD.LCKED_TS AS VARCHAR2(1024)) AS LCKED_TS
                           ,CAST(:OLD.DISABLED_TS AS VARCHAR2(1024)) AS DISABLED_TS
                           ,CAST(:OLD.LST_SCSFL_LOGIN_TS AS VARCHAR2(1024)) AS LST_SCSFL_LOGIN_TS
                          /* etc etc*/ 
                           ,CAST(:OLD.VLDT_SCRT_QUES_FAILURE_CNT AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_FAILURE_CNT
                           ,CAST(:OLD.VLDT_SCRT_QUES_LST_FAILURE_TS AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_LST_FAILURE_TS
                           ,CAST(:OLD.VLDT_SCRT_QUES_SUCCESS_TS AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_SUCCESS_TS
                           ,CAST(:OLD.DLTD_TS AS VARCHAR2(1024)) AS DLTD_TS
                           ,CAST(:OLD.TRMS_CONDS_ACPTED_HOST_ADDR_NM AS VARCHAR2(1024)) AS TRMS_CONDS_ACPTED_HOST_ADDR_NM
                  FROM dual)
               UNPIVOT (ATTR_OLD_VLU FOR
                      ATTR_NM IN (
                      ONL_ACCT_INTL_ID
                      ,ONL_ACCT_EXT_ID
                      ,LCKED_TS
                      ,DISABLED_TS
                      ,VLDT_TOKEN_VLU
                      ,VLDT_TOKEN_KEY_NM
                      ,VLDTD_TS
                      ,VLDT_TOKEN_CREATE_TS
                      ,PRIM_BILLING_ACCT_SRC_ID
                      ,PRIM_BILLING_ACCT_SRC_ID_2
                      ,PRIM_BILLING_ACCT_SRC_SYS_CD
                      ,TRMS_CONDS_VER_NUM
                      ,TRMS_CONDS_ACPTED_TS
                      ,PSWD_FMT_NM
                      ,VLDT_SCRT_QUES_FAILURE_CNT
                      ,VLDT_SCRT_QUES_LST_FAILURE_TS
                      ,VLDT_SCRT_QUES_SUCCESS_TS
                      ,DLTD_TS
                      ,TRMS_CONDS_ACPTED_HOST_ADDR_NM
                     ))  O;   

Why is it that we are doing the

 CAST(:OLD.VLDTD_TS AS VARCHAR2(1024)) AS VLDTD_TS

part there? I generally understand how the pivot is working, but not sure why every column is casted like that.

Answer 1

UNPIVOT transforms the data that is spread across several columns into a single column. If the datatype of those columns in not same, it will result in error.

ORA-01790: expression must have same datatype as corresponding expression

SQL Fiddle

create table mytable(
    id_ number,
    col1_ varchar2(10),
    col2_ number,
    col3_ date
);

insert into mytable values(1, 'asd',32,date'2014-03-04');
insert into mytable values(2, 'qwe',16,date'2014-02-11');

select *
from mytable
unpivot(val_ for col_name in (
  col1_,col2_,col3_)
        );

ORA-01790: expression must have same datatype as corresponding expression : select * from mytable unpivot(val_ for col_name in ( col1_,col2_,col3_) )

That's why you should convert all the columns to same datatype before unpivoting.

SQL Fiddle

select *
from (
  select
  id_,
  col1_,
  to_char(col2_) col2_,
  to_char(col3_,'dd-mm-yyyy') col3_
  from mytable
  )
unpivot(
  val_ for col_name_ in (
    col1_, col2_, col3_
    )
  )

Results:

| ID_ | COL_NAME_ |       VAL_ |
|-----|-----------|------------|
|   1 |     COL1_ |        asd |
|   1 |     COL2_ |         32 |
|   1 |     COL3_ | 04-03-2014 |
|   2 |     COL1_ |        qwe |
|   2 |     COL2_ |         16 |
|   2 |     COL3_ | 11-02-2014 |