Help with returning XML from Oracle function to JSP

[Bryan Jackson]

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()"):
<%@ page import="java.sql.*"%>

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

Class.forName("oracle.jdbc.driver.OracleDriver").n ewInstance();
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