Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22950: cannot ORDER objects without MAP - XMLTYPE
ORA-22950: cannot ORDER objects without MAP - XMLTYPE [message #209575] Fri, 15 December 2006 11:01 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
getting error in below secnario...


create or replace PROCEDURE test_xml (
      p_idn_in         		IN       table_1.idn%TYPE,
	  p_user_name			IN		 table_1.user_name%type;
      p_extension_xt_in     IN       table_1.extension_xt%TYPE 		  default  XMLTYPE('<default></default>'),
      p_err_code_out        OUT      NUMBER,
      p_err_mesg_out        OUT      VARCHAR2
   )
   IS
   BEGIN
      UPDATE table_1
         set extension_xt =
                DECODE (p_extension_xt_in,
                          XMLTYPE('<default></default>'), extension_xt,
                        p_extension_xt_in
                       ),
		     user_name = p_user_name                		                		   
       WHERE idn = p_idn_in
      p_err_code_out := SQLCODE;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_err_code_out := SQLCODE;
         p_err_mesg_out := SQLERRM;
END test_xml_spt_update;
/




execution script


*******************************************************************************
           ******************  SCRIPT    ********************
*******************************************************************************		   

declare
  my_err number;
  my_code  varchar2(200);
begin
test_xml_spt_update
                  (p_id_in             		   => 2705,
				   p_user_name				   => 'AAAAA',
                   p_err_code_out              => my_err ,
                   p_err_mesg_out              => my_err 
                  );
		
		
end;

 		
******************************************************************************		



"ORA-22950: cannot ORDER objects without MAP or ORDER method"
is thrown


if i modify the decode in procedure as


DECODE (p_extension_xt_in,
                         ('<default></default>'), extension_xt,
                        p_extension_xt_in
                       )


it throws "ORA-03113: end-of-file on communication channel"

execution is successful only if i modify the decode as above and pass null as parameter to p_extension_xt_in while call the procedure...i.e as below:

declare
  my_err number;
  my_code  varchar2(200);
begin
test_xml_spt_update
                  (p_id_in             		   => 2705,
				   p_user_name				   => 'AAAAA',
				   p_extension_xt_in		   => NULL,
                   p_err_code_out              => my_err ,
                   p_err_mesg_out              => my_err 
                  );
		
		
end;


But i dont want to pass null if i dont want to modify the column p_extension_xt_in...

any help on this pls...


Naveen
Re: ORA-22950: cannot ORDER objects without MAP - XMLTYPE [message #209843 is a reply to message #209575] Mon, 18 December 2006 01:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As I understand it, the problem is that DECODE (and also CASE incidentaly) do not fully support the XMLTYPE data type (or if you like, the XMLTYPE does not contain all the methods required to allow it to work with CASE and DECODE)
SQL> create table xml_test (col_1 varchar2(10), col_2 xmltype);

Table created.

SQL> insert into xml_test values ('10',xmltype('<test>A</test>'));
SQL> insert into xml_test values ('10',xmltype('<test>B</test>'));
SQL> insert into xml_test values ('10',null);

SQL> select decode(col_2,xmltype('<test>A</test>'),1,2) from xml_Test;
select decode(col_2,xmltype('<test>A</test>'),1,2) from xml_Test
                    *
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method

One workround is to use the EXTRACT and GETSTRINGVAL methods of the XMLTYPE to extract as strings fragments of the XML and do comparisons against those:
SQL> select decode(xmltype.getstringval(col_2),'<test>A</test>',1,2) from xml_Test;

DECODE(XMLTYPE.GETSTRINGVAL(COL_2),'<TEST>A</TEST>',1,2)
--------------------------------------------------------
                                                       1
                                                       2
                                                       2
Previous Topic: cursors in trigger
Next Topic: How to get the Column list of a table in run time
Goto Forum:
  


Current Time: Mon Dec 05 05:02:18 CST 2016

Total time taken to generate the page: 0.10372 seconds