Swadhin Ray
asked on
ORA-30354: Query rewrite not allowed on SYS relations
Hello Experts,
I am getting the below error while creating the :
I tried with altering the session but still the same error:
I am getting the below error while creating the :
CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE AS
select CHD_DETAIL_ID,
SOURCE_ID,
SOURCE_NAME,
FIELD_NAME,
BEFORE_VALUE,
AFTER_VALUE,
CHD_LAST_MODIFIED_ON,
CHD_LAST_MODIFIED_BY
from
(SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
D.SOURCE_NAME,
SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
||H.OBJECT_ID
||' - '
||H.SOURCE_ID
||']', 'MS_SRA_VISIT_SETUP',' ['
||H.OBJECT_ID
||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
||H.OBJECT_ID
||']', 'MS_SRA_FACILITY_INFO',' ['
||H.OBJECT_ID
||']', 'MS_SRA_PROTOCOL_SETUP',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_PROTOCOL_SETUP_FLV',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_QUESTION_SETUP',' ['
||H.OBJECT_ID
||']','MS_SRA_VISIT_SETUP_ASN',
(SELECT ' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
||']'
FROM DUAL
) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - '
||QST_CODE
||']',2,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile - '
||QST_QUESTION
||']',3,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile General - '
||QST_QUESTION
||']',NULL),'FINDING_ID',' ['
||F.FINDING_ID
||' - '
||F.FND_NAME
||']',NULL))),1,200) AS FIELD_NAME,
D.BEFORE_VALUE,
D.AFTER_VALUE,
D.CHD_LAST_MODIFIED_ON,
D.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
MS_SRA_CHANGE_HIS_HEADER_RPT H,
(SELECT VISIT_ID,
QUESTION_ID,
QST_CODE,
QST_QUESTION,
QST_SHORT_TEXT,
QST_TYPE
FROM MS_SRA_VISIT_QUESTIONS
WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
) Q,
MS_SRA_VISIT_FINDINGS F,
MS_SRA_QUESTION_SETUP_KEY K,
MS_SRA_CHANGE_HIS_COL_LOOKUP L
WHERE H.SOURCE_NAME = D.SOURCE_NAME
and H.SOURCE_ID = D.SOURCE_ID
AND (((H.OBJECT_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
and H.SOURCE_NAME not in ('Question Setup','Protocol Setup'))
OR H.OBJECT_ID = D.SOURCE_ID)
AND H.SOURCE_ID = F.FINDING_ID(+)
AND H.SOURCE_ID = TO_CHAR(Q.QUESTION_ID(+))
AND H.OBJECT_ID = Q.VISIT_ID(+)
AND H.OBJECT_ID = F.VISIT_ID(+)
AND D.SOURCE_ID = TO_CHAR(K.QUESTION_ID(+))
AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME) >0
AND D.SOURCE_NAME = L.FORM_NAME
AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
OR (D.SOURCE_NAME ='MS_SRA_VISIT_SETUP'
AND D.FIELD_NAME != 'VISIT ID'))
union all
SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
DET.SOURCE_NAME,
DET.FIELD_NAME,
DET.BEFORE_VALUE,
DET.AFTER_VALUE,
DET.CHD_LAST_MODIFIED_ON,
DET.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
MS_SRA_CHANGE_HIS_HEADER_RPT HED
WHERE HED.SOURCE_ID = DET.SOURCE_ID
AND HED.SOURCE_NAME = DET.SOURCE_NAME
and HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
);
Error at Command Line:79 Column:10
Error report:
SQL Error: ORA-30354: Query rewrite not allowed on SYS relations
30354. 00000 - "Query rewrite not allowed on SYS relations"
*Cause: A SYS relation was referenced in the select clause for a
materialized view with query rewrite enabled.
I tried with altering the session but still the same error:
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=trusted;
Remove DUAL from the query.
Here is what you need to read:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF54181
Here is what you need to read:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF54181
ASKER
Removed dual but seems function cannot be used :
CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE AS
select CHD_DETAIL_ID,
SOURCE_ID,
SOURCE_NAME,
FIELD_NAME,
BEFORE_VALUE,
AFTER_VALUE,
CHD_LAST_MODIFIED_ON,
CHD_LAST_MODIFIED_BY
from
(SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
D.SOURCE_NAME,
SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
||H.OBJECT_ID
||' - '
||H.SOURCE_ID
||']', 'MS_SRA_VISIT_SETUP',' ['
||H.OBJECT_ID
||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
||H.OBJECT_ID
||']', 'MS_SRA_FACILITY_INFO',' ['
||H.OBJECT_ID
||']', 'MS_SRA_PROTOCOL_SETUP',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_PROTOCOL_SETUP_FLV',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_QUESTION_SETUP',' ['
||H.OBJECT_ID
||']','MS_SRA_VISIT_SETUP_ASN',
(--SELECT
' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
||']'
--FROM DUAL
) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - '
||QST_CODE
||']',2,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile - '
||QST_QUESTION
||']',3,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile General - '
||QST_QUESTION
||']',NULL),'FINDING_ID',' ['
||F.FINDING_ID
||' - '
||F.FND_NAME
||']',NULL))),1,200) AS FIELD_NAME,
D.BEFORE_VALUE,
D.AFTER_VALUE,
D.CHD_LAST_MODIFIED_ON,
D.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
MS_SRA_CHANGE_HIS_HEADER_RPT H,
(SELECT VISIT_ID,
QUESTION_ID,
QST_CODE,
QST_QUESTION,
QST_SHORT_TEXT,
QST_TYPE
FROM MS_SRA_VISIT_QUESTIONS
WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
) Q,
MS_SRA_VISIT_FINDINGS F,
MS_SRA_QUESTION_SETUP_KEY K,
MS_SRA_CHANGE_HIS_COL_LOOKUP L
WHERE H.SOURCE_NAME = D.SOURCE_NAME
and H.SOURCE_ID = D.SOURCE_ID
AND (((H.OBJECT_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
and H.SOURCE_NAME not in ('Question Setup','Protocol Setup'))
OR H.OBJECT_ID = D.SOURCE_ID)
AND H.SOURCE_ID = F.FINDING_ID(+)
AND H.SOURCE_ID = TO_CHAR(Q.QUESTION_ID(+))
AND H.OBJECT_ID = Q.VISIT_ID(+)
AND H.OBJECT_ID = F.VISIT_ID(+)
AND D.SOURCE_ID = TO_CHAR(K.QUESTION_ID(+))
AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME) >0
AND D.SOURCE_NAME = L.FORM_NAME
AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
OR (D.SOURCE_NAME ='MS_SRA_VISIT_SETUP'
AND D.FIELD_NAME != 'VISIT ID'))
union all
SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
DET.SOURCE_NAME,
DET.FIELD_NAME,
DET.BEFORE_VALUE,
DET.AFTER_VALUE,
DET.CHD_LAST_MODIFIED_ON,
DET.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
MS_SRA_CHANGE_HIS_HEADER_RPT HED
WHERE HED.SOURCE_ID = DET.SOURCE_ID
AND HED.SOURCE_NAME = DET.SOURCE_NAME
AND HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
);
Error at Command Line:69 Column:27
Error report:
SQL Error: ORA-30357: this PL/SQL function cannot be supported for query rewrite
30357. 00000 - "this PL/SQL function cannot be supported for query rewrite"
*Cause: The statement referenced a PL/SQL function that is not marked
DETERMINISTIC.
*Action: Perform one of the following actions
- Remove the use of the PL/SQL function.
- Mark the PL/SQL function as DETERMINISTIC.
- Disable the REWRITE option on the materialized view.
The function should be marked DETERMINISTIC only if it always
returns the same result value for any given set of input argument
values, regardless of any database state or session state.
Do not mark the function as DETERMINISTIC if it has any
meaningful side-effects.
Remove the query rewrite from line #3?
Recompile your user-defined function "MS_APPS_UTILITIES.GET_USE R_FULL_NAM E" as DETERMINISTIC.
Read more here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1580
Read more here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1580
ASKER
Changed my functions to deterministic but still inline view having a problem to create :
CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE AS
select CHD_DETAIL_ID,
SOURCE_ID,
SOURCE_NAME,
FIELD_NAME,
BEFORE_VALUE,
AFTER_VALUE,
CHD_LAST_MODIFIED_ON,
CHD_LAST_MODIFIED_BY
from
(SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
D.SOURCE_NAME,
SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
||H.OBJECT_ID
||' - '
||H.SOURCE_ID
||']', 'MS_SRA_VISIT_SETUP',' ['
||H.OBJECT_ID
||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
||H.OBJECT_ID
||']', 'MS_SRA_FACILITY_INFO',' ['
||H.OBJECT_ID
||']', 'MS_SRA_PROTOCOL_SETUP',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_PROTOCOL_SETUP_FLV',
(SELECT ' ['
||PROTOCOL_NAME
||']'
FROM MS_SRA_PROTOCOL_SETUP
WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
),'MS_SRA_QUESTION_SETUP',' ['
||H.OBJECT_ID
||']','MS_SRA_VISIT_SETUP_ASN',
(--SELECT
' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
||']'
--FROM DUAL
) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - '
||QST_CODE
||']',2,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile - '
||QST_QUESTION
||']',3,' ['
||H.OBJECT_ID
||' - '
||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
||' - Facility Profile General - '
||QST_QUESTION
||']',NULL),'FINDING_ID',' ['
||F.FINDING_ID
||' - '
||F.FND_NAME
||']',NULL))),1,200) AS FIELD_NAME,
D.BEFORE_VALUE,
D.AFTER_VALUE,
D.CHD_LAST_MODIFIED_ON,
D.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
MS_SRA_CHANGE_HIS_HEADER_RPT H,
(SELECT VISIT_ID,
QUESTION_ID,
QST_CODE,
QST_QUESTION,
QST_SHORT_TEXT,
QST_TYPE
FROM MS_SRA_VISIT_QUESTIONS
WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
) Q,
MS_SRA_VISIT_FINDINGS F,
MS_SRA_QUESTION_SETUP_KEY K,
MS_SRA_CHANGE_HIS_COL_LOOKUP L
WHERE H.SOURCE_NAME = D.SOURCE_NAME
and H.SOURCE_ID = D.SOURCE_ID
AND (((H.OBJECT_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
and H.SOURCE_NAME not in ('Question Setup','Protocol Setup'))
OR H.OBJECT_ID = D.SOURCE_ID)
AND H.SOURCE_ID = F.FINDING_ID(+)
AND H.SOURCE_ID = TO_CHAR(Q.QUESTION_ID(+))
AND H.OBJECT_ID = Q.VISIT_ID(+)
AND H.OBJECT_ID = F.VISIT_ID(+)
AND D.SOURCE_ID = TO_CHAR(K.QUESTION_ID(+))
AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME) >0
AND D.SOURCE_NAME = L.FORM_NAME
AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
OR (D.SOURCE_NAME ='MS_SRA_VISIT_SETUP'
AND D.FIELD_NAME != 'VISIT ID'))
union all
SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
DET.SOURCE_NAME,
DET.FIELD_NAME,
DET.BEFORE_VALUE,
DET.AFTER_VALUE,
DET.CHD_LAST_MODIFIED_ON,
DET.CHD_LAST_MODIFIED_BY
FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
MS_SRA_CHANGE_HIS_HEADER_RPT HED
WHERE HED.SOURCE_ID = DET.SOURCE_ID
AND HED.SOURCE_NAME = DET.SOURCE_NAME
AND HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
);
Error at Command Line:84 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
Have you created the MV log ?
ASKER
nope ... on which table I should create it ?
i created it on :
CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS;
after that when I try to create it says:
i created it on :
CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS;
after that when I try to create it says:
Error at Command Line:107 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
ASKER
I created the new log like :
CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS WITH ROWID, SEQUENCE
(VISIT_ID,
QUESTION_ID,
QST_CODE,
QST_QUESTION,
QST_SHORT_TEXT,
QST_TYPE,QST_SOURCE_OBJECT _TYPE)
INCLUDING NEW VALUES;
and then executed the MV but still I get the below error:
CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS WITH ROWID, SEQUENCE
(VISIT_ID,
QUESTION_ID,
QST_CODE,
QST_QUESTION,
QST_SHORT_TEXT,
QST_TYPE,QST_SOURCE_OBJECT
INCLUDING NEW VALUES;
and then executed the MV but still I get the below error:
Error at Command Line:113 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
Sorry, I just saw your problems with fast refresh:
the "union all" and the "distinct keyword" don't allow fast refresh -you have a complex materialized view.
In case, you eliminate both restrictions, you have to make sure that the full primary key for your master table is part of the SELECT statement.
the "union all" and the "distinct keyword" don't allow fast refresh -you have a complex materialized view.
In case, you eliminate both restrictions, you have to make sure that the full primary key for your master table is part of the SELECT statement.
Try eliminating the DISTINCT keywords and change "Union All" to "Union" -The query may still be too complex for fast refresh, but let's try...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks..
Anytime!!
alter session set query_rewrite=false;