Skip to main content

Switching between PL/SQL, Java and Shell Scripts

Calling a SQL script from another SQL script:
Suppose we have two script files, main.sql and sub.sql. To call sub.sql from main.sql write in main.sql:


The SQL code in sub.sql will be run as if it is written in main.sql

Calling a java method from a shell script:
We can only call main method of a class, by running that class with a java command line execution.

# Here I have already compiled
echo "Running Test"
java Test

Calling a shell script from a Java method:
You can run it using exec method of Runtime Object. Sample code:

import java.util.*;

class Execx{
public static void main(String args[])
Runtime r=Runtime.getRuntime();
}catch(IOException e){

But check this link also:

Calling a stored procedure from Java:

The following code puts the SQL statement into a string and assigns it to the variable createProcedure , which we will use later:

String createProcedure = "create procedure SHOW_SUPPLIERS " +
"as " +
"order by SUP_NAME";

The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:

Statement stmt = con.createStatement();

The procedure SHOW_SUPPLIERS will be compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

JDBC allows you to call a database-stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}”, it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

Calling PL/SQL procedure from shell script
This is easy. In the following example we have var1 available to us even after going in SQL Plus.

sqlplus user/passwd@service <<>
Calling Shell Script from PL/SQL Procedure

This solution by Thomas Kyte has been taken from a Google group. This is a quick and dirty daemon.

Here is a PL/SQL subroutine you can install in your schema:

create or replace procedure host( cmd in varchar2 )
status number;
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;

Here is a Shell script you can run in the background (make sure it is named

#!/bin/csh -f
sqlplus tkyte/tkyte <<"EOF" grep '^#' sed 's/^.//' > tmp.csh
set serveroutput on
status number;
command varchar2(255);
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' command );
dbms_output.put_line( '#exec host.csh' );
end if;
spool off

chmod +x tmp.csh
exec tmp.csh

If you run this in the background (The script), you'll be able to have it execute any host command you want. Run this in one window for example and in anther window go into sql*plus and try:

SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );

You'll see the output of ls -l, uptime, and echo happen on the other window where the shell script is running and you'll get real time feedback from your pl/sql procedure).


Popular posts from this blog

File upload problem: UTF-8 encoding not honored when form has multipart/form-data

The problem that I was facing was something like this. I was using Apache Commons File Upload library to upload and download some file.

I had a form in which user can upload a file and another field 'name' in which she can give any name to the file being loaded.

When I submitted the form, the file was uploaded fine but the value in name field was garbled. I followed all the possible suggestions I found:

<%@page pageEncoding="UTF-8"%> set. <%@page contentType="text/html;charset=UTF-8"%gt; set after the first directive. <meta equiv="Content-Type" content="text/html;charset=UTF-8"> in the head. enctype="multipart/form-data" attribute in the form. accept-charset="UTF-8" attribute in the form.
in the Servlet:
before doing any operations on request object: request.setCharacterEncoding("UTF-8"); For accessing the value

FileItem item = (FileItem);

if (item.isFormField()) {

//For regular…

java.lang.IllegalArgumentException: Malformed \uxxxx encoding

I was getting this exception during build while running ant. Googling didn't help much and I was flummoxed because the same code was running fine till now.

My code reads a text file and does some operations on the basis of values read. It was only when I saw the text files I understood the error. I had copied the text in wordpad and saved it as .txt file. Wordpad had put lot of formatting information before and after the content. Also there was "\par" after every line, which was giving this error.

So moral of the story: if you get this exception check your properties file (or any other file that your code might be reading.)

Easiest way to print Timestamp in Java

Rather than using Calendar.getTime() we can use java.sql.Timestamp class to get the time stamp which gives date and time till millisecond precision.

System.out.println(new Timestamp(System.currentTimeMillis()));

Above will give you current timestamp in this format: 2010-07-27 16:37:45.39