can´t read the Oracle´s XMLTYPE generated in a query in my JSP page

[antonio alvarez]

Hi, I´ve been working on an application that return an XMLTYPE from an schema package, the XML is generated in a query, not read it in a table, and use that variable in a JSP.

The DDL of the package function is this:

CREATE OR REPLACE 
PACKAGE BODY pkg_modulos
IS
    FUNCTION obtenerModulos (pAnio IN NUMBER DEFAULT 2011)
        RETURN XMLTYPE
    IS
        x    XMLTYPE;
    BEGIN
        SELECT  XMLELEMENT ("tree", XMLAttributes (0 AS "id"),
                    (SELECT  DBMS_XMLGEN.getxmltype(DBMS_XMLGEN.newcontextfromhierarchy('SELECT level,
                                          XMLElement("item",
                                                                      XMLAttributes(
                                                                m.idmodulo as "id",
                                                                m.nombre AS "text"))
                            FROM            modulos m
             WHERE m.anio = 2011
CONNECT BY   PRIOR m.idmodulo = m.padre
START WITH   m.padre IS NULL'))
                        FROM     DUAL))
                        arbolXML
          INTO  x
          FROM  DUAL;
 
        RETURN x;
    END;                                                                          
END;   

as far as I see there shouldn´t be any problems with this stored function

The problem is on the JSP code

this is my code:

<?xml version="1.0" encoding="UTF-8"?>
<%@ page contentType="text/xml; charset=UTF-8" import="java.io.*,java.lang.*,java.sql.*,java.util.*,oracle.jdbc.*,oracle.xdb.*, oracle.jdbc.OracleTypes,oracle.jdbc.pool.OracleDataSource"%>
<% 
  Connection dbconn;
  OracleDataSource ods = new OracleDataSource();
  OracleCallableStatement stmt;
    try
    {
	    String hostname="localhost";
	    String dbname="xe";
	    String usr="seguridad";
	    String pass="seguridad";
	    String URL = "jdbc:oracle:thin:@"+hostname+":1521:"+dbname;
	    ods.setURL(URL);
        ods.setUser(usr);
        ods.setPassword(pass);
	    dbconn = ods.getConnection();
		String st=request.getParameter("st").toString();		
		stmt = (OracleCallableStatement)dbconn.prepareCall( "begin ? := "+st+"; end;" );
		stmt.registerOutParameter(1,OracleTypes.OPAQUE,"SYS.XMLTYPE");		
		stmt.execute();
		XMLType xml = null; 
		xml = (XMLType) stmt.getObject(1);
		System.out.println(xml.getStringVal());
		stmt.close();
		dbconn.close();
    }
    catch (SQLException s)
    {
      out.println("SQL Error: " + s.toString());
	  System.out.println("SQL Error: " + s.toString());
    }
%>

I´m getting te error:

SQL Error: java.sql.SQLException: Inconsistent java and sql object types: SYS.XMLTYPE

Making a debbuging I identified that the exception is been raised in this line:

xml = (XMLType) stmt.getObject(1);

I´ve been checking a lot of online documentation but I´ve not been able to make it work

If anyone could help me please, I don´t know what else to do

Thanks... sorry for the english

[Fatih Keles]

Can you try this one

xml = XMLType.createXML(stmt.getOPAQUE(1));