Undestanding Stored procedure and calling it from SOA Suite db adapter

Stored procedure is a segment of code which contains declarative or procedural SQL statements.It can contains any SQL statement like INSERT, UPDATE ,MERGE or any SQL data definition like CREATE TABLE, ALTER TABLE etc.Also a stored procedure also supports procedure statements such as IF ELSE, WHILE making it not less than any programming language.

Stored procedure has several advantage

They are compiled and stored in database.So they are executed much faster so each time we need not load the sql statment and then compile then run,they are already compiled and are much faster.

Each stored procedure can have its own database priviledges so it helps in securing data also.

The most important is code reusability. Once created, a stored procedure can be reused again and again by multiple applications.

Lets try to write a simple stored procedure we will try to understand the syntax

First of all log in to the database and create a new account for our exercise.
to create a new account use following command

SQL>create user arpit identified by arpit;
User Created

Grant priviledge to arpit user.
SQL>grant dba to arpit;
Grant succeeded.

Now connect to the database using arpit as user and password

SQL> connect
Enter user-name: arpit
Enter password:
Connected.
SQL>

the first command you need to pass is

"SET SERVEROUTPUT ON".It is the SQL command to activate the console output. You only need to issue this command once in a SQL session.

SQL> SET SERVEROUTPUT ON
SQL>


Noe create a simple procedure which will give some standard output.I have used the following.

SQL> CREATE OR REPLACE PROCEDURE arpit
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello Arpit!');
END;
/ 2 3 4 5 6

Procedure created.

Now here consider the following

1>CREATE OR REPLACE PROCEDURE is the fixed command that you have to use while creating a procedure

2>arpit is the name of the procedure

3>the keywords BEGIN...END define a scope and is equivalent to a a java {}

4>the put_line function (in the built-in package dbms_output) displays a string in the SQL*Plus console.

So now our procdure is created now we will check how to execute it.


SQL> exec arpit;
Hello Arpit!

PL/SQL procedure successfully completed.

So you got an idea of how to create a simple procedure and how to execute it.

Now we will create a simple procedure in database and check how we can call it from jdeveloper.

here is my script for the procedure

CREATE OR REPLACE PROCEDURE TRANSFORM (
in1 in number,
out1 out number)
IS
BEGIN
out1 := 2 * in1;
END;
/

This procedure takes an input parameter in1 and produce an output parameter out1

In this procedure the input variable is multiplied with two and then it is assigned to out1.

This isn't a great example but i have limited knowledge in database side but i believe this example will tell you the usages of stored procedure and how it is called in Oracle BPEL.

so here goes my command in sql

SQL> CREATE OR REPLACE PROCEDURE TRANSFORM (
in1 in number,
out1 out number)
IS
BEGIN
out1 := 2 * in1;
END;
/ 2 3 4 5 6 7 8

Procedure created.

Now create a simple bpel process in jdeveloper.Let it be asynchronous only.

Now drag and drop a db adapter in the pallete





choose the database you are trying to connect to





choose the schema in next box and browse to choose the procedure



So it should now look like this



Now drag and drop an invoke activity and connect the invoke with the db adapter that we have created.

Again drag and drop two assign activity one in between the receive and invoke activity and another in between the invoke and callback client activity.

First assign activity assign the input variable to the invoke input variable.



next assign activity map the output of invoke to the output variable



In all your process should look like this



Once process is completed deploy it and check the output

I deployed the process and gave 12 as input



and the output i received is



So i believe you might have got the idea what is a stored procedure and how it is used.