Oracle shell call

Literally the other day I had a very interesting task - to call a shell script from a trigger in Oracle. The easiest way out in this situation is a Java stored procedure, but the Oracle edition for which it was necessary to perform actions is XE, in which there is no such possibility.
Google gives a lot of tips on how to implement the procedure that refers to the C library, but I didn’t work out such instructions in their pure form, I had to finish a few tips, in which Google also helped me. I no longer remember links to resources where I received answers, but I will try to tell the whole chain of actions.

So, everything begins with the creation of the required C library, which within the system will call the shell. The library code is elementary:

#include <stdio.h> #include <stdlib.h> #include <string.h> void sh(char*); void sh(char* cmd) { system(cmd); } 

After creating the source code file, it is necessary to compile the lib itself, for which we will use gcc and ld. Suppose the source is called shell.c.

 gcc -fPIC -DSHARED_OBJECT -c shell.c ld -shared -o shell.so shell.o 

For me, as for a C # and Java programmer, it turned out to be a surprise that for everything to work, the lib should be compiled on an OS with the same bit depth that is installed on the machine with Oracle.
')
We have a library that is able to perform in the shell what is passed to it by the parameter — great.

The next step is to create a procedure in Oracle. We begin this step by copying the resulting library in $ ORACLE_HOME / bin - as practice has shown, the library may not work if it is located in another way.
In Oracle itself, we create an external library by changing $ ORACLE_HOME to the path.

 create or replace library shell_lib is '$ORACLE_HOME/bin/shell.so'; 

Having a library - create a procedure that will call the function of this library.

 create or replace procedure shell(cmd IN char) as external name "sh" library shell_lib language C parameters (cmd string); 

At this stage, Oracle already has a ready procedure that calls the shell, but for its work, you may need to tune Oracle itself - you need to fix the listener. Go to $ ORACLE_HOME / network / admin / , where we open the tnsnames.ora file and check the presence of the description for connecting via IPC in it, something like this should be written

 EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))) (CONNECT_DATA =(SID = plsextproc)(PRESENTATION = RO)) ) 

If there is no such string, then we create it, if there is, then we look at the KEY value and remember it.
After that, open the listener.ora file lying there and check for the availability of connectivity via the IPC protocol for the listener to which you are connecting, the setting will look something like this:

 (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1)) 

If there is no such setting, add, if there is, check the KEY value with the value from the tnsnames.ora file, they must match.
If at least one tnsnames.ora or listener.ora file has changed, you need to reload the listener:

 lsnrctl stop lsnrctl start 

After performing these simple actions, it is possible to call the shell from any procedure / function / trigger in Oracle.