| ORA-22950: cannot ORDER objects without MAP - XMLTYPE [message #209575] |
Fri, 15 December 2006 11:01  |
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  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
