Skip to Main Content
  • Questions

  • Encountering (ora-12015: cannot create a fast refresh materialized view from a complex query) when using user defined funtion in dbms_redefinition package in oracle

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vikas.

Asked: June 14, 2016 - 6:16 am UTC

Last updated: June 15, 2016 - 3:37 pm UTC

Version: 12.1.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to redefine a table using the dbms_redefinition package in oracle 12c. objective is to partition the original table and add couple of new columns(ILM_DT, ILM_ARCH_SW ) . I am able to populate the data in ILM_ARCH_SW with a default value but i am encountering issue (ora-12015) when i try to use a user defined function (GET_TXN_CALC_ILM_DT) to populate ILM_DT column. Below are the table and error details .
Kindly help. Thank you in advance.

The original table

CREATE TABLE CI_TXN_CALC
( "TXN_CALC_ID" NUMBER(22,0) NOT NULL ENABLE,
"TXN_DETAIL_ID" NUMBER(15,0) NOT NULL ENABLE,
"SEQ_NO" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"START_DT" DATE NOT NULL ENABLE,
"CURRENCY_CD" CHAR(3 BYTE) NOT NULL ENABLE,
"END_DT" DATE NOT NULL ENABLE,
"RS_CD" CHAR(8 BYTE) NOT NULL ENABLE,
"EFFDT" DATE,
"CALC_AMT" NUMBER(15,2) NOT NULL ENABLE,
"DESCR_ON_BILL" VARCHAR2(80 BYTE) NOT NULL ENABLE,
"VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"ACCUMULATION_SW" CHAR(1 BYTE) DEFAULT 'N',
"ACCT_ID" CHAR(10 BYTE) NOT NULL ENABLE,
"INIT_PRICEITEM_CD" CHAR(30 BYTE) DEFAULT ' ',
"TOU_CD" CHAR(8 BYTE) DEFAULT ' ',
"PRICEITEM_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1 NOT NULL ENABLE,
"AGG_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1,
"ORG_TXN_DATE" DATE,
"DER_DIVISION" CHAR(5 BYTE) NOT NULL ENABLE,
"TXN_RATING_CRITERIA" CHAR(4 BYTE),
PRIMARY KEY ("TXN_CALC_ID")
);

Insert statements for creating data in the original table


Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226591931,1957494492,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9756000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030218835446,1957494493,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1240106101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226592203,1957494494,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9756006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030221997328,1957494495,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1241000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226692615,1957494496,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9757000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030227399735,1957494497,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1241106101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226692628,1957494498,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9757006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030221997368,1957494499,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1242000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226692299,1957494500,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9758000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030225673984,1957494501,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1242006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226692214,1957494502,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9758006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030233014818,1957494503,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1242106101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030230056846,1957494504,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9759000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030238518950,1957494505,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1243000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226894379,1957494506,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9759006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030222714787,1957494507,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1243006101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226692227,1957494508,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9760000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030238316859,1957494509,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1243106101','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030226591820,1957494510,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','9760100001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');
Insert into CI_TXN_CALC (TXN_CALC_ID,TXN_DETAIL_ID,SEQ_NO,START_DT,CURRENCY_CD,END_DT,RS_CD,EFFDT,CALC_AMT,DESCR_ON_BILL,VERSION,ACCUMULATION_SW,ACCT_ID,INIT_PRICEITEM_CD,TOU_CD,PRICEITEM_PARM_GRP_ID,AGG_PARM_GRP_ID,ORG_TXN_DATE,DER_DIVISION,TXN_RATING_CRITERIA) values (161540030232712502,1957494511,10,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'USD',to_timestamp('31-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'RITX_TST',to_timestamp('23-MAY-00','DD-MON-RR HH.MI.SSXFF AM'),50,'TESTING RITX',1,'N','1244000001','TEST1111 ',' ',161441105353500001,1,to_timestamp('01-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),'DIV01','RITA');


The intermediate table definition

CREATE TABLE INT_CI_TXN_CALC
(
"TXN_CALC_ID" NUMBER(22,0) NOT NULL ENABLE,
"TXN_DETAIL_ID" NUMBER(15,0) NOT NULL ENABLE,
"SEQ_NO" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"START_DT" DATE NOT NULL ENABLE,
"CURRENCY_CD" CHAR(3 BYTE) NOT NULL ENABLE,
"END_DT" DATE NOT NULL ENABLE,
"RS_CD" CHAR(8 BYTE) NOT NULL ENABLE,
"EFFDT" DATE,
"CALC_AMT" NUMBER(15,2) NOT NULL ENABLE,
"DESCR_ON_BILL" VARCHAR2(80 BYTE) NOT NULL ENABLE,
"VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"ACCUMULATION_SW" CHAR(1 BYTE) DEFAULT 'N',
"ACCT_ID" CHAR(10 BYTE) NOT NULL ENABLE,
"INIT_PRICEITEM_CD" CHAR(30 BYTE) DEFAULT ' ',
"TOU_CD" CHAR(8 BYTE) DEFAULT ' ',
"PRICEITEM_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1 NOT NULL ENABLE,
"AGG_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1,
"ORG_TXN_DATE" DATE,
"DER_DIVISION" CHAR(5 BYTE) NOT NULL ENABLE,
"TXN_RATING_CRITERIA" CHAR(4 BYTE),
"ILM_DT" DATE,
ILM_ARCH_SW CHAR(1) DEFAULT 'N'
)
PARTITION BY RANGE ("ILM_DT") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "P0" VALUES LESS THAN (TO_DATE('2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

Sample user defined function

create or replace function GET_TXN_CALC_ILM_DT(TXN_CALC_ID number)
return date as v_dt date;


begin
v_dt := trunc(sysdate);
return v_dt;
EXCEPTION
WHEN OTHERS THEN
RAISE;
end;

starting redefinition process

DECLARE

l_num_errors PLS_INTEGER;


begin


--------START REDEFINITION

DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>sys_context('USERENV', 'SESSION_USER'), ORIG_TABLE=>'CI_TXN_CALC',INT_TABLE=>'INT_CI_TXN_CALC',
COL_MAPPING=> 'TXN_CALC_ID TXN_CALC_ID ,
TXN_DETAIL_ID TXN_DETAIL_ID ,
SEQ_NO SEQ_NO ,
START_DT START_DT ,
CURRENCY_CD CURRENCY_CD ,
END_DT END_DT ,
RS_CD RS_CD ,
EFFDT EFFDT ,
CALC_AMT CALC_AMT ,
DESCR_ON_BILL DESCR_ON_BILL ,
VERSION VERSION ,
ACCUMULATION_SW ACCUMULATION_SW ,
ACCT_ID ACCT_ID ,
INIT_PRICEITEM_CD INIT_PRICEITEM_CD ,
TOU_CD TOU_CD ,
PRICEITEM_PARM_GRP_ID PRICEITEM_PARM_GRP_ID ,
AGG_PARM_GRP_ID AGG_PARM_GRP_ID ,
ORG_TXN_DATE ORG_TXN_DATE ,
DER_DIVISION DER_DIVISION ,
TXN_RATING_CRITERIA TXN_RATING_CRITERIA ,
GET_TXN_CALC_ILM_DT(TXN_CALC_ID) ILM_DT,
''N'' ILM_ARCH_SW'
, OPTIONS_FLAG=>DBMS_REDEFINITION.cons_use_rowid, PART_NAME=>null);

DBMS_OUTPUT.PUT_LINE ('CI_TXN_CALC : START_REDEF_TABLE done');

/* i have not included the further steps of COPY_TABLE_DEPENDENTS,FINISH_REDEF_TABLE as i am encountering the issue in this step */

end;

Finally, the error which i am encountering
ORA-42008: error occurred while instantiating the redefinition
ORA-12015: cannot create a fast refresh materialized view from a complex query



and Chris said...

Thanks for providing a complete test case! I can reproduce your error.

So why is this happening?

Online redefinition uses MV fast refresh technology in the background. So you must be able to make a fast refreshable MV from your query.

The problem is your function is not determinitic. Non-deterministic functions such as sysdate are not allowed in fast refreshable MVs.

You can overcome this by declaring your function as deterministic.

But this gives you a problem. A function using sysdate is non-deterministic. You can cheat by declaring it deterministic. But this may lead to unexpected results if it isn't (for the duration of your redefinition). 

create or replace function GET_TXN_CALC_ILM_DT (TXN_CALC_ID number) 
return date deterministic as v_dt date; 
begin 
v_dt := trunc(sysdate); 
return v_dt; 
end; 
/

DECLARE 

l_num_errors PLS_INTEGER; 

begin 

--------START REDEFINITION 

DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>sys_context('USERENV', 'SESSION_USER'), ORIG_TABLE=>'CI_TXN_CALC',INT_TABLE=>'INT_CI_TXN_CALC', 
COL_MAPPING=> 'TXN_CALC_ID TXN_CALC_ID , 
TXN_DETAIL_ID TXN_DETAIL_ID , 
SEQ_NO SEQ_NO , 
START_DT START_DT , 
CURRENCY_CD CURRENCY_CD , 
END_DT END_DT , 
RS_CD RS_CD , 
EFFDT EFFDT , 
CALC_AMT CALC_AMT , 
DESCR_ON_BILL DESCR_ON_BILL , 
VERSION VERSION , 
ACCUMULATION_SW ACCUMULATION_SW , 
ACCT_ID ACCT_ID , 
INIT_PRICEITEM_CD INIT_PRICEITEM_CD , 
TOU_CD TOU_CD , 
PRICEITEM_PARM_GRP_ID PRICEITEM_PARM_GRP_ID , 
AGG_PARM_GRP_ID AGG_PARM_GRP_ID , 
ORG_TXN_DATE ORG_TXN_DATE , 
DER_DIVISION DER_DIVISION , 
TXN_RATING_CRITERIA TXN_RATING_CRITERIA , 
GET_TXN_CALC_ILM_DT(TXN_CALC_ID) ILM_DT, 
''N'' ILM_ARCH_SW' 
, OPTIONS_FLAG=>DBMS_REDEFINITION.cons_use_rowid, PART_NAME=>null); 

DBMS_OUTPUT.PUT_LINE ('CI_TXN_CALC : START_REDEF_TABLE done'); 

/* i have not included the further steps of COPY_TABLE_DEPENDENTS,FINISH_REDEF_TABLE as i am encountering the issue in this step */ 

end; 
/
CI_TXN_CALC : START_REDEF_TABLE done


A better solution is to code your function so it's actually deterministic. For example, uses a fixed date instead of sysdate. Though obviously this depends on your requirements.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Appreciate the quick help. Thanks a ton !

Vikas, June 14, 2016 - 10:14 am UTC

Great! Thank you Chris. In my case most likely the actual function is going to be deterministic but i was testing with a sample non deterministic function. I was unaware about this issue.
Appreciate your quick response.

No luck :(

Vikas, June 15, 2016 - 10:37 am UTC

Hi Chris,

I tried to use a deterministic function with the given example
but i am still encountering the same error

Here is n my new function where i have replaced sysdate with static date

create or replace function GET_TXN_CALC_ILM_DT(TXN_CALC_ID number)
return date as v_dt date;


begin
v_dt := to_date('12-01-2015','dd-mm-yyyy');
return v_dt;
EXCEPTION
WHEN OTHERS THEN
RAISE;
end;
Chris Saxon
June 15, 2016 - 11:16 am UTC

Have you tried declaring it deterministic?

create or replace function GET_TXN_CALC_ILM_DT(TXN_CALC_ID number) 
return date deterministic as ...

Got it but one more followup question

Vikas, June 15, 2016 - 1:50 pm UTC

Ok..After i declared the function as deterministic it worked. I thought only by changing the return value from sysdate to a fixed date will solve the issue.

But can you also please let me know if there is any negative impact of declaring the function as deterministic as you said
"....You can cheat by declaring it deterministic. But this may lead to unexpected results if it isn't "

really i would appreciate if you could elaborate on this. thanks for the guidance.


Chris Saxon
June 15, 2016 - 2:06 pm UTC

One example:

The database uses the deterministic keyword to optimize function calls.

For example, the following function takes 1s to execute and returns sysdate. If you place this in the select clause, it's evaulated for each row. So you'll see different values for each:

create or replace function f
  return date as 
begin
  dbms_lock.sleep(1);
  return sysdate;
end;
/

select to_char(f, 'hh24:mi:ss') from dual
connect by level <= 5;

TO_CHAR(
--------
14:03:34
14:03:35
14:03:36
14:03:37
14:03:38
Elapsed: 00:00:05.243


But if you stick determinstic in the declaration, Oracle assumes it always returns the same value. So it only executes once:

create or replace function f
  return date deterministic as 
begin
  dbms_lock.sleep(1);
  return sysdate;
end;
/

select to_char(f, 'hh24:mi:ss') from dual
connect by level <= 5;

TO_CHAR(
--------
14:03:40
14:03:40
14:03:40
14:03:40
14:03:40
Elapsed: 00:00:01.241


All rows get the same value! So if you're function really is non-deterministic, you could get incorrect results...

Great ! and the final question for this thread

Vikas, June 15, 2016 - 2:24 pm UTC

Thanks a ton. In my case i am going to take a unique id ( TXN_CALC_ID column from the original table, which is being redefined) as a parameter to my deterministic function and based on that unique id(TXN_CALC_ID), i will return a date for same. the date might be same for multiple unique ids. for e.g.

TXN_CALC_ID DT_TO_RETURN
1 01jan2016
2 01jan2016
3 01jan2016
4 15mar2017
5 22dec1981

so i am assuming that i would be safe declaring the function as deterministic.
Chris Saxon
June 15, 2016 - 3:37 pm UTC

As long as for each input ID you expect the same result every time you call the function, then yes it's deterministic and you're good to go.

https://en.wikipedia.org/wiki/Deterministic_algorithm

Question what

Ghassan, June 15, 2016 - 4:13 pm UTC

What about default value equals sysdate in table definition ? Or as virtual col? Does this meet the need ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.