Re: Executing external programs from a database procedure.
From: Max Sivkov <maxx_at_itprojects.net>
Date: Tue, 19 Dec 2000 17:16:39 +0200
Message-ID: <91nuai$r6a$1_at_hyppo.gu.net>
Date: Tue, 19 Dec 2000 17:16:39 +0200
Message-ID: <91nuai$r6a$1_at_hyppo.gu.net>
sorry, second one u should add to the listener.ora file
-- Regards MaXX "Max Sivkov" <maxx_at_itprojects.net> wrote in message news:91nu8u$r0r$1_at_hyppo.gu.net...Received on Tue Dec 19 2000 - 16:16:39 CET
> hi
> you can use external procedures(found $ORACLE_HOME/plsql/demo/ext*)
> also u need 2 patch tnsnames.ora, insert following
> extproc_connection_data =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = SID))
> (CONNECT_DATA = (SID = extproc))
> )
>
> and add
> (SID_DESC =
> (SID_NAME = extproc)
> (ORACLE_HOME = /usr/oracle/product/8.0.5)
> (PROGRAM = extproc)
> )
> into SID_LIST_LISTENER 's SID_LIST section
>
>
> --
> Regards
>
> MaXX
> <djsnyder_at_my-deja.com> wrote in message
news: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 */
> >
> > /***************************************************************
> > ***************************************************************/
> >
> > /*
> > * 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
> > containing
> > * 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 run
an 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/
>
>