I'm using one generic code that is available here to execute shell from Oracle procedure.
The generic procedure are as follows -
- Code: Select all
create or replace and compile java source named host as
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/bash";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Failed to print.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
Oracle version -
- Code: Select all
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Elapsed: 00:00:00.01
SQL>
SQL>
Linux Version -
- Code: Select all
[oracle@bpcatapult loader_command]$ cat /proc/version
Linux version 2.6.18-274.7.1.el5PAE (mockbuild@x86-007.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP Mon Oct 17 12:05:46 EDT 2011
And, the problem I'm facing -
Case 1: [Directly running from shell prompt]
- Code: Select all
[oracle@pult loader_command]$ ./TG_Supply.sh
Commit point reached - logical record count 100
Commit point reached - logical record count 200
Commit point reached - logical record count 300
.
.
.
.
Commit point reached - logical record count 53051
Commit point reached - logical record count 53082
Commit point reached - logical record count 53113
Commit point reached - logical record count 53144
Commit point reached - logical record count 53175
Commit point reached - logical record count 53206
Commit point reached - logical record count 53237
Commit point reached - logical record count 53268
Commit point reached - logical record count 53299
Commit point reached - logical record count 53330
Commit point reached - logical record count 53640
And, the count it shows -
- Code: Select all
SQL>
SQL> select count(*) from tg_sup;
COUNT(*)
----------
53342
Elapsed: 00:00:00.03
SQL>
Case 2: [Running from Oracle Procedure]
- Code: Select all
SQL> declare
2 err_cd number;
3 err_desc varchar2(500);
4 begin
5 dbms_java.set_output(1000000);
6 host(p_command => '/a/mis/Sqlloader_script/loader_command/ST_det.sh');
7 dbms_output.put_line('Successfully Executed SQL Loader Command');
8 exception
9 when others then
10 err_cd := 1;
11 err_desc := substr(sqlerrm,1,500);
12 dbms_output.put_line(err_desc);
13 end;
14 /
And, the output count is -
- Code: Select all
SQL>
SQL> select count(*) from tg_sup;
COUNT(*)
----------
42476
Elapsed: 00:00:00.03
SQL>
And, from Unix prompt it shows -
- Code: Select all
[oracle@pult loader_command]$ ps -ef|grep -i "ST_det.sh"
oracle 14565 14485 0 11:00 ? 00:00:00 /a/mis/Sqlloader_script/loader_command/ST_det.sh T_det.sh
oracle 14608 13619 0 11:01 pts/1 00:00:00 grep -i st_det.sh
In first case, It loads 53K+ & finished successfully, whereas in the second case it loads 42K+ and then the program seems to be hanged.
One more thing I've noticed now.
Until I killed the session from shell, rest of the records won't insert.
But, SQL*Plus session still remain in freeze state even this forceful methods loads all the data.
What might be the reason for this strange action?
Really looking for some suggestion.
This is working perfectly in Windows server.
Thanks.
