How to execute a shell script from an Oracle trigger
1. Compile the attached C program. It contains the following code
————————————————————–
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
void sh(char *);
void sh( char *cmd )
{
int num;
num = system(cmd);
}
————————————————————–
Compile it using the following command at the UNIX prompt
$cc -G -c shell.c
Make a shared library using the following command at the UNIX prompt
$ld -r -o shell.so shell.o
2. Create the Library and the PLSQL Wrapper body
Login as “system” to the Oracle server.
Library Definition
——————
CREATE LIBRARY shell_lib is
’/local/home/vmachan/lakeeda/db/proc/shell.so’;
/
Note:
The directory in quotes is the current of location of the library that was
created in the steps above.
The absolute path above is what I used for my testing. You will need to use
your directory settings and give the appropriate absolute path.
Login as “system” to the Oracle server.
PL/SQL Wrapper Procedure
————————
create or replace procedure shell(cmd IN char)
as external
name “sh”
library shell_lib
language C
parameters (cmd string);
/
3. Create the sample table
———————–
Login as “system” to the Oracle server.
CREATE TABLE stsh_test
(
a number(2) not null
,b number(2) null
)
/
4. Create the trigger on the above table
————————————-
Login as “system” to the Oracle server.
CREATE OR REPLACE TRIGGER shst_trig
BEFORE INSERT OR UPDATE OF a, b on stsh_test
FOR EACH ROW
DECLARE cmd1 varchar2(255);
BEGIN
cmd1 := ‘smpshl.sh ‘ || :new.a || ‘ > /tmp/smpshl.out’;
execute immediate ‘call shell(:cmd)’ using cmd1;
END;
/
5. Create the sample shell script – smpshl.sh
——————————
#!/usr/bin/ksh
echo “Argument 0 is ” $0
echo “Argument 1 is ” $1
echo “Argument 2 is ” $2
Give Oracle execute permissions to the shell script. (chmod +x smpshl.sh)
6. Make a symbolic link from $ORACLE_HOME/bin to the sample shell script
Login in to UNIX as the ORACLE user
$cd $ORACLE_HOME/bin
$ln -s /local/home/vmachan/lakeeda/db/proc/smpshl.sh smpshl.sh
This ensures that the shell script is found by oracle (is in its PATH).
Note:
The absolute path above is what I used for my testing. You will need to use
your directory settings and give the appropriate absolute path.
At this point, all the setup is done.
To test whether it works, execute the following steps.
1. Login as “system” to the Oracle server.
2. SQL>insert into stsh_test values (1, 2);
3. SQL>exit;
4. $cat /tmp/smpshl.out
Argument 0 is /opt/oracle/product/8.1.6/bin/smpshl.sh
Argument 1 is 7
Argument 2 is
5. If you get the above output, everything works fine.
If for some reason, you do not see this following are some reasons that might help
to troubleshoot.
1. The shell script has to have execute permission granted to everyone, especially
oracle.
2. Make sure all the path names are typed correctly.
You will have to
1. Replace smpshl.sh with your shell script.
2. Pass any additional parameters to the shell script.
3. Ensure that the shell script has its environment set properly if it calls other
executables or shells.
Hi there, is step 6 necessary? In other words, can I keep the script in a separate location and ensure I give the full path to the script in step 4?
Thanks
Hi Sai,
Step 6 is required to exeute the script from any location because ORALCE_HOME/bin is already added in the PATH environment variable. If you would like to go the folder and execute everytime then you can ignore step6.
Regards
Mudhalvan M.M
Hi Mudhalvan,
My script is on Sun Solaris . Everthing is correct but when I am inserting the values in the table stsh_test I am getting following error:
ORA-28575: unable to open RPC connection to external procedure agent
My tnsnames.ora and listener.ora entries are correct.
Please help me.
Thanks in advance
This is because you are using External Procedure which required External Listener running.