Oracle PL/SQL example with Ballerina

Ok, we are going write a simple HTTP service with Ballerina that exposes stored procedures/functions in an Oracle PL/SQL package!

1. First, let’s get Oracle set up. I simply followed [1] and got a docker container with oracle 11g running.
2. Then installed SQLPlus with the help of [2]. Then connected to my oracle instance in the docker
   container with the following command.
   sqlplus system/oracle@localhost:49161/xe
3. Now we need to get Ballerina installed as well. You can download Ballerina from [3].

Now let’s create a PL/SQL package.

Connect to the Oracle instance with SQLPlus as I have mentioned above and run the following statements.

Here we are first declaring a package. This package has 2 functions and one procedure.

Now we are going to define the functions/procedures in the package.

Following is the table used in the above procedures/functions.

Here is the ballerina program.

Now start the Ballerina service with the following command.

ballerina run oracle_plsql_service.bal

If service was successfully started you should see an output similar to the following.

ballerina: initiating service(s) in ‘oracle_plsql_service.bal’
ballerina: started HTTP/WS endpoint 0.0.0.0:9090

Following are sample cURL requests you can send to this service.

Request:

curl -v http://localhost:9090/dataService/getInfoById/2

Output:

<results><result><first_name>Manuri</first_name><last_name>Perera</last_name><id>2.0</id><salary>20.0</salary></result></results>

Request:

curl -v http://localhost:9090/dataService/getSalary/2

Output:

<Salary><id>2</id><value>20.0</value></Salary>

Request:

curl -v http://localhost:9090/dataService/adjustSalary/1/20

Output:

<Salary><id>2</id><value>24.0</value></Salary>

[1] https://tuhrig.de/3-ways-of-installing-oracle-xe-11g-on-ubuntu/
[2] https://gm.matias.ph/internet-technology/linux-unix/running-oracles-sql-plus-in-linux-ubuntu/
[3] https://ballerina.io/downloads/