Tutorial #98: Calling web services using PL/SQL in Oracle

Web services are widely used to as a means of communication between two multiple devices. Oracle also allows developers to consume web services using certain built-in packages. Moreover, one can extend a relational database’s storage, indexing, and searching capabilities to include web services. Through this tutorial, we will learn how to call a simple web service using PL/SQL in Oracle. We would using a public testing platform for services utilizing JavaScript Object Notation (JSON).

Pre-requisites: Oracle 11gR2 database with *SQLPlus, Windows 7 (64 bit)

Step 1: Writing the PL/SQL query

Open the SQL Plus command prompt and login using your database credentials. Once logged in successfully execute the following command in order to enable DBMS_OUTPUT statements.

set serveroutput on

In order to call web services, Oracle provides the UTL_HTTP package. It helps make hyper-text transfer protocol (HTTP) callouts from PL/SQL and SQL. One can use it to access data on the Internet or to call Oracle Web Server cartridges. We would be creating a stored procedure to use the same. Our PL/SQL procedure would be as follows,

create or replace procedure call_webservice as
  t_http_req  utl_http.req;
  t_http_resp  utl_http.resp; 
  t_response_text VARCHAR2 (2000);

begin
 
  t_http_req:= utl_http.begin_request('http://date.jsontest.com/','GET','HTTP/1.1');
  t_http_resp:= utl_http.get_response(t_http_req);
  UTL_HTTP.read_text(t_http_resp, t_response_text);
  DBMS_OUTPUT.put_line('Response> status_code: "' || t_http_resp.status_code || '"');
  DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||t_http_resp.reason_phrase || '"');    
  DBMS_OUTPUT.put_line('Response> data:' ||t_response_text); 
  utl_http.end_response(t_http_resp);
 
end;

create_procedure

Step 2: Executing the PL/SQL query

Now, execute the above PL/SQL procedure using the below command,

exec call_webservice

Once the procedure has executed successfully you will see the following output,

execute_procedure

Reference: UTL_HTTP in Oracle

Tutorial #89: Execute batch file using Oracle Job Scheduler

Hey everyone!

The Oracle database includes a built-in scheduler that allows scheduling functions and procedures that can be called from any PL/SQL program. The DBMS_SCHEDULER package introduced in Oracle 10g helps to create, run and monitor jobs. In addition, one can also define a job to execute a batch (.bat) file.

In order to run an external job, you need to enable and start the OracleJobScheduler service on the database server. Through this tutorial, we will learn how to execute a batch file using Oracle Job Scheduler.

Pre-requisites: Oracle 11gR2 Enterprise Edition, Windows 7 (64 bit)

Step 1: Create a batch script

Let’s begin by creating a batch script called example1.bat. On Windows, launch any text editor like Notepad and include the following contents.

example1.bat

echo test > F:\programs\calling_job_from_oracle\output\sample.txt

The above batch file will output the word “test” to a text file in the output directory. The batch file will automatically create the text file upon execution.

Step 2: Create Oracle job

Open SQLPlus and log in as a user with highest privileges. Now, let’s create our job definition by executing the below commands in the console.

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB( job_name => 'example_job',
        job_type => 'EXECUTABLE',
        job_action => 'C:\WINDOWS\system32\cmd.exe',
        job_class => 'DEFAULT_JOB_CLASS',
        comments => 'Job to call batch script on Windows',
        auto_drop => FALSE,
        number_of_arguments => 3,
        enabled => FALSE);
 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 1, argument_value => '/q'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 2, argument_value => '/c'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 3, argument_value => '"F:\programs\calling_job_from_oracle\example1.bat"'); 
    SYS.DBMS_SCHEDULER.ENABLE( 'example_job' ); 
END;
/

As seen above, it is important to note that one can’t directly execute a batch script. Instead, we need to open an instance of the command prompt and pass an argument in order to execute the batch script. For instance, /q is the cmd.exe switch to turn off echo where as /c is the switch to terminate the same after completing the specified command. Finally, you can test the job by executing the below PL/SQL statement.

BEGIN
    DBMS_SCHEDULER.RUN_JOB( 'example_job');
END;
/

If the job is executed successfully, you should see a sample.txt file present in the output folder. That’s it for this tutorial. Hope it helps! 🙂

Reference:

1. Oracle Job Scheduler

2. Running a Windows batch file from DBMS_SCHEDULER

Tutorial #18: Calling a PL/SQL procedure in ODI

Hello friends!

Today, I will be demonstrating a short tutorial that will help you call a PL/SQL procedure using Oracle Data Integrator (ODI).

Pre-requisites: Oracle Data Integrator 11g, Oracle 10g Express Edition with *SQL Plus.

I will be creating a procedure that will insert a new row in my database table in Oracle. Through ODI, I will execute/call the same procedure!

The steps are as follows:

Step 1: Create table Product

Open *SQL Plus and connect to the database using your credentials. Now, let’s create Product table.

create table Product(prod_id number, prod_name varchar2(30), price number);

Step 2: Create PL/SQL procedure

Let’s write a simple procedure that will insert a new row in the Product table.

create or replace procedure insert_proc as
begin
insert into Product values(104,'Nokia',2440);
end;
/

Once the procedure has been created successfully, open Oracle Data Integrator studio and connect to your work repository. Using ODI we will simply call the procedure.

If you haven’t created your work repository in ODI please refer this.

Step 3: Create data server, physical and logical schema

To create a new data server, go to Topology navigator, under Technologies,right click Oracle and select new data server. Enter the correct details (including JDBC URL) as per your database connection and click on Save.

creating new data server

creating new data server

creating new data server

creating new data server

Now create physical schema by right clicking the data server and selecting new physical schema.

create physical schema

create physical schema

In the same way, create your logical schema. Make sure you select a Context. In my case I have selected Global.

create logical schema

create logical schema

Step 4: Create new project followed by new procedure

Create a new project named test_project under the Designer navigator. Go to First Folder–>Procedure and right click to create new procedure.

create new procedure

create new procedure

Run the procedure and check the Operator navigator for the status of the operation.

view operator status

view operator status

Finally, check if the new row is inserted by performing a select operation on your table in Oracle.

check product table

check product table