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:

@sub.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.

#!/bin/ksh
# Here I have already compiled Test.java
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.*;
import java.io.*;

class Execx{
public static void main(String args[])
{
Runtime r=Runtime.getRuntime();
try{
r.exec("javaex.sh");
}catch(IOException e){
e.printStackTrace();
}
}
}

But check this link also: http://www.javaworld.com/javaworld/jw-12-2000/jw-1229-traps.html

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 " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"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();
stmt.executeUpdate(createProcedure);

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.

#!/bin/bash
var1='test'
PROC_DATE="'2002-03-22'"
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 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

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

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

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).