Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API
Packages in the Oracle Database are a fine construct to use for creating a service API at PL/SQL level – that through JDBC or other connections into the database can quickly be exposed at other levels than just internally for PL/SQL. A service API has a number of characteristics, that typically include a structured, well documented contract – definition for the data that goes in and comes out of the service -, complex, nested message structures and the ability to communicate in terms of XML.
Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Presenting such a service-style interface from PL/SQL packages is quite easily realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a contract in terms of nested types quite close resembles the typicall WebService contract that used nested XML documents.
Fortunately it is rather simple to turn the XML input to this second layer and convert it to Oracle Object speak (user defined types) and also to take the result from the innner API that is in terms of the user defined types and turn it into [an] XML [message]. The mechanisms we can use for the conversion from UDT (or ADT) to XML and vice versa are XMLType, SYS_XMLGEN and the TOOBJECT operation on XMLType.
Let’s first create a nested user defined type structure that represents either the input to or the output from the service API:
create or replace type person_t as object ( first_name varchar2(30) , last_name varchar2(30) , birthdate date , gender varchar2(1) ) / create or replace type person_tbl_t as table of person_t / create or replace type team_t as object ( name varchar2(40) , activity varchar2(40) , members person_tbl_t ) /
Next, we create a function that in this case portrays the service API – it returns an instance of team_t, our user defined object:
create or replace function get_team return team_t as begin return team_t( 'The Flyers', 'Hockey' , person_tbl_t( person_t('Johnny', 'Sailing' , sysdate-30*365, 'M') , person_t('Sarah', 'Jackson' , sysdate-25*365, 'F') , person_t('Jim', 'Yates' , sysdate-35.43*365, 'M') ) ); end; /
We can call this function from PL/SQL or SQL:
1 select get_team 2* from dual SQL> / GET_TEAM(NAME, ACTIVITY, MEMBERS(FIRST_NAME, LAST_NAME, BIRTHDATE, GENDER)) -------------------------------------------------------------------------------- TEAM_T('The Flyers', 'Hockey' , PERSON_TBL_T ( PERSON_T('Johnny', 'Sailing', '11-SEP-79', 'M') , PERSON_T('Sarah', 'Jackson', '09-SEP-84', 'F'), PERSON_T('Jim', 'Yates', '08-APR-74', 'M') ))
However, clients that do not know how to handle user defined types are at a loss when they try to leverage this service API. We need techniques to convert from the user defined type to XML and vice versa. First from user defined type to XML:
SELECT sys_XMLGEN(get_team, XMLFormat('TEAM')) from dual / SYS_XMLGEN(GET_TEAM,XMLFORMAT('TEAM')) ----------------------------------------------- <?xml version="1.0"?> <TEAM> <NAME>The Flyers</NAME> <ACTIVITY>Hockey</ACTIVITY> <MEMBERS> <PERSON_T> <FIRST_NAME>Johnny</FIRST_NAME> <LAST_NAME>Sailing</LAST_NAME> <BIRTHDATE>11-SEP-79</BIRTHDATE> <GENDER>M</GENDER> </PERSON_T> <PERSON_T> <FIRST_NAME>Sarah</FIRST_NAME> <LAST_NAME>Jackson</LAST_NAME> <BIRTHDATE>09-SEP-84</BIRTHDATE> <GENDER>F</GENDER> </PERSON_T> <PERSON_T> <FIRST_NAME>Jim</FIRST_NAME> <LAST_NAME>Yates</LAST_NAME> <BIRTHDATE>08-APR-74</BIRTHDATE> <GENDER>M</GENDER> </PERSON_T> </MEMBERS> </TEAM>
The result from SYS_XMLGEN is an XMLType, on which we can perform operation such as XPath extract and XSLT transform
SELECT sys_XMLGen(get_team, XMLFormat('TEAM')).extract('/TEAM/NAME') from dual / SYS_XMLGEN(GET_TEAM,XMLFORMAT('TEAM')).EXTRACT('/TEAM/NAME') --------------------------------------------------------------------- <NAME>The Flyers</NAME>
Then the reverse operation – take the XMLType and create the user defined type instances that map to it:
declare l_xml xmltype; l_team team_t; begin select sys_XMLGen(get_team, XMLFormat('TEAM')) into l_xml from dual ; <strong>l_xml.toobject(l_team);</strong> dbms_output.put_line(l_team.name); dbms_output.put_line('number of players '||l_team.members.count); end; / SQL> / The Flyers number of players 3 PL/SQL procedure successfully completed.
We use the toobject operation on XMLType for this conversion.
XMLType API for PL/SQL (Oracle Documentation as of 9iR2)
- Oracle RDBMS 11gR2 – alter or replace user defined types even when there are dependencies
- Juggling met SQL Types in SQL and PL/SQL – fading borders between Collection and Table
- Creating a message based routing service in ESB
- Creating XML Data Services for Reporting and ESB integration using DBMS_EPG (the internal HTTP/PLSQL Gateway) and XMLType
- Oracle Raptor – Using Snippets (and adding User Defined Snippets) for quicker SQL and PL/SQL coding
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- Read an Excel xlsx with PL/SQL
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation