Calling PL/SQL code in Java

In this blogpost, I will show you how to interact with Oracle PL/SQL code in Java. Before we continue with the Java code, let's create a simple Oracle package we can use to test our code.

Compile the following package specification and body in the Oracle database.

Package specification:

create or replace
package test_package as

  procedure test_procedure(v varchar2);

  function test_function(v varchar2) return varchar2;

end test_package;

Package body:

create or replace
package body package test_package as

procedure test_procedure(v varchar2) is
begin
    null;
end;

function test_function(v varchar2) return varchar2 is
begin
    return v;
end;

end test_package;

The package contains a procedure and a function that returns a VARCHAR2 type. We can call the procedure with the following Java code:

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{call test_package.test_procedure(?)}");
cs.setString(1, "Calling test_procedure!");
cs.executeUpdate();
cs.close();

The function is slightly different, because we have to register the type the function returns.

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{? = call test_package.test_function(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, "Calling test_function!");
cs.execute();
String s = cs.getString(1);
cs.close();

The function can also be called in another way. Like this:

DataSource ds = ...
Connection con = ds.getConnection();
PreparedStatement ps =
    con.prepareStatement(
        "SELECT test_package.test_function(?) S FROM dual"
    );
ps.setString(1, "Calling test_function!");
ResultSet r = ps.executeQuery();
String result = null;

if (r.next()) {
    result = r.getString("S");
}

con.close();

This way is similar to executing a standard SQL-statement.

Daily remote sync Oracle refresh group

One way to integrate Oracle systems is to share data between databases. In this example we will create a group of materialized views, which is then daily synced with a remote database.

In this scenario we have a couple of remote tables on an external Oracle database. The tables can be accessed using a database link we create.

create public database link <link_name>  connect to <schema>  using '<tns_service_name>';

The <link_name> variable is the name you want to use for the database link. The <schema> variable is the schema name of the remote database. The <tns_service_name> variable is the TNS-name of the remote database.

We fill in these variables with example data.

create public database link MY_DB_LINK;  connect to REMOTE_SCHEMA  using 'remote_database';

Now we create two materialized views of the two remote tables using the database link we just created.

CREATE MATERIALIZED VIEW m_table1 AS
SELECT col1, col2 FROM table1@remote_database;
CREATE MATERIALIZED VIEW m_table2 AS
SELECT col1, col2 FROM table2@remote_database;

Finally, we create the refresh group using the PL/SQL code below. The materialized views are refreshed every day at 6:00 in the morning.

begin
  dbms_refresh.make(
    name => 'mviews',
    list => 'm_table1,
    m_table2',
    next_date => to_date('06:00:00', 'hh24:mi:ss'),
    interval => 'sysdate+1'
  );
end;

To check if everything works, the following internal tables of Oracle can be queried to see if everything we created registered successfully by Oracle.

• dba_mviews
  
• dba_refresh
  
• dba_refresh_children
  
• dba_jobs
  

Sometimes the job just doesn't want to start. Maybe, the database is configured not allowed to start database jobs. Use the following query to check the number of jobs allowed to run on the database.

select t.value from v$parameter t
where name='job_queue_processes';

The number must be greater than 0. The standard value is 4. You can alter the value using:

alter system set job_queue_processes = 4;

Output in MS Excel-format for browser in PL/SQL

This example will show how to output Tab-Separated-Values with Oracle PL/SQL for Microsoft Excel in a browser. This is not exactly JEE, but the main idea can also be applied in Java.

Create a public accessible procedure that outputs Tab-Separated-Values. Make sure the execute rights are granted on user 'PUBLIC' for the procedure, otherwise the procedure is not allowed to be called by public users.

procedure example is
    cursor c_cursor is
        select col1, col2
        from table;

    r_row c_cursor%rowtype;
begin
    -- Print TSV header.
    owa_util.mime_header('application/vnd.ms-excel');

    htp.print('COL1' || CHR(9) ||
              'COL2' || CHR(9) ||
              CHR(13));

    open c_cursor;
    loop
      fetch c_cursor into r_row;
      exit when c_cursor%NOTFOUND;

      -- Print TSV row.
      htp.print(r_row.col1 || CHR(9) ||
                r_row.col2 || CHR(9) ||
                CHR(13));
    end loop;
    close c_cursor;
end example;

The main point in this procedure is to let the browser know we are dealing with output intended for Microsoft Excel. We use this procedure to set the MIME-type in the HTTP-header of the response:

owa_util.mime_header('application/vnd.ms-excel');

The browser will then forward the output to MS Excel for processing.

In the procedure we output the values separated by tabs, which are generated with the PL/SQL function CHR(9). The line is then closed with a new-line using CHR(13).

The output is accessible using the URL structure:

http://<hostname>/pls/portal/<schema>.<package>.<procedure>