ORA-30373: Object Data Types Are Not Supported In This Context (Doc ID 1939208.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

Creating a Materialized View worked on 11.2.03 but now in 12.1.0.1 generates error

  ORA-30373: object data types are not supported in this context

This is the statement

CREATE MATERIALIZED VIEW PAYMENT.VW_POS_BANK_DEPOSIT_PRASAD
AS
SELECT stg_transaction_id transaction_num,
  to_timestamp(substr(tlogtrans. "transaction_dttime", 1, 10)||' '|| substr(tlogtrans. "transaction_dttime", 12, 13) , 'yyyy-mm-dd hh24:mi:ss.ff4') as transaction_dttime,
 tlogtrans. "store_num" store_num,
 tlogtrans. "employee_id" employee_id,
 tlogtrans. "employee_name" employee_name,
 tlogtenderdeposit. "bank" bank,
 tlogtenderdeposit. "bag_id" bag_id,
 tlogtenderdeposit. "total_amount" total_amount,
sum(decode(tlogdepositdetails. "tender_id", 1, tlogdepositdetails. "tender_amount", 0)) as cash_amount,
sum(decode(tlogdepositdetails. "tender_id", 2, tlogdepositdetails. "tender_amount", 0)) as check_amount,
create_dttime, trunc(sysdate) as refresh_dt
FROM payment.stage_tlog tlog,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction' PASSING (tlog.transaction)
columns
"store_num" PATH '//BusinessUnit/UnitID',
"employee_id" PATH 'OperatorID',
"employee_name" PATH 'OperatorID/@OperatorName',
"member_num" PATH '//Customer/CustomerID',
"transaction_dttime" PATH '//EndDateTime') tlogtrans,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction/TenderControlTransaction/Deposit'
PASSING (tlog.transaction)
columns "bank" PATH 'Bank',
"bag_id" PATH 'BagID',
"total_amount" PATH 'Amount',
"description" PATH 'Description',
"depositor" PATH 'Depositor') tlogtenderdeposit,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction/TenderControlTransaction/Deposit/DepositDetail/Totals'
PASSING (tlog.transaction)
columns "tender_id" PATH 'TenderID',
"amount" PATH 'Amount',
"tender_amount" PATH 'Amount') tlogdepositdetails
WHERE tlogtrans. "store_num" != 600
and flag != 'X'
and existsnode((TRANSACTION),'//Deposit','xmlns="http://www.nrf-arts.org/IXRetail/namespace/"') = 1
group by
 stg_transaction_id,
 create_dttime,
 trunc(sysdate),
 tlogtrans. "transaction_dttime",
 tlogtrans. "store_num",
 tlogtrans. "employee_id",
 tlogtrans. "employee_name",
 tlogtenderdeposit. "bank",
 tlogtenderdeposit. "bag_id",
 tlogtenderdeposit. "total_amount"
ORDER BY stg_transaction_id desc;

*
ERROR at line 29:
ORA-30373: object data types are not supported in this context

The select statement works by itself

SELECT stg_transaction_id transaction_num,
  to_timestamp(substr(tlogtrans. "transaction_dttime", 1, 10)||' '|| substr(tlogtrans. "transaction_dttime", 12, 13) , 'yyyy-mm-dd hh24:mi:ss.ff4') as transaction_dttime,
 tlogtrans. "store_num" store_num,
 tlogtrans. "employee_id" employee_id,
 tlogtrans. "employee_name" employee_name,
 tlogtenderdeposit. "bank" bank,
 tlogtenderdeposit. "bag_id" bag_id,
 tlogtenderdeposit. "total_amount" total_amount,
sum(decode(tlogdepositdetails. "tender_id", 1, tlogdepositdetails. "tender_amount", 0)) as cash_amount,
sum(decode(tlogdepositdetails. "tender_id", 2, tlogdepositdetails. "tender_amount", 0)) as check_amount,
create_dttime, trunc(sysdate) as refresh_dt
FROM payment.stage_tlog tlog,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction' PASSING (tlog.transaction)
columns
"store_num" PATH '//BusinessUnit/UnitID',
"employee_id" PATH 'OperatorID',
"employee_name" PATH 'OperatorID/@OperatorName',
"member_num" PATH '//Customer/CustomerID',
"transaction_dttime" PATH '//EndDateTime') tlogtrans,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction/TenderControlTransaction/Deposit'
PASSING (tlog.transaction)
columns "bank" PATH 'Bank',
"bag_id" PATH 'BagID',
"total_amount" PATH 'Amount',
"description" PATH 'Description',
"depositor" PATH 'Depositor') tlogtenderdeposit,
xmltable (xmlnamespaces ('http://www.Retalix.com/Extensions' AS "r10Ex",DEFAULT 'http://www.nrf-arts.org/IXRetail/namespace/'),'//Transaction/TenderControlTransaction/Deposit/DepositDetail/Totals'
PASSING (tlog.transaction)
columns "tender_id" PATH 'TenderID',
"amount" PATH 'Amount',
"tender_amount" PATH 'Amount') tlogdepositdetails
WHERE tlogtrans. "store_num" != 600
and flag != 'X'
and existsnode((TRANSACTION),'//Deposit','xmlns="http://www.nrf-arts.org/IXRetail/namespace/"') = 1
group by
 stg_transaction_id,
 create_dttime,
 trunc(sysdate),
 tlogtrans. "transaction_dttime",
 tlogtrans. "store_num",
 tlogtrans. "employee_id",
 tlogtrans. "employee_name",
 tlogtenderdeposit. "bank",
 tlogtenderdeposit. "bag_id",
 tlogtenderdeposit. "total_amount"
ORDER BY stg_transaction_id desc;

-- 545 rows selected

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms