run shell script from oracle store procedure

Shell Programming and Scripting
1

hi,

this is urgent..can i run a shell script from store procedure without using java.

2

You can with external procedure calls but it's not as simple as invoking a built-in system procedure.

  1. Create a shared library with C (.so file)
  2. Create a library in PL/SQL (e.g. CREATE OR REPLACE LIBRARY xyz AS '/path/your.so')
  3. Create a PL/SQL interface that uses the library (e.g. CREATE OR REPLACE FUNCTION ... RETURN ... EXTERNAL LIBRARY xyz NAME "your_c_function" LANGUATE C;
  4. Update your TNSNAMES.ORA to allow external procedure calls
  5. Update your listener.ora to allow for external procedure calls

However, having said that, it's probably easier using Java.

3

can i do this without any lang..

4

check out these links....where you will get no of methods...

shell script from oracle
other one

5

The Java solution is simply the best cut it requires specific grants to run various UNIX commands. The first solution using DBMS_PIPE requires that you keep sqlplus resident on a UNIX server for however long that you wish to access UNIX OS commands.

6

Yes tmarikle you are right Java solution can be considered as best solution and widely used.

7

Thanx for reps...
but i need to execute this without any help of java or c..
can it possible?

8

yes that is possible and was suggested by Tmarikle

check out links in my previous post in that link this method is given.

9

What's more, the previous post doesn't mention that the output in the example is still on the server process. There are two more things that you need to be aware of using this approach:

  1. The UNIX command output on the server where sqlplus is running will need to be logged and you will need to utilize UTL_FILE system calls to make use of the output on your client process (or you can store the information in a database table and then read from it).
  2. The example comes with a warning that the UNIX command issued is not checked for safety. You should wrap some additional logic around the sqlplus code to prohibit dangerous commands from being executed.