Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/DBWS/DBWSOverloadStoredProcedure"

Line 7: Line 7:
 
__NOTOC__  
 
__NOTOC__  
  
−
== EclipseLink DBWS Service based on an overloaded Stored Procedure ==
+
== EclipseLink DBWS Service based on an overloaded PL/SQL Stored Procedure ==
−
The use-case for this example is the creation of a Web service that exposes a Stored Procedure (or multiple procedures).
+
The use-case for this example is the creation of a Web service that exposes a PL/SQL Stored Procedure (or multiple procedures).
 
From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore,
 
From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore,
 
the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'.
 
the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'.
Line 14: Line 14:
 
same name, different parameters – are supported.
 
same name, different parameters – are supported.
  
−
The following stored procedures will be used for this example: <source lang="sql" enclose="div">
+
The following PL/SQL stored procedures will be used for this example: <source lang="sql" enclose="div">
−
CREATE OR REPLACE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
+
CREATE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
 
BEGIN
 
BEGIN
 
   // do something
 
   // do something
 
END P1;
 
END P1;
  
−
CREATE OR REPLACE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
+
CREATE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
 
BEGIN
 
BEGIN
 
   // do something
 
   // do something
 
END P1;
 
END P1;
 
</source>
 
</source>
 +
 +
Type <code>TBL1</code> is defined in PL/SQL Package <code>SOMEPACKAGE</code> as follows: <source lang="sql" enclose="div">
 +
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
 +
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
 +
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
 +
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2);
 +
END SOMEPACKAGE;
 +
</source>
  
 
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.
 
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.
Line 34: Line 42:
 
     ... database properties
 
     ... database properties
 
   </properties>
 
   </properties>
−
   <procedure
+
   <plsql-procedure
−
     name="overloadedProcedureTest"
+
     name="overloadedProcedure"
 +
    catalogPattern="SOMEPACKAGE"
 
     procedurePattern="P1"
 
     procedurePattern="P1"
−
    isSimpleXMLFormat="true"
 
 
   />
 
   />
 
</dbws-builder>
 
</dbws-builder>
Line 50: Line 58:
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
  
−
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file is the schema for the Simple XML format:
+
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> follows: <source lang="xml" enclose="div">
−
<source lang="xml" enclose="div">
+
 
<?xml version="1.0" encoding="UTF-8"?>
 
<?xml version="1.0" encoding="UTF-8"?>
−
<xsd:schema
+
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqloverload" xmlns="urn:plsqloverload" elementFormDefault="qualified">
−
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
+
  <xsd:complexType name="SOMEPACKAGE_TBL1">
−
  >
+
      <xsd:sequence>
−
  <xsd:complexType name="simple-xml-format">
+
        <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/>
−
    <xsd:sequence>
+
      </xsd:sequence>
−
      <xsd:any minOccurs="0"/>
+
  </xsd:complexType>
−
    </xsd:sequence>
+
  <xsd:complexType name="simple-xml-format">
−
  </xsd:complexType>
+
      <xsd:sequence>
 +
        <xsd:any minOccurs="0"/>
 +
      </xsd:sequence>
 +
  </xsd:complexType>
 +
  <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/>
 
</xsd:schema>
 
</xsd:schema>
 
</source>
 
</source>
−
The element tags <tt><b>simple-xml-format</b></tt> and <tt><b>simple-xml</b></tt> can be customized by setting the appropriate properties
 
−
on an <tt>sql</tt> operation.
 
 
[[Category:EclipseLink/Example/DBWS]]
 
[[Category:EclipseLink/Example/DBWS]]

Revision as of 15:01, 10 June 2011


EclipseLink DBWS Service based on an overloaded PL/SQL Stored Procedure

The use-case for this example is the creation of a Web service that exposes a PL/SQL Stored Procedure (or multiple procedures). From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore, the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'. Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded - same name, different parameters – are supported.

The following PL/SQL stored procedures will be used for this example:
CREATE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
BEGIN
  // do something
END P1;

CREATE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
BEGIN
  // do something
END P1;
Type TBL1 is defined in PL/SQL Package SOMEPACKAGE as follows:
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2);
END SOMEPACKAGE;

The DBWSBuilder utility requires a DBWS configuration file as input.

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">testOverloadedProcedure</property>
    ... database properties
  </properties>
  <plsql-procedure
   name="overloadedProcedure"
   catalogPattern="SOMEPACKAGE"
   procedurePattern="P1"
 />
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war

where

  • dbws-builder.xml is the DBWS builder XML file above
  • output_directory is the output directory for the generated files
  • -packageAs specifies the platform on which the web service will be deployed
The generated eclipselink-dbws-schema.xsd follows:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqloverload" xmlns="urn:plsqloverload" 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="simple-xml-format">
      <xsd:sequence>
         <xsd:any minOccurs="0"/>
      </xsd:sequence>
   </xsd:complexType>
   <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/>
</xsd:schema>
Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/Examples/DBWS/DBWSOverloadStoredProcedure&oldid=255108"

Copyright © Eclipse Foundation, Inc. All Rights Reserved.