Database Software

Hi,
I am trying to create a PL/SQL stored procedure that can return XML from a relational table. In order to get it working, I tried the following code.

SQL> CREATE OR REPLACE PROCEDURE NRTESTA.TestXML (clob_out OUT CLOB) IS
2 l_clob CLOB;
3 l_ctx dbms_xmlquery.ctxHandle;
4 BEGIN
5 l_ctx := dbms_xmlquery.newContext('select * from dual');
6 l_clob := dbms_xmlquery.getXml(l_ctx);
7 clob_out := l_clob;
8 dbms_xmlquery.CLOSECONTEXT(l_ctx);
9 END TestXML;
10 /

Procedure created.

SQL> variable vout CLOB;
SQL> exec NRTESTA.TestXML (:vout)
BEGIN NRTESTA.TestXML (:vout); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NoClassDefFoundError
ORA-06512: at "SYS.DBMS_XMLQUERY", line 22
ORA-06512: at "NRTESTA.TESTXML", line 5
ORA-06512: at line 1

But if I try the same block as an anonymous SQL as below and insert into a table with a colum of type CLOB, it works.

create table temp_clob_ted (l_clob clob) tablespace users;

declare
l_ctx dbms_xmlgen.ctxHandle;
l_clob clob;
begin
l_ctx := dbms_xmlgen.newContext('select * from dual');
l_clob := dbms_xmlgen.getXml(l_ctx);
insert into temp_clob_ted values(l_clob);
dbms_xmlgen.closeContext(l_ctx);
end;
/

PL/SQL procedure successfully completed.


SQL> select * from temp_clob_ted;

L_CLOB
----------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DUMMY>X</DUMMY>
</ROW>
</ROWSET>


SQL>

Can you please help me on this?

Thanks
Roni