SOAP Service and Client Example

Server: This test server will query a MySQL database and return tables with names matching the selection criteria.

#!/usr/bin/perl
#use SOAP::Lite +trace=>"all";
use SOAP::Transport::HTTP;

my $daemon = SOAP::Transport::HTTP::CGI
      ->new (LocalPort => 8080)
      ->dispatch_to('Services')
      ->handle;

BEGIN {
	package Services;
	use vars qw(@ISA);
	@ISA = qw(Exporter SOAP::Server::Parameters);
	use SOAP::Lite;
	use DBI;
	use Data::Dumper;
	$SOAP::Constants::DO_NOT_USE_CHARSET = 1;

	# sample service to list schemas and tables
	sub listTables {
	        my $self = shift;
	        my $envelope = pop;
	        my $wsdl = ($envelope->{'_content'}->[1]->{'xmlns:tns'} =~ /wsdl/)?1:0;
		my $dbh;
		my $query;

		# Grab details
		for (qw/database tablename/) {
		        eval {$envelope->dataof("//$_")->value};
		        &makeFault("Missing or invalid $_") if $@;
		        $query->{$_} = $envelope->dataof("//$_")->value unless $@;
		}        

		# Connect to database
	        my $dbh = DBI->connect ("DBI:mysql:database=mysql","root","xxxxxx", { PrintError => 0, RaiseError => 0 }) or &makeFault($DBI::errstr);

		# Run query
        	my $sth = $dbh->prepare("select table_schema, table_name from information_schema.tables where table_schema like ? and table_name like ?") or &makeFault($DBI::errstr);
        	$sth->execute($query->{'database'}, $query->{'tablename'}) or &makeFault($DBI::errstr);

		my $type = 'xsi:type="xsd:string"';
		my $xmlbody = "";
		while (my @row = $sth->fetchrow_array()) {
			$xmlbody .= "<tableRow>";
			map {$xmlbody .= "<col $type>" . $_ . "</col>"} @row;
			$xmlbody .= "</tableRow>\n";
		}

		my $xml = "
		<response>
		    <table>
			<colHeading $type>Database</colHeading>
			<colHeading $type>Table Name</colHeading>
			<table>$xmlbody</table>
		    </table>
		</response>";

		# Return different content depending on how its called
   		my $result = ($wsdl)
   			?SOAP::Data->name('response')->type('string')->uri('')->value($xml)
   			:SOAP::Data->type('xml' => $xml);
        	return $result;
	}

	sub makeFault {
		my ($faultString) = @_;
		die SOAP::Fault->faultcode("Services")->faultstring($faultString);
	}
}

Client: This test client will query the server and dump the response

use SOAP::Lite;
#use SOAP::Lite +trace=>"all";
use Data::Dumper;
use strict;

my $client = SOAP::Lite->proxy("http://localhost:8080/soap/test.pl")->uri("Services");
my $class =
{
	database => 'information%',
	tablename => 'column%',
};

my $soapResponse = $client->listTables(
	SOAP::Data->name('listTablesDetails' => $class)
);
checkResponse($soapResponse);
print Dumper($soapResponse->result);

sub checkResponse() {
	my ($soapResponse) = (@_);
	if ($soapResponse->fault)
	{
		print $soapResponse->faultcode, ": ", $soapResponse->faultstring, "\n";
		exit();
	}
}

WSDL: This WSDL is compatible with .NET

<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://localhost/wsdl/Services.wsdl" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsd1="http://localhost/schema" targetNamespace="http://localhost/wsdl/Services.wsdl" name="Services">
	<types>
		<xsd:schema targetNamespace="http://localhost/schema" xmlns="http://www.w3.org/2001/XMLSchema">
			<xsd:complexType name="listTablesDetails">
				<xsd:sequence>
					<xsd:element name="database" type="xsd:string" minOccurs="1" maxOccurs="1"/>
					<xsd:element name="tablename" type="xsd:string" minOccurs="1" maxOccurs="1"/>
				</xsd:sequence>
			</xsd:complexType>
		</xsd:schema>
	</types>
	<message name="listTablesRequest">
		<part name="listTablesDetails" type="xsd1:listTablesDetails"/>
	</message>
	<message name="listTablesResponse">
		<part name="response" type="xsd:string"/>
	</message>
	<portType name="PortType">
		<operation name="listTables">
			<input message="tns:listTablesRequest"/>
			<output message="tns:listTablesResponse"/>
		</operation>
	</portType>
	<binding name="Binding" type="tns:PortType">
		<soap:binding style="rpc" transport="http://schemas.xmlsoap.org/soap/http"/>
		<operation name="listTables">
			<soap:operation soapAction="Services#listTables"/>
			<input>
				<soap:body use="encoded" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" namespace="Services"/>
			</input>
			<output>
				<soap:body use="encoded" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" namespace="Services"/>
			</output>
		</operation>
	</binding>
	<service name="Service">
		<port name="Port" binding="tns:Binding">
			<soap:address location="http://localhost:8080/soap/test.pl"/>
		</port>
	</service>
</definitions>

mod_plsql XML example

To add a mod_plsql procedure to an Oracle application server is pretty straightforward. The first step is to add the DAD to the server using the console.

After logging into the application server console (http://app:1156) select HTTP_server > Administration > PLSQL > Create DAD.

Add the database connectivity information and restart the server. Then create the stored procedures to access the database and return XML. e.g.

CREATE OR REPLACE PROCEDURE web_getservices (p_account_no varchar2) AS
BEGIN
DECLARE
 ctx    dbms_xmlgen.ctxHandle;
 xml    CLOB;
 amount integer:= 5000;
 position integer := 1;
 charString varchar2(5000); --set to varchar2 max limit

BEGIN

  owa_util.mime_header ('text/xml');
  dbms_lob.createtemporary(
    lob_loc => xml,
    cache => true, -- this is the line to watch
	dur => dbms_lob.session
  );

  ctx := dbms_xmlgen.newContext('SELECT * FROM access_no_details WHERE account_no = '|| p_account_no);
  dbms_xmlgen.setRowTag(ctx, 'row');
  xml := dbms_xmlgen.getXML(ctx);
  dbms_xmlgen.closeContext(ctx);
  if length(xml) > 0 then
    dbms_lob.open(xml,dbms_lob.lob_readonly); -- Open the file in READ_ONLY mode
    loop
      dbms_lob.read(xml,amount,position,charString); --Read until end
      -- DEBUG dbms_output.put_line (charString);
      htp.prn(charString); -- generates the specified string,
      --not terminated with a newline.
      position := position + amount;
    end loop;

  else
    htp.prn('<ROWSET/>');
  end if;

exception
  when no_data_found then
     dbms_lob.close(xml);
     dbms_lob.freetemporary(xml);

  when others then
    htp.prn('<ROWSET><ERROR>'||SQLERRM||'</ERROR></ROWSET>');
END;

END web_getservices;
/