| <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" |
| "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> |
| <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> |
| <head> |
| <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> |
| <meta http-equiv="Content-Style-Type" content="text/css" /> |
| <meta http-equiv="Content-Script-Type" content="text/javascript" /> |
| <title>Creating from a Stored Function with complex PL/SQL arguments | EclipseLink 3.0 EclipseLink DBWS</title> |
| <meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 1.0.22 Build 1" /> |
| <meta name="date" content="2014-06-10T10:32:29Z" /> |
| <meta name="robots" content="noarchive" /> |
| <meta name="doctitle" content="Creating from a Stored Function with complex PL/SQL arguments" /> |
| <meta name="relnum" content="Release 3.0" /> |
| <link rel="stylesheet" type="text/css" href="../../dcommon/style.css" media="screen" /> |
| <link rel="copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> |
| <link rel="start" href="../../index.htm" title="Home" type="text/html" /> |
| <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> |
| <link rel="prev" href="creating_dbws_services007.htm" title="Previous" type="text/html" /> |
| <link rel="next" href="creating_dbws_services009.htm" title="Next" type="text/html" /> |
| <!-- START: Disqus --><script type="text/javascript"> var disqus_developer = 0; </script><!-- END: Disqus --><!-- START: Sharethis --><script type="text/javascript">var switchTo5x=true;</script><script type="text/javascript" src="http://w.sharethis.com/button/buttons.js"></script><script type="text/javascript" src="http://s.sharethis.com/loader.js"></script> <!-- END: Sharethis --></head> |
| <body bgcolor="#FFFFFF"><iframe id="docheader" frameborder="0" framemargin="0" scrolling="no" src="../../dcommon/header.html"></iframe><script src="http://www.google.com/jsapi" type="text/javascript"></script><script type="text/javascript"> google.load('search', '1', {language : 'en'}); google.setOnLoadCallback(function() { var customSearchOptions = {}; var googleAnalyticsOptions = {}; googleAnalyticsOptions['queryParameter'] = 'q'; googleAnalyticsOptions['categoryParameter'] = ''; customSearchOptions['googleAnalyticsOptions'] = googleAnalyticsOptions; var customSearchControl = new google.search.CustomSearchControl( '016171230611334810008:1hjujor5_fg', customSearchOptions); customSearchControl.setResultSetSize(google.search.Search.FILTERED_CSE_RESULTSET); var options = new google.search.DrawOptions(); options.setSearchFormRoot('cse-search-form'); customSearchControl.draw('cse', options); }, true);</script><link rel="stylesheet" href="http://www.google.com/cse/style/look/default.css" type="text/css" /><div id="cse" style="width:100%;"></div> |
| <div class="header"><a id="top" name="top"></a> |
| <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> |
| <tr> |
| <td align="left" valign="top"><font face="helvetica, arial, sans-serif"><b>Developing Persistence Architectures Using EclipseLink Database Web Services</b><br /> |
| <b>Release 3.0</b><br /> |
| </font></td> |
| <td valign="bottom" align="right" width="144"> |
| <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> |
| <tr> |
| <td> </td> |
| <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br /> |
| </td><td> </td><td align="center"><a href="../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td> </td><td align="center"><a href="../eclipselink_moxy.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td> |
| </tr> |
| </table> |
| </td> |
| </tr> |
| </table> |
| <hr /> |
| <table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center"> |
| <tr> |
| <td align="center"><a href="creating_dbws_services007.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td> |
| <td align="center"><a href="creating_dbws_services009.htm"><img src="../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td> |
| <td> </td> |
| </tr> |
| </table> |
| </div> |
| <!-- class="header" --> |
| <div class="ind"><!-- End Header --><a id="CJAGADDJ" name="CJAGADDJ"></a><a id="TLDBW181" name="TLDBW181"></a> |
| <div class="sect1"><!-- infolevel="all" infotype="General" --> |
| <h1 class="sect1"><font face="arial, helvetica, sans-serif" color="#330099">Creating from a Stored Function with complex PL/SQL arguments</font></h1> |
| <p>Starting with EclipseLink 2.3, you can create a DBWS web service from a stored function that uses complex PL/SQL types as either an <code>IN</code> or return argument.</p> |
| <a id="TLDBW182" name="TLDBW182"></a> |
| <div class="sect2"><a id="sthref57" name="sthref57"></a> |
| <h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Example</font></h2> |
| <p>In this example, the following stored function is used:</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS |
| arec ARECORD; -- temp var |
| BEGIN |
| arec.T1 := ... some processing based upon OLDREC |
| arec.T2 := ... AND FOO |
| arec.T3 := ... |
| RETURN arec; |
| END F1; |
| |
| </pre> |
| <p>Type <code>ARECORD</code> is defined in the PL/SQL package <code>SOMEPACKAGE</code> as follows:</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| CREATE OR REPLACE PACKAGE SOMEPACKAGE AS |
| TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; |
| TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; |
| TYPE ARECORD IS RECORD ( |
| T1 TBL1, |
| T2 TBL2, |
| T3 BOOLEAN |
| ); |
| FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD; |
| END SOMEPACKAGE; |
| |
| </pre> |
| <p>Because PL/SQL types and collection types cannot be transported via JDBC, EclipseLink will generate an anonymous block of PL/SQL code that contains the functions to convert to and from JDBC and PL/SQl types. To be successful, each PL/SQL type or collection type that will appear in an <code>IN</code>, <code>IN OUT</code>, or <code>OUT OF RETURN</code> argument (or any PL/SQL record or collection type that is nested within these arguments) <em>must</em> have an equivalent JDBC type. The name of this type must be in the form <code><package name>_<type name></code>.</p> |
| <p>For this example, the following JDBC types are required:</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111) |
| |
| CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER |
| |
| CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( |
| T1 SOMEPACKAGE_TBL1, |
| T2 SOMEPACKAGE_TBL2, |
| T3 BOOLEAN |
| ) |
| </pre> |
| <p>The DBWSBuilder utility requires a DBWS configuration file as input.</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| <?xml version="1.0" encoding="UTF-8"?> |
| <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> |
| <properties> |
| <property name="projectName">testPLSQLFunction</property> |
| ... database properties |
| </properties> |
| <plsql-procedure |
| name="plsqlfunction" |
| catalogPattern="SOMEPACKAGE" |
| procedurePattern="F1" |
| returnType="SOMEPACKAGE_ARECORD" |
| /> |
| </dbws-builder> |
| |
| </pre> |
| <p>Notice that <code>returnType</code> is set to <code>SOMEPACKAGE_ARECORD</code>. This value indicates a complex type in the generated EclipseLink DBWS schema (as shown below). In this case, it is constructed based on the contents of the <code>SOMEPACKAGE</code> package.</p> |
| <p>Execute the <code>DBWSBuilder</code>, as shown here:</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war |
| |
| </pre> |
| <p>where</p> |
| <ul> |
| <li> |
| <p><code>dbws-builder.xml</code> is the DBWS builder configuration file (as shown previously).</p> |
| </li> |
| <li> |
| <p><code>output_directory</code> is the output directory for the generated files.</p> |
| </li> |
| <li> |
| <p><code>-packageAs</code> is the platform on which the web service will be deployed.</p> |
| </li> |
| </ul> |
| <p>The generated <code>eclipselink-dbws-schema.xsd</code> file follows:</p> |
| <pre xml:space="preserve" class="oac_no_warn"> |
| <?xml version="1.0" encoding="UTF-8"?> |
| <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified"> |
| <xsd:complexType name="SOMEPACKAGE_TBL1"> |
| <xsd:sequence> |
| <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| <xsd:complexType name="SOMEPACKAGE_TBL2"> |
| <xsd:sequence> |
| <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| <xsd:complexType name="SOMEPACKAGE_ARECORD"> |
| <xsd:sequence> |
| <xsd:element name="t1"> |
| <xsd:complexType> |
| <xsd:sequence> |
| <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| </xsd:element> |
| <xsd:element name="t2"> |
| <xsd:complexType> |
| <xsd:sequence> |
| <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| </xsd:element> |
| <xsd:element name="t3" type="xsd:boolean" nillable="true"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| <xsd:complexType name="simple-xml-format"> |
| <xsd:sequence> |
| <xsd:any minOccurs="0"/> |
| </xsd:sequence> |
| </xsd:complexType> |
| <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/> |
| <xsd:element name="SOMEPACKAGE_TBL2" type="SOMEPACKAGE_TBL2"/> |
| <xsd:element name="SOMEPACKAGE_ARECORD" type="SOMEPACKAGE_ARECORD"/> |
| </xsd:schema> |
| </pre></div> |
| <!-- class="sect2" --></div> |
| <!-- class="sect1" --></div> |
| <!-- class="ind" --> |
| <!-- Start Footer --> |
| <div class="footer"> |
| <hr /> |
| <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> |
| <col width="33%" /> |
| <col width="*" /> |
| <col width="33%" /> |
| <tr> |
| <td valign="bottom"> |
| <table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center"> |
| <col width="*" /> |
| <col width="48%" /> |
| <col width="48%" /> |
| <tr> |
| <td> </td> |
| <td align="center"><a href="creating_dbws_services007.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td> |
| <td align="center"><a href="creating_dbws_services009.htm"><img src="../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td> |
| </tr> |
| </table> |
| </td> |
| <td align="center" width="34%"><a href="http://www.eclipse.org/eclipselink/" title="EclipseLink home"><img src="../../dcommon/images/ellogo.png" alt="EclipseLink" width="150" border="0" /></a><br /> |
| |
| |
| <td valign="bottom" align="right"> |
| <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="225"> |
| <tr> |
| <td> </td> |
| <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br /> |
| </td><td> </td><td align="center"><a href="../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td> </td><td align="center"><a href="../eclipselink_moxy.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td> |
| </tr> |
| </table> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <!-- class="footer" --> |
| <div id="copyright">Copyright © 2014 by The Eclipse Foundation under the <a href="http://www.eclipse.org/org/documents/epl-v10.php">Eclipse Public License (EPL)</a><br /> <script type="text/javascript">var LastUpdated = document.lastModified;document.writeln ("Updated: " + LastUpdated);</script> </div><!-- START: Analytics --><script type="text/javascript"> var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-1608008-2']); _gaq.push(['_trackPageview']); (function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })(); </script><!-- END: Analytics --><!-- START: Sharethis --><script>var options={ "publisher": "e2fe9e07-fab6-4f84-83ea-0991b429842c", "position": "right", "ad": { "visible": false, "openDelay": 5, "closeDelay": 0}};var st_hover_widget = new sharethis.widgets.hoverbuttons(options);</script><!-- END: Sharethis --></body> |
| </html> |
