Re: Executing external programs from a database procedure.
Date: Tue, 19 Dec 2000 14:33:39 GMT
Message-ID: <91nrk3$rl5$1_at_nnrp1.deja.com>
I've done this by using the UTL_FILE package to write a Unix shell script on the host system. Then, I have a C program that monitors a directory for one of these files. When it finds the file it uses the SYSTEM command to execute it. If your host has a decent scheduling tool on it, one that can monitor a directory, then you don't need to write your own C program.
Here's some sample code. I pulled out a few installation-specific things, but it should still work OK. At least it might be enough to get you started.
CREATE OR REPLACE PROCEDURE sp_write_script (p_emp_id IN VARCHAR2,
p_command IN VARCHAR2,
p_o_err OUT NUMBER,
p_o_msg OUT VARCHAR2,
p_sql_err OUT NUMBER,
p_sql_msg OUT VARCHAR2) IS
/* This routine creates a shell script file on the Unix server.
The contents
of the file are specified in the second parameter.
The name of the file created is p_emp_id_xxxxxxxxx.req where xxxxxxxxx is
a unique temporary file name generated by Unix. Thus, if the employee id passed to this routine is 'billybob', the file created would be
named something like this:
billybob_baaa13569.req
Parameters:
1. Employee ID. Used to help prevent duplicate file IDs.
2. Unix command(s) to execute.
3. On return, result of routine.
0 : No errors.
1 : Warning.
-1 : An error occurred.
4. On return, current error or step message. NULL if no
error.
5. On return, SQLCODE from last SQL statement when there
was an error; otherwise 0.
6. On return, SQLERRM from last SQL statement when there
was an error; otherwise 0.
*/
v_output_file_handle UTL_FILE.FILE_TYPE; v_output_directory VARCHAR2 (512) := 'a directory specified by the initialization parameter utl_file_dir';
v_output_file VARCHAR2 (512);
e_edit_excep EXCEPTION;
BEGIN /* main */
IF p_emp_id IS NULL THEN
p_o_msg := 'Required emp_id parameter missing';
raise e_edit_excep;
END IF;
--sf_tmpname is a call to an external procedure to generate a unique name using Unix's TMPNAM function.
p_o_msg := 'Building output file name'; v_output_file := RTRIM (LTRIM (p_emp_id)) || '_' || sf_tmpname || '.req';
p_o_msg := 'Opening output file ' || v_output_directory || '/' || v_output_file;
v_output_file_handle := UTL_FILE.FOPEN (v_output_directory, v_output_file, 'w');
p_o_msg := 'Writing to output file ' || v_output_directory || '/' || v_output_file;
UTL_FILE.PUT_LINE (v_output_file_handle, '#!/usr/bin/sh');
UTL_FILE.PUT_LINE (v_output_file_handle, p_command);
UTL_FILE.PUT_LINE (v_output_file_handle, 'return 0');
p_o_msg := 'Closing output file ' || v_output_directory || '/' ||
v_output_file;
UTL_FILE.FCLOSE (v_output_file_handle);
p_o_err := 0;
p_o_msg := NULL;
p_sql_err := 0;
p_sql_msg := NULL;
EXCEPTION /* main */
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN (v_output_file_handle) = TRUE THEN
UTL_FILE.FCLOSE (v_output_file_handle);
END IF;
p_o_err := -1;
p_sql_err := SQLCODE;
p_sql_msg := SQLERRM;
END; /* main */
/
/*
- script_launcher.c *
- This program monitors a directory for specific
- file names.
- When such a file is found, this program then
- executes that file. After the execution is complete,
- this program moves the file to the specified
- subdirectory of the directory where the file
- was found.
- Messages are logged to a file with a base name the
- same as this program's executable, an extension of .log,
- located in the $LOG directory. *
- Parameters: *
- 1. Directory to monitor for files. Required.
- 2. Characters in the file name to check for. Required.
- 3. Starting position in the file name to
- check for characters. Zero for match at
- end of name. -1 for match anywhere in the
- name. Required.
- 4. Permissions that the file being processed should
- be set to. 777=rwxrwxrwx, 744=rwxr--r--, etc. Required.
- If we don't care what the permissions are set to, use
- "XXX".
- 5. Subdirectory of parameter 1 that the processed
- files should be moved to. Required.
- 6. Number of seconds to sleep after finding a file to process.
- This is done because files written by the Oracle database
- appear in the directory with non-zero sizes before they
- are completely written. Required. *
- Return values:
- 0 : No errors.
- -1 : An error ocurred. * */
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <dirent.h> #include <limits.h> #include <sys/stat.h> #include <sys/types.h> #include <unistd.h>
/***************************************************************
***************************************************************/
/* Prototypes */
char *match_first_file (char p_directory_to_search[],
char p_characters_to_match[],
int p_starting_position);
int copy_file (char p_source_file[],
char p_dest_file[],
char p_permissions[4]);
int move_file (char p_base_file_name[],
char p_source_dir_name[],
char p_dest_dir_name[]);
int log_message (char p_file[],
char p_message[]);
/***************************************************************
***************************************************************/
int main(int argc, char *argv[])
{
char l_parm0 [MAXNAMLEN],
l_parm1 [PATH_MAX],
l_parm2 [MAXNAMLEN],
l_parm3 [4],
l_parm4 [4],
l_parm5 [PATH_MAX],
l_parm6 [4],
l_full_orig_file_name [MAXNAMLEN],
l_base_file_name [MAXNAMLEN],
l_exec_file_name [MAXNAMLEN],
l_temp_file_name [MAXNAMLEN],
l_sys_cmd [MAXNAMLEN],
l_msg [400];
int l_return_value,
l_result_value;
l_return_value = 0;
strcpy (l_parm0, argv [0]);
log_message (l_parm0, "Starting script_launcher");
if (argc != 7)
{
/* We require all parameters. */
log_message (l_parm0, "Missing parameters");
printf ("\nUsage: this_program_name directory_to_monitor
characters_to_match");
printf ("\n starting_position_to_search (0=at end, -
1=anywhere)");
printf ("\n permissions_of_copied_script (XXX=don't
care)");
printf ("\n subdirectory_for_used_files
sleep_time_after_finding_file");
l_return_value = -1;
}
else
{
strcpy (l_parm1, argv [1]);
strcpy (l_parm2, argv [2]);
strcpy (l_parm3, argv [3]);
strcpy (l_parm4, argv [4]);
strcpy (l_parm5, argv [5]);
strcpy (l_parm6, argv [6]);
}
while (l_return_value == 0)
{
/* Find a file to process. */
strcpy (l_base_file_name, match_first_file (l_parm1, l_parm2,
atoi (l_parm3)));
if (strncmp (l_base_file_name, "MFF_ERR", 7) == 0)
{
sprintf (l_msg, "Error in match_first_file=%s. parm1=%s
parm2=%s parm3=%s",
l_base_file_name, l_parm1, l_parm2,
l_parm3);
log_message (l_parm0, l_msg);
l_return_value = -1;
break;
}
if (strcmp (l_base_file_name, NULL) == 0)
{
/* No files found. Wait to make another pass. */
sleep (10);
/* Insert a break here to have this program make only a
single pass. */
continue;
}
/* At this point we know we found a file to process. */
sleep (atoi (l_parm6));
sprintf (l_full_orig_file_name, "%s/%s", l_parm1,
l_base_file_name);
sprintf (l_msg, "Processing file %s", l_full_orig_file_name);
log_message (l_parm0, l_msg);
/* Copy the file we found as one of our own files since
the execute attribute on the original file might not be enabled
and we might not be able to change it. */
strcpy (l_exec_file_name, l_full_orig_file_name);
strcat (l_exec_file_name, ".sh");
l_result_value = copy_file (l_exec_file_name,
l_full_orig_file_name, l_parm4);
if (l_result_value != 0)
{
sprintf (l_msg, "Error in copy_file=%d\n", l_result_value);
log_message (l_parm0, l_msg);
l_return_value = -1;
break;
}
/* Move the original file to the specified directory. */
sprintf (l_temp_file_name, "%s/%s", l_parm1, l_parm5);
if (move_file (l_base_file_name, l_parm1, l_temp_file_name) !=
0)
{
log_message (l_parm0, "Error in file rename1");
l_return_value = -1;
break;
}
/* Execute our copy of the job. */
system (l_exec_file_name);
/* Move our copy of the job to the specified directory. */
sprintf (l_temp_file_name, "%s/%s", l_parm1, l_parm5);
strcat (l_base_file_name, ".sh");
if (move_file (l_base_file_name, l_parm1, l_temp_file_name) !=
0)
{
log_message (l_parm0, "Error in file rename2");
l_return_value = -1;
break;
}
/* Insert a break here to have this program process only a
single file. */
}
return l_return_value;
} /* End of main */
/***************************************************************
***************************************************************/containing
/*
* This routine searches the directory named in its first parameter * for the first file name with characters that match those specified in * the second parameter. * This routine returns a pointer to the character string
- the name of the first file found. If a file was not found, this routine
- returns NULL. *
- Parameters:
- 1. Name of directory to search.
- 2. Characters of file name to match.
- 3. Starting position of characters to match name on. Zero for
- match at end of name. -1 for match anywhere in the name. *
- Return values:
- NULL : A file was not found.
- MFF_ERR1 : An error ocurred while opening the directory.
- MFF_ERR2 : The number of characters requested to match
- on was longer than the longest valid file name.
- MFF_ERR3 : The directory name requested was longer than the
- longest valid path name. * */
char *match_first_file (char p_directory_to_search[],
char p_characters_to_match[],
int p_starting_position)
{
#define MFF_TOO_MANY_CHARACTERS_TO_MATCH "MFF_ERR2"
#define MFF_BAD_DIR_OPEN "MFF_ERR1"
#define MFF_DIR_NAME_TOO_LONG "MFF_ERR3"
#define MFF_FIND_MATCH_ANYWHERE p_starting_position == -1
#define MFF_FIND_MATCH_AT_END p_starting_position == 0
DIR *l_directory_pointer;
struct dirent *l_directory_entry;
struct stat l_file_status;
int l_number_of_characters_to_match,
l_starting_position,
l_ending_position,
l_filename_position,
l_filename_length,
l_compare_string_position;
char l_compare_string [MAXNAMLEN];
char l_found_name [MAXNAMLEN];
char l_full_path_name [PATH_MAX];
/* When p_starting_position is not zero, we want to extract
strlen (p_characters_to_match) characters from the file name
starting at p_starting_position and compare this to
p_characters_to_match.
When p_starting_position is zero, we want to extract
strlen (p_characters_to_match) characters from the file name
starting at strlen (filename) - strlen (p_characters_to_match) */
l_number_of_characters_to_match = strlen (p_characters_to_match); l_starting_position = p_starting_position - 1;
if (strlen (p_directory_to_search) > PATH_MAX) {
return MFF_DIR_NAME_TOO_LONG;
}
if (! MFF_FIND_MATCH_ANYWHERE)
{
if (l_number_of_characters_to_match > MAXNAMLEN)
{
/* If the character string to match is longer than the
longest possible file name. */
return MFF_TOO_MANY_CHARACTERS_TO_MATCH;
}
}
if ((l_directory_pointer = opendir (p_directory_to_search)) == NULL) {
return MFF_BAD_DIR_OPEN;
}
while ((l_directory_entry = readdir (l_directory_pointer)) != NULL) {
strcpy (l_found_name, NULL);
l_filename_length = strlen (l_directory_entry->d_name);
if (l_filename_length > MAXNAMLEN)
{
/* Don't process any file names that are too long. */
continue;
}
if (strlen (p_directory_to_search) + strlen (l_directory_entry-
>d_name) > PATH_MAX)
{
/* Don't process any path/file name combinations that are
too long. */
continue;
}
/* Now we need to determine the type of the file we found. */
strcpy (l_full_path_name, p_directory_to_search);
strcat (l_full_path_name, "/");
strcat (l_full_path_name, l_directory_entry->d_name);
stat (l_full_path_name, &l_file_status);
if ((l_file_status.st_mode & S_IFMT) == S_IFDIR)
{
/* Don't process any directories. */
continue;
}
if (MFF_FIND_MATCH_ANYWHERE)
{
/* If we want to match characters anywhere in the file name
we don't need to do much work. */
if (strstr (l_directory_entry->d_name,
p_characters_to_match))
{
strcpy (l_found_name, l_directory_entry->d_name);
break;
}
else
{
continue;
}
}
if (l_number_of_characters_to_match > l_filename_length)
{
/* If we are requested to match more characters than
there are in the file name, skip the file. */
continue;
}
if (MFF_FIND_MATCH_AT_END)
{
/* When the starting position we were given is zero, we
have to
figure out what it really is, based upon the length of
the
file name. */
l_starting_position = l_filename_length -
l_number_of_characters_to_match;
}
l_ending_position = l_starting_position +
l_number_of_characters_to_match - 1;
if (l_ending_position > l_filename_length)
{
/* If we end up with an ending position past the end of the
file name
then skip the file. */
continue;
}
l_filename_position = l_starting_position;
l_compare_string_position = 0;
l_compare_string [0] = '\0';
/* Now extract the characters from the file name that we will
compare
to the string to search for. */
while (l_directory_entry->d_name [l_filename_position] != '\0'
&&
l_filename_position <= l_ending_position &&
l_compare_string_position < MAXNAMLEN &&
l_filename_position < MAXNAMLEN)
{
l_compare_string [l_compare_string_position] =
l_directory_entry->d_name [l_filename_position];
l_filename_position++;
l_compare_string_position++;
}
l_compare_string [l_compare_string_position] = '\0';
if (strcmp (p_characters_to_match, l_compare_string) == 0)
{
strcpy (l_found_name, l_directory_entry->d_name);
break;
}
} /* while ((l_directory_entry = readdir (l_directory_pointer)) !=
NULL) */
closedir (l_directory_pointer);
return l_found_name;
} /* End of match_first_file */
/***************************************************************
***************************************************************/
/*
* This routine copies the file named in the first parameter as
- the file named in the second parameter. The core of this routine
- was copied from Kernighan & Ritchie's filecopy function. The permissions
- of the destination file are set according to the third parameter if
- the third parameter is not "XXX". *
- Parameters:
- 1. Name of destination file.
- 2. Name of source file.
- 3. String representing the permissions of the
- new file. 777=rwxrwxrwx, 744=rwxr--r--, etc.
- If this parameter = "XXX" then don't set the
- permissions. *
- Return values:
- 0 : No errors
- -1 : Error in opening source file.
- -2 : Error in opening destination file.
- -3 : Error in closing source file.
- -4 : Error in closing destination file. * */
int copy_file (char p_dest_file[],
char p_source_file[],
char p_permissions[4])
{
FILE *l_input_file,
*l_output_file;
char l_sys_cmd [MAXNAMLEN];
int l_c;
if ((l_input_file = fopen (p_source_file, "r")) == NULL) {
return -1;
}
if ((l_output_file = fopen (p_dest_file, "w")) == NULL) {
return -2;
}
while ((l_c = getc (l_input_file)) != EOF) {
putc (l_c, l_output_file);
}
if (fclose (l_input_file) == EOF)
{
return -3;
}
if (fclose (l_output_file) == EOF)
{
return -4;
}
if (strcmp (p_permissions, "XXX") != 0) {
strcpy (l_sys_cmd, "chmod ");
strcat (l_sys_cmd, p_permissions);
strcat (l_sys_cmd, " ");
strcat (l_sys_cmd, p_dest_file);
system (l_sys_cmd);
}
return 0;
} /* End of copy_file */
/***************************************************************
***************************************************************/*
/*
* This routine moves the file specified from one directory to * another. * * Parameters: * 1. Base name of file to be moved. * 2. Source directory name. * 3. Destination directory name. * * Return values: * 0 : No errors * -1 : Error in rename function.
*/
int move_file (char p_base_file_name[],
char p_source_dir_name[],
char p_dest_dir_name[])
{
char l_source_name [MAXNAMLEN],
l_dest_name [MAXNAMLEN],
l_sys_cmd [MAXNAMLEN];
DIR *l_directory_pointer;
/* Check to see that the destination directory exists. */ if ((l_directory_pointer = opendir (p_dest_dir_name)) == NULL) {
/* Create the destination directory. */
strcpy (l_sys_cmd, "mkdir ");
strcat (l_sys_cmd, p_dest_dir_name);
system (l_sys_cmd);
strcpy (l_sys_cmd, "chmod 777 ");
strcat (l_sys_cmd, p_dest_dir_name);
system (l_sys_cmd);
}
strcpy (l_source_name, p_source_dir_name);
strcat (l_source_name, "/");
strcat (l_source_name, p_base_file_name);
strcpy (l_dest_name, p_dest_dir_name);
strcat (l_dest_name, "/");
strcat (l_dest_name, p_base_file_name);
if (rename (l_source_name, l_dest_name) != 0) {
return -1;
}
closedir (l_directory_pointer);
return 0;
} /* End of move_file */
/***************************************************************
***************************************************************/*
/*
* This routine logs the message passed to it to the file * script_launcher.log located in the directory specified * by the LOG environment variable. * * Parameters: * 1. Base name of file to log to. * 2. Message to be logged. * * Return values: * 0 : No errors * -1 : LOG environment variable not set.
*/
int log_message (char p_file[], char p_message[]) {
FILE *l_output_file;
char l_log_file_name [MAXNAMLEN],
l_str_date_now [9],
l_str_time_now [14],
l_msg [400];
int l_time_len;
struct tm *local;
time_t l_time_now;
if ((getenv("LOG")) == NULL)
{
return -1;
}
l_time_now=time(NULL);
local=localtime(&l_time_now);
strftime(l_str_date_now,100,"%04Y/%02m/%02d",local);
strftime(l_str_time_now,100,"%02H:%02M:%02S:00",local);
strcpy (l_log_file_name, getenv ("LOG"));
strcat (l_log_file_name, "/");
strcat (l_log_file_name, p_file);
strcat (l_log_file_name, ".log");
sprintf (l_msg,"%s %s %s", l_str_date_now, l_str_time_now, p_message);
if (strlen (l_msg) > 395)
{
l_msg [399] = '\0';
}
umask(0);
l_output_file = fopen (l_log_file_name, "a");
fprintf (l_output_file, "%s\n", l_msg);
fclose (l_output_file);
return 0;
} /* End of log_message */
/***************************************************************
***************************************************************/
In article <KDJ%5.109856$yO2.3247911_at_telenews.teleline.es>, "Ant" <anorfri_at_teleline.es> wrote:
> Hello !!! > > Now I have a new (and big for me) problem. It's because I must runan exe
> file (a Pro C program) from a stored procedure. I run Oracle (8.0.4.0.0) on
> Compaq Unix (OSF1) V4.0. I've tried to do this whit the "host" built in,
> but it's not supported on stored program units. So, How could I run an exe
> file from Oracle? > > Thanks > > Ant > >
Sent via Deja.com
http://www.deja.com/
Received on Tue Dec 19 2000 - 15:33:39 CET