Home » Developer & Programmer » JDeveloper, Java & XML » ORA-1460 while working with XML (merged 3) (oracle9i)
ORA-1460 while working with XML (merged 3) [message #547252] Tue, 13 March 2012 10:18 Go to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

Hi!

I have a problem when trying to assign the value of a VARCHAR2 variable into a XMLType: ORA-1460 unimplemented or unreasonable conversion requested.

This is my code:
----------------------------------------------------------------
FUNCTION test(
xml_string IN VARCHAR2,
type IN VARCHAR2,
path_array IN VARCHAR2)
RETURN NUMBER AS
sub_xml XMLType;
index NUMBER;
path_find VARCHAR2(300);
existNode NUMBER;
number_nodes NUMBER;
xpath_main_node VARCHAR2(200);

BEGIN
number_nodes := 0;

SELECT xmltype(xml_string) INTO sub_xml from dual;

xpath_nodo_principal := '/' || tipo || 'Response/' || tipo || 'Result/child::node()';

sub_xml := sub_xml.EXTRACT(XPATH_HEADER, NAME_SPACE_GENERAL);

IF(sub_xml IS NOT NULL) THEN
...
-----------------------------------------------------------------

The error happens at SELECT xmltype(xml_string) INTO sub_xml from dual;

I really don't understand what's exactly happening cause this code works for some cases but this happens a lot too cause my application handles a lot of information. The length of xml_string in that moment is 4119.

Also the XML code I'm working with and I detected this one always goes wrong doesn't seem to have any errors.

Please I need some help here.

Thanks in advance.
Re: ORA-1460 while working with XML [message #547270 is a reply to message #547252] Tue, 13 March 2012 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the complete code as well as the xml data that does not work so we can test it in our environment.
Post your Oracle version, with 4 decimals.

Regards
Michel
Re: ORA/1460 in XML [message #547294 is a reply to message #547252] Tue, 13 March 2012 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Re: ORA/1460 in XML [message #547312 is a reply to message #547252] Tue, 13 March 2012 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 13 March 2012 17:32
Post the complete code as well as the xml data that does not work so we can test it in our environment.
Post your Oracle version, with 4 decimals.

Regards
Michel


If you don't answer our question we can't help.
Now you can stay with your problem or not, it's up to you.

Regards
Michel
Re: ORA/1460 in XML [message #547313 is a reply to message #547294] Tue, 13 March 2012 12:46 Go to previous messageGo to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

Sorry about the post it was my bad i think cause i tried to send the post again after an error i got.

Oracle9i
Client 8.0.6.0.0
Server 9.2.0.8.0

  FUNCTION BE_P_CONTAR_NODOS_XML(
    xml_string IN VARCHAR2,
    tipo IN VARCHAR2,
    path_arreglo IN VARCHAR2)
  RETURN NUMBER AS
    sub_xml XMLType;              
    indice NUMBER;                
    path_buscar VARCHAR2(300);    
    existeNodo NUMBER;            
    cantidad_nodos NUMBER;       
    xpath_nodo_principal VARCHAR2(200);
    Lv_mensajeError VARCHAR2(200);    
            
  BEGIN
    cantidad_nodos := 0;
    BEGIN
        SELECT xmltype(xml_string) INTO sub_xml from dual;
    EXCEPTION
        WHEN OTHERS THEN            
            Lv_mensajeError :=  length(Lv_XML) ||' '|| length(xml_string) ||' '|| SQLERRM;
            INSERT INTO TEMP_XML VALUES (xml_string);
            COMMIT;
    END;
    --EL XPATH ES GENERALEMNTE EN TODOS LOS REPONSE XML IGUAL A ESTE
    xpath_nodo_principal := '/' || tipo || 'Response/' || tipo || 'Result/child::node()';
  
    --SE EXTRAE LA RESPUESTA ELIMINANDO LOS ENCABEZADOS PRINCIPALES
    sub_xml := sub_xml.EXTRACT(XPATH_ENCABEZADO, NAME_SPACE_GENERAL);
    --EXTRAE EL CONTENIDO DE LA RESPUESTA
    IF(sub_xml IS NOT NULL) THEN
    --EXTRAE EL CUERPO DEL MENSAJE
      sub_xml := sub_xml.EXTRACT(xpath_nodo_principal, NAME_SPACE_NODO);
      LOOP
        indice := cantidad_nodos + 1;
        path_buscar := path_arreglo || '[' || TO_CHAR(indice) || ']';
        existeNodo := sub_xml.EXISTSNODE(path_buscar, NAME_SPACE_NODO);
        IF (existeNodo = 0) THEN
          EXIT;
        ELSE
          cantidad_nodos := cantidad_nodos + 1;
        END IF;
      END LOOP;
    ELSE
      RETURN NULL;
    END IF;
    RETURN cantidad_nodos;
  END BE_P_CONTAR_NODOS_XML;
  • Attachment: Test.txt
    (Size: 3.96KB, Downloaded 431 times)
Re: ORA/1460 in XML [message #547323 is a reply to message #547313] Tue, 13 March 2012 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace
  2  FUNCTION BE_P_CONTAR_NODOS_XML(
  3      xml_string IN VARCHAR2,
  4      tipo IN VARCHAR2,
  5      path_arreglo IN VARCHAR2)
  6    RETURN NUMBER AS
  7      sub_xml XMLType;              
  8      indice NUMBER;                
  9      path_buscar VARCHAR2(300);    
 10      existeNodo NUMBER;            
 11      cantidad_nodos NUMBER;       
 12      xpath_nodo_principal VARCHAR2(200);
 13      Lv_mensajeError VARCHAR2(200);    
 14              
 15    BEGIN
 16      cantidad_nodos := 0;
 17  --    BEGIN
 18          SELECT xmltype(xml_string) INTO sub_xml from dual;
 19  /*
 20      EXCEPTION
 21          WHEN OTHERS THEN            
 22              Lv_mensajeError :=  length(Lv_XML) ||' '|| length(xml_string) ||' '|| SQLERRM;
 23              INSERT INTO TEMP_XML VALUES (xml_string);
 24              COMMIT;
 25      END;
 26  */
 27      --EL XPATH ES GENERALEMNTE EN TODOS LOS REPONSE XML IGUAL A ESTE
 28      xpath_nodo_principal := '/' || tipo || 'Response/' || tipo || 'Result/child::node()';
 29    
 30      --SE EXTRAE LA RESPUESTA ELIMINANDO LOS ENCABEZADOS PRINCIPALES
 31      sub_xml := sub_xml.EXTRACT(XPATH_ENCABEZADO, NAME_SPACE_GENERAL);
 32      --EXTRAE EL CONTENIDO DE LA RESPUESTA
 33      IF(sub_xml IS NOT NULL) THEN
 34      --EXTRAE EL CUERPO DEL MENSAJE
 35        sub_xml := sub_xml.EXTRACT(xpath_nodo_principal, NAME_SPACE_NODO);
 36        LOOP
 37          indice := cantidad_nodos + 1;
 38          path_buscar := path_arreglo || '[' || TO_CHAR(indice) || ']';
 39          existeNodo := sub_xml.EXISTSNODE(path_buscar, NAME_SPACE_NODO);
 40          IF (existeNodo = 0) THEN
 41            EXIT;
 42          ELSE
 43            cantidad_nodos := cantidad_nodos + 1;
 44          END IF;
 45        END LOOP;
 46      ELSE
 47        RETURN NULL;
 48      END IF;
 49      RETURN cantidad_nodos;
 50    END BE_P_CONTAR_NODOS_XML;
 51  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION BE_P_CONTAR_NODOS_XML:
LINE/COL
---------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------------------------------------------
30/5
PL/SQL: Statement ignored
30/32
PLS-00201: identifier 'XPATH_ENCABEZADO' must be declared
34/7
PL/SQL: Statement ignored
34/56
PLS-00201: identifier 'NAME_SPACE_NODO' must be declared
38/9
PL/SQL: Statement ignored
38/55

I can't reproduce what you post as I can't even compile.

This:
    EXCEPTION
        WHEN OTHERS THEN            
            Lv_mensajeError :=  length(Lv_XML) ||' '|| length(xml_string) ||' '|| SQLERRM;
            INSERT INTO TEMP_XML VALUES (xml_string);
            COMMIT;
    END;

is VERY VERY bad.
You have an error and so you commit ALL the work the caller did!
Are you sure this what it wants to do?
There is ONLY one way to handle this kind of errors: let them go.

Regards
Michel
Re: ORA/1460 in XML [message #547328 is a reply to message #547313] Tue, 13 March 2012 13:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3106
Registered: January 2010
Location: Connecticut, USA
Senior Member
Spada wrote on Tue, 13 March 2012 13:46
Sorry about the post


It is still a "bad post":

SQL> create or replace
  2    FUNCTION BE_P_CONTAR_NODOS_XML(
  3      xml_string IN VARCHAR2,
  4      tipo IN VARCHAR2,
  5      path_arreglo IN VARCHAR2)
  6    RETURN NUMBER AS
  7      sub_xml XMLType;              
  8      indice NUMBER;                
  9      path_buscar VARCHAR2(300);    
 10      existeNodo NUMBER;            
 11      cantidad_nodos NUMBER;       
 12      xpath_nodo_principal VARCHAR2(200);
 13      Lv_mensajeError VARCHAR2(200);    
 14              
 15    BEGIN
 16      cantidad_nodos := 0;
 17      BEGIN
 18          SELECT xmltype(xml_string) INTO sub_xml from dual;
 19      EXCEPTION
 20          WHEN OTHERS THEN            
 21              Lv_mensajeError :=  length(Lv_XML) ||' '|| length(xml_string) ||' '|| SQLERRM;
 22              INSERT INTO TEMP_XML VALUES (xml_string);
 23              COMMIT;
 24      END;
 25      --EL XPATH ES GENERALEMNTE EN TODOS LOS REPONSE XML IGUAL A ESTE
 26      xpath_nodo_principal := '/' || tipo || 'Response/' || tipo || 'Result/child::node()';
 27    
 28      --SE EXTRAE LA RESPUESTA ELIMINANDO LOS ENCABEZADOS PRINCIPALES
 29      sub_xml := sub_xml.EXTRACT(XPATH_ENCABEZADO, NAME_SPACE_GENERAL);
 30      --EXTRAE EL CONTENIDO DE LA RESPUESTA
 31      IF(sub_xml IS NOT NULL) THEN
 32      --EXTRAE EL CUERPO DEL MENSAJE
 33        sub_xml := sub_xml.EXTRACT(xpath_nodo_principal, NAME_SPACE_NODO);
 34        LOOP
 35          indice := cantidad_nodos + 1;
 36          path_buscar := path_arreglo || '[' || TO_CHAR(indice) || ']';
 37          existeNodo := sub_xml.EXISTSNODE(path_buscar, NAME_SPACE_NODO);
 38          IF (existeNodo = 0) THEN
 39            EXIT;
 40          ELSE
 41            cantidad_nodos := cantidad_nodos + 1;
 42          END IF;
 43        END LOOP;
 44      ELSE
 45        RETURN NULL;
 46      END IF;
 47      RETURN cantidad_nodos;
 48    END BE_P_CONTAR_NODOS_XML;
 49  /
Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION BE_P_CONTAR_NODOS_XML:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/13    PL/SQL: Statement ignored
20/40    PLS-00201: identifier 'LV_XML' must be declared
21/13    PL/SQL: SQL Statement ignored
21/25    PL/SQL: ORA-00942: table or view does not exist
28/5     PL/SQL: Statement ignored
28/32    PLS-00201: identifier 'XPATH_ENCABEZADO' must be declared
32/7     PL/SQL: Statement ignored
32/56    PLS-00201: identifier 'NAME_SPACE_NODO' must be declared
36/9     PL/SQL: Statement ignored
36/55    PLS-00201: identifier 'NAME_SPACE_NODO' must be declared
SQL> 


SY.
Re: ORA/1460 in XML [message #547336 is a reply to message #547323] Tue, 13 March 2012 14:36 Go to previous messageGo to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

Alright, it seems like someone was trying to do some weird stuff in the code when I took it from there, yeah I am not the only one working on this.

I just compiled this so it should work.

Im sorry if im causing much trouble with this Im just too busy at work, painful day.

I uploaded the code in a TXT cause the forum wont let me post it.
Re: ORA/1460 in XML [message #547339 is a reply to message #547336] Tue, 13 March 2012 14:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3106
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, maybe now you'll post function call with ALL parameters?

SY.
Re: ORA/1460 in XML [message #547342 is a reply to message #547339] Tue, 13 March 2012 14:59 Go to previous messageGo to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

syakobson wrote on Tue, 13 March 2012 13:43
Well, maybe now you'll post function call with ALL parameters?

SY.


The xml_string was uploaded before.

The other two parameters are attached.
Re: ORA/1460 in XML [message #547344 is a reply to message #547313] Tue, 13 March 2012 15:21 Go to previous messageGo to next message
joy_division
Messages: 4961
Registered: February 2005
Location: East Coast USA
Senior Member
Spada wrote on Tue, 13 March 2012 13:46

Client 8.0.6.0.0


        SELECT xmltype(xml_string) INTO sub_xml from dual;


Did this even exist in 8.0 client? Surely not in PL/SQL, right?
Re: ORA/1460 in XML [message #547354 is a reply to message #547344] Tue, 13 March 2012 18:52 Go to previous messageGo to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

Well gues I will have to solve this by myself, thank you guys for your time.
Re: ORA/1460 in XML [message #547357 is a reply to message #547354] Tue, 13 March 2012 20:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Your biggest problem is that you need to upgrade your Oracle database and client to versions that support the features that you are trying to use. None of it will work until you do that.
Re: ORA-1460 while working with XML (merged 3) [message #547498 is a reply to message #547252] Wed, 14 March 2012 08:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3106
Registered: January 2010
Location: Connecticut, USA
Senior Member
Spada wrote on Tue, 13 March 2012 11:18
I have a problem when trying to assign the value of a VARCHAR2 variable into a XMLType: ORA-1460 unimplemented or unreasonable conversion requested.


You are not telling the whole story. Look at function declaration - parameter xml_string is a VARCHAR2 while xml you attached is way longer that 4000 characters. It can't be passed to your function. So I don't know how did you get ORA-1460.

SY.
Re: ORA-1460 while working with XML (merged 3) [message #547522 is a reply to message #547498] Wed, 14 March 2012 09:50 Go to previous messageGo to next message
Spada
Messages: 14
Registered: March 2012
Junior Member

syakobson wrote on Wed, 14 March 2012 07:07
Spada wrote on Tue, 13 March 2012 11:18
I have a problem when trying to assign the value of a VARCHAR2 variable into a XMLType: ORA-1460 unimplemented or unreasonable conversion requested.


You are not telling the whole story. Look at function declaration - parameter xml_string is a VARCHAR2 while xml you attached is way longer that 4000 characters. It can't be passed to your function. So I don't know how did you get ORA-1460.

SY.


When I do the debug the parameter it is passed without problems so I get the error ORA-1460 at this line:

SELECT xmltype(xml_string) INTO sub_xml from dual;


Re: ORA-1460 while working with XML (merged 3) [message #547530 is a reply to message #547498] Wed, 14 March 2012 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
syakobson wrote on Wed, 14 March 2012 13:07
Look at function declaration - parameter xml_string is a VARCHAR2 while xml you attached is way longer that 4000 characters. It can't be passed to your function.

It can if the function is being called by PL/SQL, varchar2 limit is 32767 in that case.
However the first thing the function does is use the parameter in a select. At which point the 4000 limit applies.

@Spada - you need to use a CLOB for the xml parameter.
Re: ORA-1460 while working with XML (merged 3) [message #547573 is a reply to message #547530] Wed, 14 March 2012 15:42 Go to previous message
Spada
Messages: 14
Registered: March 2012
Junior Member

Thanks. This worked for me.


[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Wed, 14 March 2012 15:51] by Moderator

Report message to a moderator

Previous Topic: 'Invalid Column Index' error - Java Web applications
Next Topic: Regular expression for exact word and occurance
Goto Forum:
  


Current Time: Wed Dec 08 09:33:20 CST 2021