Home » Developer & Programmer » JDeveloper, Java & XML » Reading the xml value (Oralce 10g)
Reading the xml value [message #619000] Thu, 17 July 2014 04:37 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

I have xml in below format, How can I extract all the values in single query.



<records>
<job>MANAGER</job>
<details>
  <ename>JONES</ename>
  <sal>2975</sal>
  <ename>BLAKE</ename>
  <sal>2850</sal>
  <ename>CLARK</ename>
  <sal>2450</sal>
  </details>
</records>

SELECT extractvalue(value(a),'job') job              
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/job'))) a;

SELECT extractvalue(value(a),'ename') ename
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/details/ename'))) a;


SELECT extractvalue(value(a),'sal') sal           
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/details/sal'))) a;


Regards,
Nathan

Report message to a moderator

Re: Reading the xml value [message #619003 is a reply to message #619000] Thu, 17 July 2014 04:55 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
 
<records>
<job>MANAGER</job>
<details>
  <ename>JONES</ename>
  <sal>2975</sal>
  <ename>BLAKE</ename>
  <sal>2850</sal>
  <ename>CLARK</ename>
  <sal>2450</sal>
  </details>
</records>


how you got the above code? I think use loop concept.

[Updated on: Thu, 17 July 2014 04:55]

Report message to a moderator

Re: Reading the xml value [message #619006 is a reply to message #619000] Thu, 17 July 2014 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 

SQL> with data as (select xmltype('
  2  <records>
  3  <job>MANAGER</job>
  4  <details>
  5    <ename>JONES</ename>
  6    <sal>2975</sal>
  7    <ename>BLAKE</ename>
  8    <sal>2850</sal>
  9    <ename>CLARK</ename>
 10    <sal>2450</sal>
 11    </details>
 12  </records>') v from dual
 13    )
 14  select extractvalue(value(x),'/records/job') job,
 15         y.ename,
 16         z.sal
 17  from data, table(xmlsequence(extract(v,'/records'))) x,
 18       xmltable('//details/ename' PASSING v
 19                COLUMNS POSITION FOR ORDINALITY,
 20                ename VARCHAR2(20) PATH '//ename') y,
 21       xmltable('//details/sal' PASSING v
 22                COLUMNS POSITION FOR ORDINALITY,
 23                sal NUMBER PATH '//sal') z
 24  where y.position = z.position
 25  /
JOB        ENAME             SAL
---------- ---------- ----------
MANAGER    JONES            2975
MANAGER    BLAKE            2850
MANAGER    CLARK            2450

Report message to a moderator

Re: Reading the xml value [message #619007 is a reply to message #619003] Thu, 17 July 2014 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Thu, 17 July 2014 10:55
<records>
<job>MANAGER</job>
<details>
  <ename>JONES</ename>
  <sal>2975</sal>
  <ename>BLAKE</ename>
  <sal>2850</sal>
  <ename>CLARK</ename>
  <sal>2450</sal>
  </details>
</records>


how you got the above code? I think use loop concept.


You have no idea what xml is do you?

Report message to a moderator

Re: Reading the xml value [message #619009 is a reply to message #619007] Thu, 17 July 2014 05:16 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
You have no idea what xml is do you?

In the XML Reports we used for each row concept that means used Loop concept. If it is wrong that is the my mistake

Report message to a moderator

Re: Reading the xml value [message #619011 is a reply to message #619009] Thu, 17 July 2014 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can construct and deconstruct xml in a loop but that's almost always the hard way.
Oracle supplies a large set of xml functions for those jobs.
See Michel's solution above for an example.

Report message to a moderator

Re: Reading the xml value [message #619012 is a reply to message #619011] Thu, 17 July 2014 05:27 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

You can construct and deconstruct xml in a loop but that's almost always the hard way.
Oracle supplies a large set of xml functions for those jobs.
See Michel's solution above for an example.

Yes cookiemonster that is simple one.

Thanks

Report message to a moderator

Re: Reading the xml value [message #619014 is a reply to message #619012] Thu, 17 July 2014 05:35 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you so much Michel, It is working fine .

[Updated on: Thu, 17 July 2014 06:03]

Report message to a moderator

Re: Reading the xml value [message #619017 is a reply to message #619014] Thu, 17 July 2014 06:18 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi this one also.. Smile 
  with testdata as (
     select xmltype(
    '<records>
    <job>MANAGER</job>
    <details>
     <emp>
      <ename>JONES</ename>
      <sal>2975</sal>
     </emp>
   <emp>
   <ename>BLAKE</ename>
   <sal>2850</sal>
  </emp>
  <emp>
   <ename>CLARK</ename>
   <sal>2450</sal>
  </emp>
   </details>
 </records>
 ') as x
from dual)
 select
  j.job
 ,d.ename
 ,d.sal
 from testdata
 join xmltable('records' passing testdata.x
 columns
  job   varchar2(30) path 'job'
 ,details xmltype path 'details'
 ) j
 on (1=1)
 left outer join
  xmltable ( 'details/emp' passing j.details
 columns
  ename varchar2(30) path 'ename'
 ,sal varchar2(30) path 'sal'
 ) d
 on (1=1)

Source: From ONT Forum

Report message to a moderator

Re: Reading the xml value [message #619021 is a reply to message #619017] Thu, 17 July 2014 07:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
mist598 wrote on Thu, 17 July 2014 16:48
ONT Forum


ONT, Oracle Non Technology forum, eh?

Perhaps you could have posted a link to OTN forum.

Report message to a moderator

Re: Reading the xml value [message #619022 is a reply to message #619006] Thu, 17 July 2014 07:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Thu, 17 July 2014 15:41

SQL> with data as (select xmltype('
  2  <records>
  3  <job>MANAGER</job>
  4  <details>
  5    <ename>JONES</ename>
  6    <sal>2975</sal>
  7    <ename>BLAKE</ename>
  8    <sal>2850</sal>
  9    <ename>CLARK</ename>
 10    <sal>2450</sal>
 11    </details>
 12  </records>') v from dual
 13    )
 14  select extractvalue(value(x),'/records/job') job,
 15         y.ename,
 16         z.sal
 17  from data, table(xmlsequence(extract(v,'/records'))) x,
 18       xmltable('//details/ename' PASSING v
 19                COLUMNS POSITION FOR ORDINALITY,
 20                ename VARCHAR2(20) PATH '//ename') y,
 21       xmltable('//details/sal' PASSING v
 22                COLUMNS POSITION FOR ORDINALITY,
 23                sal NUMBER PATH '//sal') z
 24  where y.position = z.position
 25  /
JOB        ENAME             SAL
---------- ---------- ----------
MANAGER    JONES            2975
MANAGER    BLAKE            2850
MANAGER    CLARK            2450



Michel, I am trying to understand why it doesn't give me the same output as yours :

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> column job format A15;
SQL> 
SQL> with data as (select xmltype('
  2     <records>
  3     <job>MANAGER</job>
  4     <details>
  5       <ename>JONES</ename>
  6       <sal>2975</sal>
  7       <ename>BLAKE</ename>
  8       <sal>2850</sal>
  9       <ename>CLARK</ename>
 10       <sal>2450</sal>
 11       </details>
 12     </records>') v from dual
 13       )
 14     select extractvalue(value(x),'/records/job') job,
 15            y.ename,
 16            z.sal
 17     from data, table(xmlsequence(extract(v,'/records'))) x,
 18          xmltable('//details/ename' PASSING v
 19                   COLUMNS POSITION FOR ORDINALITY,
 20                   ename VARCHAR2(20) PATH '//ename') y,
 21          xmltable('//details/sal' PASSING v
 22                   COLUMNS POSITION FOR ORDINALITY,
 23                   sal NUMBER PATH '//sal') z
 24     where y.position = z.position;
JOB             ENAME                       SAL
--------------- -------------------- ----------
MANAGER                              
MANAGER                              
MANAGER

Report message to a moderator

Re: Reading the xml value [message #619027 is a reply to message #619022] Thu, 17 July 2014 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did it in 10.2.0.4 and I get the same thing in 11.2.0.1 (both Windows XP).
I currently have no 11.2.0.4 to test it.

Report message to a moderator

Re: Reading the xml value [message #619029 is a reply to message #619027] Thu, 17 July 2014 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
  1  with data as (select xmltype('
  2         <records>
  3         <job>MANAGER</job>
  4         <details>
  5           <ename>JONES</ename>
  6           <sal>2975</sal>
  7           <ename>BLAKE</ename>
  8           <sal>2850</sal>
  9           <ename>CLARK</ename>
 10          <sal>2450</sal>
 11          </details>
 12        </records>') v from dual
 13          )
 14        select extractvalue(value(x),'/records/job') job,
 15               y.ename,
 16               z.sal
 17        from data, table(xmlsequence(extract(v,'/records'))) x,
 18             xmltable('//details/ename' PASSING v
 19                      COLUMNS POSITION FOR ORDINALITY,
 20                      ename VARCHAR2(20) PATH '//ename') y,
 21             xmltable('//details/sal' PASSING v
 22                      COLUMNS POSITION FOR ORDINALITY,
 23                      sal NUMBER PATH '//sal') z
 24*       where y.position = z.position
SQL> /

JOB             ENAME                       SAL
--------------- -------------------- ----------
MANAGER         JONES                      2975
MANAGER         BLAKE                      2850
MANAGER         CLARK                      2450

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


It works for me.

Report message to a moderator

Re: Reading the xml value [message #619031 is a reply to message #619029] Thu, 17 July 2014 08:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I have two databases, recently applied 11.2.0.4 patchset for testing, other is still in 11.2.0.2. Let me execute the same query in both versions :

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> column job format A15;
SQL> 
SQL> with data as (select xmltype('
  2            <records>
  3            <job>MANAGER</job>
  4            <details>
  5              <ename>JONES</ename>
  6              <sal>2975</sal>
  7            <ename>BLAKE</ename>
  8              <sal>2850</sal>
  9              <ename>CLARK</ename>
 10             <sal>2450</sal>
 11             </details>
 12           </records>') v from dual
 13             )
 14           select extractvalue(value(x),'/records/job') job,
 15                  y.ename,
 16                  z.sal
 17           from data, table(xmlsequence(extract(v,'/records'))) x,
 18                xmltable('//details/ename' PASSING v
 19                         COLUMNS POSITION FOR ORDINALITY,
 20                         ename VARCHAR2(20) PATH '//ename') y,
 21                xmltable('//details/sal' PASSING v
 22                         COLUMNS POSITION FOR ORDINALITY,
 23                         sal NUMBER PATH '//sal') z
 24          where y.position = z.position
 25  /
JOB             ENAME                       SAL
--------------- -------------------- ----------
MANAGER         JONES                      2975
MANAGER         BLAKE                      2850
MANAGER         CLARK                      2450


Now in 11.2.0.4 :

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> column job format A15;
SQL> 
SQL> with data as (select xmltype('
  2            <records>
  3            <job>MANAGER</job>
  4            <details>
  5              <ename>JONES</ename>
  6              <sal>2975</sal>
  7            <ename>BLAKE</ename>
  8              <sal>2850</sal>
  9              <ename>CLARK</ename>
 10             <sal>2450</sal>
 11             </details>
 12           </records>') v from dual
 13             )
 14           select extractvalue(value(x),'/records/job') job,
 15                  y.ename,
 16                  z.sal
 17           from data, table(xmlsequence(extract(v,'/records'))) x,
 18                xmltable('//details/ename' PASSING v
 19                         COLUMNS POSITION FOR ORDINALITY,
 20                         ename VARCHAR2(20) PATH '//ename') y,
 21                xmltable('//details/sal' PASSING v
 22                         COLUMNS POSITION FOR ORDINALITY,
 23                         sal NUMBER PATH '//sal') z
 24          where y.position = z.position
 25  /
JOB             ENAME                       SAL
--------------- -------------------- ----------
MANAGER                              
MANAGER                              
MANAGER


So it doesn't work in 11.2.0.4

Regards,
Lalit

Report message to a moderator

Re: Reading the xml value [message #619033 is a reply to message #619031] Thu, 17 July 2014 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have a test case to raise a SR. Smile

[Updated on: Thu, 17 July 2014 08:28]

Report message to a moderator

Re: Reading the xml value [message #619034 is a reply to message #619031] Thu, 17 July 2014 08:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I searched for any known bugs in 11.2.0.4, http://docs.oracle.com/cd/E11882_01/readmes.112/e41331/chapter11204.htm, didn't find anything relevant. Need to search in MOS.

Report message to a moderator

Re: Reading the xml value [message #619141 is a reply to message #619034] Fri, 18 July 2014 06:29 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

For fetching the nodes of different xml format we have to manually construct plsql or change xml format to suit the coding.
And I think there is no direct sql possible for all type of xml formats.Please suggest anything wrong if we do as follows or any better option is available.

declare
l_str varchar2(1000):=
'<emp_details>
  <deptno>20</deptno>
  <job_emp_det>
    <job>CLERK</job>
    <ename>SMITH</ename>
    <ename>ADAMS</ename>
  </job_emp_det>
  <job_emp_det>
    <job>MANAGER</job>
    <ename>JONES</ename>
  </job_emp_det>
  <job_emp_det>
    <job>ANALYST</job>
    <ename>FORD</ename>
    <ename>SCOTT</ename>
  </job_emp_det>
</emp_details>';
l_deptno varchar2(100);
type rec is record(c1 varchar2(100),c2 varchar2(100),c3 varchar2(100));
type tab is table of rec index by binary_integer;
tab1 tab;
l_counter number:=1;
begin
SELECT extractvalue(value(k),'deptno/text()') deptno into l_deptno from table(xmlsequence(extract(xmltype(l_str),'/emp_details/deptno'))) k ;
for l in (select extract(value(k),'job_emp_det') job_det  from table(xmlsequence(extract(xmltype(l_str),'/emp_details/job_emp_det'))) k )loop
    for i in (select extractvalue(value(k),'job/text()') job from table(xmlsequence(extract(l.job_det,'job_emp_det/job'))) k  )loop
--    dbms_output.put_line(i.job);
        for j in (select extractvalue(value(k),'ename/text()') ename  from table(xmlsequence(extract(l.job_det,'job_emp_det/ename'))) k  )loop
--        dbms_output.put_line(j.ename);
        tab1(l_counter).c1:=l_deptno;
        tab1(l_counter).c2:=i.job;
        tab1(l_counter).c3:=j.ename;
        l_counter:=l_counter+1;
        end loop;
    end loop;
end loop;
for i in 1 .. tab1.count loop
dbms_output.put_line(tab1(i).c1||' '||tab1(i).c2||' '||tab1(i).c3);
end loop;
end;

Report message to a moderator

Re: Reading the xml value [message #619148 is a reply to message #619141] Fri, 18 July 2014 07:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
sss111ind wrote on Fri, 18 July 2014 07:29

And I think there is no direct sql possible for all type of xml formats.



First of all your XML is not well formed. Try adding another department to it. Anyway:

with t as (
           select xmltype('<emp_details>
  <deptno>20</deptno>
  <job_emp_det>
    <job>CLERK</job>
    <ename>SMITH</ename>
    <ename>ADAMS</ename>
  </job_emp_det>
  <job_emp_det>
    <job>MANAGER</job>
    <ename>JONES</ename>
  </job_emp_det>
  <job_emp_det>
    <job>ANALYST</job>
    <ename>FORD</ename>
    <ename>SCOTT</ename>
  </job_emp_det>
</emp_details>') xmldoc from dual
           )
select  deptno,
        job,
        ename
  from  t,
        xmltable(
                 '/emp_details'
                 passing xmldoc
                 columns
                   deptno      number  path 'deptno',
                   job_emp_det xmltype path 'job_emp_det'
                ),
        xmltable(
                 '/job_emp_det'
                 passing job_emp_det
                 columns
                   job        varchar2(10) path 'job',
                   ename_list xmltype path 'ename'
                ),
        xmltable(
                 '/ename'
                 passing ename_list
                 columns
                   ename varchar2(20) path '/ename'
                )
/

DEPTNO JOB                  ENAME
------ -------------------- ------
    20 CLERK                SMITH
    20 CLERK                ADAMS
    20 MANAGER              JONES
    20 ANALYST              FORD
    20 ANALYST              SCOTT

SQL>


SY.

Report message to a moderator

Re: Reading the xml value [message #619149 is a reply to message #619148] Fri, 18 July 2014 07:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Something even more strange than the bug we I reported yesterday. This time SY's query works fine in 11.2.0.4, but it throws internal error in 11.2.0.2

11.2.0.2
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> with t as (
  2             select xmltype('<emp_details>
  3    <deptno>20</deptno>
  4    <job_emp_det>
  5      <job>CLERK</job>
  6      <ename>SMITH</ename>
  7      <ename>ADAMS</ename>
  8    </job_emp_det>
  9    <job_emp_det>
 10      <job>MANAGER</job>
 11      <ename>JONES</ename>
 12    </job_emp_det>
 13    <job_emp_det>
 14      <job>ANALYST</job>
 15      <ename>FORD</ename>
 16      <ename>SCOTT</ename>
 17    </job_emp_det>
 18  </emp_details>') xmldoc from dual
 19             )
 20  select  deptno,
 21          job,
 22          ename
 23    from  t,
 24          xmltable(
 25                   '/emp_details'
 26                   passing xmldoc
 27                   columns
 28                     deptno      number  path 'deptno',
 29                     job_emp_det xmltype path 'job_emp_det'
 30                  ),
 31          xmltable(
 32                   '/job_emp_det'
 33                   passing job_emp_det
 34                   columns
 35                     job        varchar2(10) path 'job',
 36                     ename_list xmltype path 'ename'
 37                  ),
 38          xmltable(
 39                   '/ename'
 40                   passing ename_list
 41                   columns
 42                     ename varchar2(20) path '/ename'
 43                  )
 44  /
    <ename>SMITH</ename>
                        *
ERROR at line 6:
ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [],
[], [], [], [], []


11.2.0.4

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> with t as (
  2             select xmltype('<emp_details>
  3    <deptno>20</deptno>
  4    <job_emp_det>
  5      <job>CLERK</job>
  6      <ename>SMITH</ename>
  7      <ename>ADAMS</ename>
  8    </job_emp_det>
  9    <job_emp_det>
 10      <job>MANAGER</job>
 11      <ename>JONES</ename>
 12    </job_emp_det>
 13    <job_emp_det>
 14      <job>ANALYST</job>
 15      <ename>FORD</ename>
 16      <ename>SCOTT</ename>
 17    </job_emp_det>
 18  </emp_details>') xmldoc from dual
 19             )
 20  select  deptno,
 21          job,
 22          ename
 23    from  t,
 24          xmltable(
 25                   '/emp_details'
 26                   passing xmldoc
 27                   columns
 28                     deptno      number  path 'deptno',
 29                     job_emp_det xmltype path 'job_emp_det'
 30                  ),
 31          xmltable(
 32                   '/job_emp_det'
 33                   passing job_emp_det
 34                   columns
 35                     job        varchar2(10) path 'job',
 36                     ename_list xmltype path 'ename'
 37                  ),
 38          xmltable(
 39                   '/ename'
 40                   passing ename_list
 41                   columns
 42                     ename varchar2(20) path '/ename'
 43                  )
 44  /

    DEPTNO JOB        ENAME
---------- ---------- --------------------
        20 CLERK      SMITH
        20 CLERK      ADAMS
        20 MANAGER    JONES
        20 ANALYST    FORD
        20 ANALYST    SCOTT


So this time a bug in 11.2.0.2?

Regards,
Lalit

Report message to a moderator

Re: Reading the xml value [message #619168 is a reply to message #619149] Fri, 18 July 2014 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-0060 is ALWAYS a bug. Wink
It works in 10.2.0.4 and 11.2.0.1.

Report message to a moderator

Re: Reading the xml value [message #619170 is a reply to message #619168] Fri, 18 July 2014 09:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I have asked my DBA to look into it and take action accordingly. If he raises a SR, I would keep you guys posted about what Oracle support replies.

Report message to a moderator

Re: Reading the xml value [message #621777 is a reply to message #619170] Mon, 18 August 2014 04:28 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
A quick update.

For the above mentioned bug, please see Bug 12381289 : SELECT WITH WITH CLAUSE(SUBFACTORING) AND XMLTYPE THROWS ORA-600 [KKOLJT1] created on 20-Apr-2011. The workaround is to use NO_XML_QUERY_REWRITE hint. I also executed the original query in 12.1.0.1 and it worked fine with no issues.

Regards,
Lalit

Report message to a moderator

Previous Topic: Wrapping a recursive function
Next Topic: Oracle REFCURSOR issue. Front end app is slow
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 00:38:17 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.