Hi,
I'm encountering an odd behavior when querying a view defined over a table with relational and an XML column. Most columns cannot be selected by themselves (message "no rows selected"),. However, when I select a good and a bad column together, suddenly both columns return data.
CREATE OR REPLACE VIEW EVENT (EVENT_ID, SOURCE, TYPE, DOCUMENT, EVENTTIME, RECORDTIME, TIMEZONEOFFSET, ACTION, BIZSTEP, DISPOSITION, READPOINT, BIZLOC, REASONCODE, CREATION_DATE, ISVRESULT, CARRIER_ID, TRANSFER_CONTAINER, PARENTID, EPCCLASS, TYPE_NAME) AS
SELECT EVENT_ID, SOURCE, t.id TYPE, y.document_id, x.eventtime, x.recordtime, TIMEZONEOFFSET, ACTION, BIZSTEP, DISPOSITION, READPOINT, BIZLOC, REASONCODE, CAST (CREATION_DATE AS TIMESTAMP), ISVRESULT, CARRIER_ID, TRANSFER_CONTAINER, x.parentID, EPCCLASS, x.TYPE_NAME
FROM EVENTSCHEMA.event x ,
EVENTSCHEMA.document y LEFT OUTER JOIN VIEWSCHEMA.event_source ON y.source = external_id ,
VIEWSCHEMA.event_type t,
XMLTABLE (XMLNAMESPACES ('urn:epcglobal:hls:1' AS "hls"), '$e/*' passing EVENTXMLTEXT as "e"
COLUMNS
TIMEZONEOFFSET VARCHAR (2000) PATH 'eventTimeZoneOffset',
ACTION VARCHAR (10) PATH 'action',
BIZSTEP VARCHAR (100) PATH 'bizStep',
DISPOSITION VARCHAR (100) PATH 'disposition',
READPOINT VARCHAR (100) PATH 'readPoint/id',
BIZLOC VARCHAR (100) PATH 'bizLocation/id',
REASONCODE VARCHAR (100) PATH 'extension/reasonCode',
CREATION_DATE TIMESTAMP(9) WITH TIME ZONE PATH 'fn:adjust-dateTime-to-timezone(hls:creationDate)',
ISVRESULT VARCHAR (10) PATH 'extension/ISVResult',
CARRIER_ID VARCHAR (100) PATH 'extension/carrierId',
TRANSFER_CONTAINER VARCHAR (100) PATH 'extension/transferContainer',
EPCCLASS VARCHAR (100) PATH 'epcClass',
TYPE_NAME VARCHAR (50) PATH 'fn:local-name(.)'
) x
WHERE y.document_id = x.document_id
AND t.type = x.TYPE_NAME;
I don't understand:
SQL> select count(*) from viewschema.event;
COUNT(*)
----------
0
SQL> select count(*) from eventschema.event;
COUNT(*)
----------
5
and this one:
SQL> select event_id from viewschema.event;
no rows selected
SQL> select event_id, type_name from viewschema.event;
EVENT_ID TYPE_NAME
---------- --------------------------------------------------
1 AggregationEvent
2 ObjectEvent
3 ObjectEvent
4 ObjectEvent
5 ObjectEvent
Most columns of that table are "bad", i.e. cannot be queried by themselves. However, once queried together with a good column, data is returned. The columns that work fine are: type_name, timezoneoffset, epcclass, transfer_container, carrier_id, reasoncode, creation_date. Does this make sense to anybody? I wonder if this is related to the XMLTable function at all, as the problem occurs already just on the relational columns, as seen in the example above.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Thank you for your help!
-- Daniela