Hi xml gurus,
Please help me with the following:
I have very limited experience with xml. I have a query on one database that is behaving differenlty on another database.
My test database is Oracle 11g version 18.104.22.168
My production database is Oracle 11g version 22.214.171.124
I have a table that contains xmltype data column called for example "myxml_column_table" that has two columns, 'REQUEST_ID' of number data type and 'MESSAGE_BODY' of xmltype. The message_body contains data looking similar to the following xml file:
The example query:
SELECT t.request_id as request_id
,int_header.a as HeaderControlRef
,message_header.a as MessageNumber
FROM ( SELECT request_id
WHERE request_id = 1 ) t
XMLTABLE( 'for $i in /ROOTPATH/HEADER
return $i' passing t.message_body
columns a varchar2(250) path 'HeaderControlRef'
,message_header xmltype path 'MESSAGE_HEADER'
) int_header on 1=1
LEFT OUTER JOIN
XMLTABLE( 'for $i in /MESSAGE_HEADER
return $i' passing int_header.message_header
columns a varchar2(250) path 'MessageNumber'
) message_header on 1=1
WHERE int_header.a = 1001;
The problem I have is that my MessageNumber is being returned as null on the production database, but returns 123456 from my test database as expected. Any help would be appreciated on this.
Thank you for your response.
I have given the file and query as just an example of what is happening, It is not allowed for me to post the full test case and explain plans on this forum. Also I don't have access to sql*plus, I only have sqldeveloper.
One thing I forgot to also mention is that the DB's are on different servers running Red Hat Enterprise Linux Server release 5.9 (Tikanga).
It is not allowed for me to post the full test case and explain plans on this forum. Also I don't have access to sql*plus, I only have sqldeveloper.
Then I'm afraid no one will help with such sketchy details.
Both databases are the same version, same OS version, so I guess the problem is not so trivial to tackle, especially remotely, on a forum.
I suggest you open a SR on My Oracle Support.