Calling external Unix Shell Script and pass a Parameter from Database Trigger

From: <azizz_at_yahoo.com>
Date: 23 Feb 2006 14:31:40 -0800
Message-ID: <1140733900.675384.276590@i39g2000cwa.googlegroups.com>

Hi everyone,

Summary: I am having a problem passing a value of a plsql variable to an external Unix shell script using extproc.

I am using the Oracle suggested method of creating and executing external procedures via extproc by creating an Oracle Library, function and a small C program along with tnsnames.ora and listener.ora modifications.

Everything works great as outlined, except that I can't seem to pass a variable from my database trigger routine to the shell script I am calling.

Here is the trigger code:

• Database Trigger-------------------------------------------------- CREATE OR REPLACE TRIGGER capture_locked_users AFTER SERVERERROR ON DATABASE DECLARE v_current_user varchar(60);

BEGIN
   IF (is_servererror (28000))
   THEN    v_current_user := SYS_CONTEXT('USERENV','CURRENT_USER');

   shell('/home/oracle/bin/alert_locked_users.sh v_current_user');

    END IF;
END capture_locked_users;

---

Here is the C code shell.c that is compiled and shared with Oracle as the "shell_lib" library.

• Shell.c -------------------------
  #include<stdio.h>
  #include<stdlib.h>
  #include<string.h>
  

void sh(char *command)
  {
    int num;
    num = system(command);
  }

---

Here is the Oracle function that interacts with the share library:

---

CREATE OR REPLACE PROCEDURE shell (command IN CHAR) AS
EXTERNAL
   NAME "sh"
   LIBRARY shell_lib
   LANGUAGE c
   PARAMETERS (command STRING);
/

---

I have the tnsnames and the listener parts and they are working fine.

my trigger fires successfully on the database error and the shell script "/home/oracle/bin/alert_locked_users.sh" is called and it captures $1 and writes it to a file as a test. $1 is meant to capture v_current_user data.

The problem is that it only sees "v_current_user" literally not its value, which is the user I intend to alert via email. I can't seem to make it pass the data of this variable.

Is there anything I am missing in the code, do I need to modify something to get this working.

Thank you in advance. Received on Thu Feb 23 2006 - 16:31:40 CST