| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Executing external programs from a database procedure.
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/
Received on Tue Dec 19 2000 - 09:15:47 CST
 |
 |