I want to call a who command of OS and record in a table. So I saw an example(attached in the bottom) in Metalink , how to use Java to call OS Commands.
Everything compiled fine, but the program is not calling any shell program... It gives 0 PL/SQL procedure successfully completed. But does not show me any results in the window and also not calling any shell program... Something needs to be done in the code... Any help would be appreciated...
THANKS
ATUL
Subject: Calling Operating System Commands from PL/SQL - The Java Way
Doc ID: Note:165256.1 Type: SAMPLE CODE
Last Revision Date: 21-OCT-2005 Status: PUBLISHED
PURPOSE
-------
This Note provides a sample Java Class that can be used to call
operating system commands from PL/SQL Stored Functions, Procedures
or Packages. Calling operating system commands can be implemented
using External Procedure call and C language. Please refer to
Note 130799.1 for more details on using this approach.
SCOPE & APPLICATION
-------------------
This Note is intended for any user, analyst or developer who is
looking for a sample code to call operating system commands from
PL/SQL without the need to write C programs.
How to Call Operating System Commands from PL/SQL - The Java Way
----------------------------------------------------------------
Step by Step Instructions to Built the Sample Code.
1- Login to SQL*Plus with a user who has the privilege to create
Java Source and Functions.
2- Create the OSCommand Java Class using the following statement:
--- Cut Code Here ---
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static String Run(String Command){
try{
Runtime.getRuntime().exec(Command);
return("0");
}
catch (Exception e){
System.out.println("Error running command: " + Command +
"\n" + e.getMessage());
return(e.getMessage());
}
}
}
/
--- Cut Code Here ---
3- Create the following Wrapper Function using the following
statement:
--- Cut Code Here ---
CREATE or REPLACE FUNCTION OSCommand_Run(Command IN STRING)
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'OSCommand.Run(java.lang.String) return int';
/
--- Cut Code Here ---
4- Connect as SYS or SYSTEM and grant the following privilege
to your database user used in Step 1:
SQL> Execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor',
'*' );
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor',
'*' );
SQL> Commit;
Note:
-----
o Replace 'YOUR_DATABASE_USER' with your database user name
used in Step 1 above. For example, SCOTT. The user name should
be written in UPPER CASE.
o Commit is mandatory.
5- Test your code, by connecting to your database user used
in Step 1 and run the following PL/SQL Block.
For example.
--- Cut Code Here ---
Set Serverout On
Declare
x Varchar2(2000);
Begin
x := OSCommand_Run('/usr/bin/who > /home/test');
DBMS_OUTPUT.Put_Line(x);
End;
/