Oracle Pl Sql Soap Example

Input table sql select from accounts order by account id.

Oracle pl sql soap example. This section presents a simple pl sql function and its access using a web service. Instead soap calls oracle indirectly. Today we will learn how to send http post request from oracle database using pl sql and get the response w e can use it with restful soap services to send json xml to api or in any another thing. The oracle9ias soap stored procedure sp provider supports exposing pl sql stored procedures or functions as soap services.

The naming and syntax of the function calls are similar to those of the oracle internet directory c api functions. Account id bal 1 1000 2 2000 3 1500 4 6500 5 500. The service contains a method with 6 parameters 5 of which are custom defined types. Example of using a pl sql function with a web service.

Internally soap is just a protocol standard and the communications with oracle is not direct like using sql. In this tutorial you will learn how to develop a pl sql function and how to call it in various places such as an assignment statement a boolean expression and an sql statement. This package enables pl sql applications to access data located in enterprise wide ldap servers. We will use package utl http and this package starting in database oracle 11g.

The oracle procedure has the oracle sql embedded inside it and this procedure or function retrieves the data from oracle. Applications use the http post method to submit soap requests to native oracle xml db web services. Http request response http. All of the examples i can find for soap api assume simple types of int or string for example.

The oracle9i database server allows procedures implemented in other languages including java and c c to be exposed using pl sql. Similar to a procedure a pl sql function is a reusable program unit stored as a schema object in the oracle database the following illustrates the syntax for creating a function. I am attempting to consume a service from within an oracle pl sql function stored procedure. The following example shows how you can embed pl sql in a high level host language such as c and demonstrates how a banking debit transaction might be done.

The http server in turn invokes a process pl sql java stored procedure. The function takes as input a department id and name and it returns the salary total of all employees. You send a soap message to the http server apache.