Help with returning XML from Oracle function to JSP

From: Bryan Jackson <bryan7902_at_hotmail.com>
Date: 18 Nov 2003 14:49:31 -0800
Message-ID: <3d482a51.0311181449.2ecf5084_at_posting.google.com>

Greetings,

(I am an Oracle newbie -- been working with SQLServer for quite some time, however. I'm using Oracle9i and Oracle9i JDeveloper v9.0.3.1 (build 1107) for my programming environment).

I'm trying to get an Oracle function to return XML to a JSP page, but am having some problems (mostly Oracle errors). Let me start by showing you what I've done so far.

I have a test table, PERSON, with the following basic structure:

Name                  Null?    Type
--------------------- -------- ---------------
PERSON_ID             NOT NULL NUMBER(19)
FNAME                 NOT NULL VARCHAR2(30)
LNAME                 NOT NULL VARCHAR2(30)

I have successfully called an Oracle function and received a "static" return value in my JSP page, with the following excerpt of code (the function exemplifed here as "myFunc()"):

<%_at_ page import="java.sql.*"%>

<%
Connection oCn = null;
CallableStatement oCallStmt = null;
String sRetVal = null;

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); oCn = DriverManager.getConnection(sDBConn, sDBUser, sDBPass);

oCallStmt = oCn.prepareCall("{? = call myFunc()}"); oCallStmt.registerOutParameter(1, java.sql.Types.VARCHAR);

oCallStmt.execute();

sRetVal = oCallStmt.getString(1);

oCallStmt.close();
oCn.close();
%>

Now I have created a new Oracle function named "getXMLTest" as follows:

01 FUNCTION getXMLTest
02 RETURN CLOB
03
04 AS
05
06 oXML CLOB;
07
08 BEGIN

09       SELECT
10          XMLElement("Person", 
11             XMLAttributes(Person_ID AS ID),
12             XMLForest
13                (
14                FNAME AS "FirstName", 
15                LNAME AS "LastName"
16                )
17             )
18          INTO oXML
19          FROM PERSON;
20 			
21        RETURN oXML;

22 END; Here's where the trouble starts. First of all, when I try to compile the function, I get the following errors in JDeveloper:

• Error(10,65530): PL/SQL: SQL Statement ignored
• Error(11,15): PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -

When I take the SELECT statement by itself and run it in SQL*Plus, it outputs the XML just fine. So what is going wrong here?

I had intended on using the above JSP (with one mod, defining the registerOutParameter as java.sql.TsDBHost.Clob) to handle the returned XML. Am I even on the right track here? I want to be able to navigate and manipulate the XML in the JSP page as a document object.

Any helpful direction would be MOST appreciated.

Thanks,
Bryan Jackson Received on Tue Nov 18 2003 - 23:49:31 CET