Table records to Xml in PL/SQL

Question

We currently use Oracle/PL/SQL and Oracle Forms WEB as User Interface.

The thing is that we decided to migrate the UI from Forms to another UI (probably HTML5/ Angular...). Our system architecture is layered in a way that the batch code will remain untouched and all we have to do is to access the GUI Façade from the new technology (still to be chosen). The problem is: All the data this GUI Façade provides (curretly to Oracle Forms) is structured in collections like:

TYPE tp_rc_cod IS RECORD( 
-- Return code 
cd_return NUMBER(2), 
-- Name 
cd_name some_table.name%TYPE 
); 
TYPE tp_table_rc_cod IS TABLE OF tp_rc_cod INDEX BY PLS_INTEGER; 

So, Is there any way to quicky convert the returns of our current GUI Façade from table records to XML or JSON? We thought about building a Wrapper in the middle of the new UI and current GUI Façade, however the system is not small, so it could became hard to build and maybe have performance issues.

I already know that It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types. However, Oracle JDBC drivers support PL/SQL index-by table of scalar element types. If this happens, How can Oracle Forms, for instance, do it? Does it build a Wrapper itself?

Any suggestions?

Answer 1

IF your types are actual oracle types (and not package types) you can cast them to a CLOB containing the xml output with code similar to

declare
l_tab  tp_table_rc_cod := tp_table_rc_cod();
--new variables
l_rc SYS_REFCURSOR;
l_xmlctx number;
l_new_retval CLOB;
begin


l_tab.extend(1);
l_tab(1) := tp_rc_cod( 1, 'testname');

--TABLE RETURN
--return l_tab;

-- XML RETURN
open l_rc for select * from table(l_tab);
l_xmlctx := SYS.DBMS_XMLGEN.NEWCONTEXT(l_rc);

l_new_retval := dbms_xmlgen.getXML(l_xmlctx);
DBMS_XMLGEN.closeContext(l_xmlctx);
--return l_new_retval;
end;
/

but as you see it is still some effort. and there are other DBMS_XMLGEN options you'd probably want to set.

I also think oracle 12 does remove the "oracle type" requirement but I am not sure.

im not sure that exactly answers your question but I hope it helps