Home » SQL & PL/SQL » SQL & PL/SQL » SAVING/LOADING DATA TO/FROM MXL
SAVING/LOADING DATA TO/FROM MXL [message #208248] Fri, 08 December 2006 16:32 Go to next message
AACG80
Messages: 1
Registered: December 2006
Junior Member

Hi,

I´m saving data from multiple tables (through a join select) into a clob variable in xml format, in this way:

FUNCTION export_as_xml(id INTEGER) RETURN CLOB AS
v_lob CLOB;

ctx DBMS_XMLGEN.ctxHandle;

CURSOR c_all_in_one IS SELECT
CURSOR (SELECT R.*,
CURSOR (SELECT H.*,
CURSOR (SELECT *
FROM RES_CLIE C
WHERE C.ID_RHAB = H.ID_RHAB) CLIENTES
FROM RES_HABI H
WHERE R.ID_RESE = H.ID_RESE) HABITACIONES
FROM RES_RESE R, RES_REP RR
WHERE R.ID_RESE = RR.ID_RESE AND R.ID_RESE = id) RESERVAS
FROM dual;

TYPE t_ref_cursor IS REF CURSOR;
v_ref_cursor t_ref_cursor;

BEGIN
OPEN c_all_in_one;
FETCH c_all_in_one INTO v_ref_cursor;

ctx := DBMS_XMLGEN.newContext(v_ref_cursor);
DBMS_XMLGEN.setRowSetTag(ctx, 'RESERVAS');
DBMS_XMLGEN.setRowTag(ctx, 'RESERVAS_ROW');
v_lob := DBMS_XMLGEN.getXML(ctx);
DBMS_XMLGEN.closeContext(ctx);
CLOSE c_all_in_one;
RETURN v_lob;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END export_as_xml;

This generate a xml just like this (when param id = 300):
<RESERVAS>
<RESERVAS_ROW>
<ID_RESE>300</ID_RESE>
<ENTRADA>10/10/06</ENTRADA>
<SALIDA>12/10/06</SALIDA>
<HABITACIONES>
<HABITACIONES_ROW>
<ID_RESE>300</ID_RESE>
<ID_RHAB>200</ID_RHAB>
<NUMERO>2</NUMERO>
<CAPACIDAD>4</CAPACIDAD>
<CLIENTES>
<CLIENTES_ROW>
<ID_RHAB>200</ID_RHAB>
<NOMBRE>Thomas</NOMBRE>
<ID_CLIE>10</ID_CLIE>
<APELL>Smith</APELL>
</CLIENTES_ROW>
</CLIENTES>
</HABITACIONES_ROW>
<HABITACIONES_ROW>
<ID_RESE>300</ID_RESE>
<ID_RHAB>300</ID_RHAB>
<NUMERO>3</NUMERO>
<CAPACIDAD>4</CAPACIDAD>
<CLIENTES>
<CLIENTES_ROW>
<ID_RHAB>300</ID_RHAB>
<NOMBRE>Anne</NOMBRE>
<ID_CLIE>20</ID_CLIE>
<APELL>Rice</APELL>
</CLIENTES_ROW>
</CLIENTES>
</HABITACIONES_ROW>
</HABITACIONES>
<RESERVAS_ROW>
</RESERVAS>

Fine, here all is fine, but now, when a try to reverse the process there is an exception. I do the following:

FUNCTION import_xml(p_data IN OUT CLOB) RETURN BOOLEAN AS

ctx DBMS_XMLSAVE.ctxType;
v_xml XMLType;
v_err_msg VARCHAR(4000);
v_rows NUMBER;

BEGIN
v_xml := XMLType(p_data);

ctx := DBMS_XMLSAVE.newContext('RES_RESE');
v_rows := DBMS_XMLSAVE.insertXML(ctx, p_data);
DBMS_XMLSAVE.closeContext(ctx);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_err_msg := SQLERRM();
RETURN FALSE;
END;
END import_xml;

The exception occur while trying insert data into table RES_RESE, the table has almost the same structure that xml except element <HABITACIONES></HABITACIONES>. This element contains data of table RES_HABI, and the same occur with element <CLIENTES>, that contains data of table RES_CLIE.

The error message is:
ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: No hay ninguna fila que modificar: falta la etiqueta de delimitaci\u3BA0de fila. Especifique la etiqueta de delimitaci\u3BA0de fila correcta.a.


Someone can help me ????

Thanx a lot
AACG80
Re: SAVING/LOADING DATA TO/FROM MXL [message #208250 is a reply to message #208248] Fri, 08 December 2006 16:35 Go to previous message
BlackSwan
Messages: 25221
Registered: January 2009
Location: SoCal
Senior Member
29532, 00000, "Java call terminated by uncaught Java exception: %s"
// *Cause: A Java exception or error was signaled and could not be
// resolved by the Java code.
// *Action: Modify Java code, if this behavior is not intended.
Previous Topic: Job control via stored procedures
Next Topic: SYS.XMLTYPE AND OUTPUT
Goto Forum:
  


Current Time: Tue Feb 21 01:42:54 CST 2017

Total time taken to generate the page: 0.11400 seconds