Sponsored Content
Top Forums Shell Programming and Scripting Executing procedure using script Post 302708313 by jim mcnamara on Monday 1st of October 2012 11:13:43 AM
Old 10-01-2012
See the top line: the proc name followed by \. Add as many procedures as you wish.
They have to be procedures because functions produce a return value. procedures do not.

Code:
for proc in  'dbms_stats.gather_table_stats();' \
                'dbms_stats.foo_table_stats();' \
                'dbms_stats.bar_table_stats();' \
                'last you procedure want goes here with a comma at the end ; '
do
   $ORACLE_HOME/bin/sqlplus -s user/password@oracleinstancename <<EOF
    set lines 120
    set whatever else you want
    DECLARE
    BEGIN
    $proc
    exit
    END;
/
EOF
    echo "$proc Succesfully completed"
done > mylogfile.log

See the two EOF? they have to be placed exactly as shown, with the last EOF has to be in the leftmost column. The /
above the last EOF also should be in the leftmost column.
This User Gave Thanks to jim mcnamara For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

calling procedure from script

Hi All, My script will call a storedprocedure #!/bin/bash # # Script for ........... . ../conf/setting.env # Environment file to set the DATABASE #TODAY=`date '+%Y%m%d_%H'` #echo TODAY = $TODAY sqlplus -s $DATABASE <<EOF spool $TRACKING_LOGDIR/CalcFreqPgsVues_H.log exec... (3 Replies)
Discussion started by: scorpio
3 Replies

2. Shell Programming and Scripting

getting proper o/p from a procedure in a script

foll. is my code snippet. #!/bin/ksh retVal=`sqlplus -s user/passwd@oracle_sid <<EOF SET SERVEROUTPUT ON SIZE 100000 DECLARE STATUS_VALUE CHAR; BEGIN SELECT temp1 INTO STATUS_VALUE FROM sai; DBMS_OUTPUT.PUT_LINE(STATUS_VALUE); END; / SET... (1 Reply)
Discussion started by: sainathdeg
1 Replies

3. Shell Programming and Scripting

Oracle procedure is not executing in uix

Hi Guys, I am trying to tun a oracle proedure throgh unix shell script but it is not running i dont know why ? i have tested this procedure in sqlplus and it was working fine. can you see the script and sql file and let me know where is my mistake. script:bm_chart_table_loading.sh ... (3 Replies)
Discussion started by: shary
3 Replies

4. Shell Programming and Scripting

Executing Procedure from shell script..

Hello, I created a sql file to create a Procedure, and it was successfully created. I created a sql file to execute the procedure, and it did without any errors, but i dont see the data been updated. The Execute procedure.sql script is: BEGIN set serveroutput on size 1000000 execute... (5 Replies)
Discussion started by: msrahman
5 Replies

5. Shell Programming and Scripting

Executing oracle procedure using cronjob

Hi, Below is the code to execute the procedure "dbms_job.broken" from the shell script. on executing manually, it works properly without any error. but it is not working when scheduled using the cronjob. #!/usr/bin/bash user_name="oracdb" password="ora123" tns="localdb"... (2 Replies)
Discussion started by: milink
2 Replies

6. Shell Programming and Scripting

Need help with procedure and parameters in a script

Hi, I'm trying to create a procedure within my script and what I want is something like: myproc () { PARM1=value after -t PARM2=value after -y } myproc -t "PARM1 Value" -y "PARM2 Value" Of course that won't work since there's no such command as "value after -t". But this is... (4 Replies)
Discussion started by: adshocker
4 Replies

7. Shell Programming and Scripting

SP2-0642 error while executing procedure from shell script

hi all, i have a unix script where i am calling a database procedure from it. while executing the procedure i am getting an error: but when i tried to call the same procedure manually then it ran successfully, i goggled this issue and found timezone.dat file missing at... (0 Replies)
Discussion started by: lovelysethii
0 Replies

8. Shell Programming and Scripting

Stored Procedure not executing

below code is not executing the stored procedure,not sure what the issue.Even sqllog is blank.please help me its very urgent. sqlplus -s $connect_str@$DB_ORACLE_SID >> ${SQL_LOG_FILE} << EOF set serverout on set feed off set head off set pages 0 set colsep , set tab off set lin 150... (3 Replies)
Discussion started by: katakamvivek
3 Replies

9. Shell Programming and Scripting

Sub script calling procedure

Hi, I have a doubt regarding how sub scripts will be executed and interested to know internal workflow, For example - My main script is A,it calls a script B then B will call some C....and so on. Then B script run parallel to A or it will wait B to execute then A will continue. ... (1 Reply)
Discussion started by: nag_sathi
1 Replies

10. Shell Programming and Scripting

Why i can't execute the procedure in shell script?

i have the following code inside a shell script .prog in oracle server when i call the program DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id); will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i... (1 Reply)
Discussion started by: feilhk
1 Replies
st_proc_addr(3) 					     Library Functions Manual						   st_proc_addr(3)

NAME
st_is_alternate_entry, st_proc_addr, st_proc_sym, st_proc_name, st_proc_to_file, st_proc_start_line, st_proc_end_line, st_outer_proc_end_line, st_check_proc_line_range, st_proc_sort, st_proc_sorted_start, st_proc_sorted_next, st_foreach_proc, st_fore- ach_obj_proc, st_foreach_proc_sorted - Access information about the procedures in an object LIBRARY
Symbol Table and Object File Access Library (libst.a) SYNOPSIS
#include <st.h> st_status_t st_is_alternate_entry ( st_obj_t *obj, st_proc_t proc, st_bool_t *alt ); st_status_t st_proc_addr ( st_obj_t *obj, st_proc_t proc, st_addr_t *proc_addr ); st_status_t st_proc_sym ( st_obj_t *obj, st_proc_t proc, st_sym_t *sym ); st_status_t st_proc_name ( st_obj_t *obj, st_proc_t proc, char **name ); st_status_t st_proc_to_file ( st_obj_t *obj, st_proc_t proc, st_file_t *file ); st_status_t st_proc_start_line ( st_obj_t *obj, st_proc_t proc, st_line_t *sline ); st_status_t st_proc_end_line ( st_obj_t *obj, st_proc_t proc, st_line_t *eline ); st_status_t st_outer_proc_end_line ( st_obj_t *obj, st_proc_t proc, st_line_t *eline ); st_status_t st_check_proc_line_range ( st_obj_t *obj, st_proc_t proc, unsigned long line, st_proc_t *oproc ); st_status_t st_proc_sort ( st_obj_t *obj ); st_status_t st_proc_sorted_start ( st_obj_t *obj, st_proc_t *pstart ); st_status_t st_proc_sorted_next ( st_obj_t *obj, st_proc_t proc, st_proc_t *result ); st_status_t st_foreach_proc ( st_obj_t *obj, st_file_t file, st_status_t (*routine) __((st_obj_t *obj, st_proc_t proc, st_any_t data, st_any_t *result)), st_any_t data, st_any_t *result ); st_status_t st_foreach_obj_proc ( st_obj_t *obj, st_status_t (*routine) __((st_obj_t *obj, st_proc_t proc, st_any_t data, st_any_t *result)), st_any_t data, st_any_t *result ); st_status_t st_foreach_obj_proc_sorted ( st_obj_t *obj, st_status_t (*routine) __((st_obj_t *obj, st_proc_t proc, st_any_t data, st_any_t *result)), st_any_t data, st_any_t *result ); PARAMETERS
Specifies an object handle, as returned by the st_obj_open function. Specifies a procedure handle, as returned by a function such as st_obj_proc_start. Specifies an address to which st_is_alternate_entry returns a Boolean value of TRUE if the specified procedure is an alternate entry or a nested routine. Specifies an address to which st_proc_addr returns the starting address of the procedure as it would reside in memory when the object is executing. Specifies an address to which st_proc_sym returns the symbol handle for the specified pro- cedure. Specifies an address to which st_proc_name returns the null-terminated string of the procedure name. Specifies an address to which st_proc_to_file returns the handle of the file that contains the specified procedure, or a file handle that st_foreach_proc uses to locate procedures for which to run the specified routine. Specifies an address to which st_proc_start_line writes the starting line number for the specified procedure. This number identifies the line in the source file at which the procedure is defined. Specifies an address to which st_proc_end_line writes the ending line number for the specified procedure. This number identifies the line in the source file at which the procedure ends. If the specified procedure is an alternate entry or a nested procedure, st_outer_proc_end_line returns the end- ing line of the procedure containing the specified procedure to this location. Specifies a line number that st_check_proc_line_range is to determine is within the range of lines within the specified procedure. Specifies an address to which st_check_proc_line_range returns a procedure handle if the specified line number is within the range of lines in the specified procedure, or -1 if it is not. Specifies an address to which st_proc_sorted_start the handle of the first procedure in the sorted procedure table created by its implicit call of (or prior explicit call to) st_proc_sort. For the st_proc_sorted_next function, specifies an address to which the function returns the proce- dure that follows proc in the sorted procedure table. For the st_foreach_proc, st_foreach_obj_proc), and st_foreach_obj_proc_sorted func- tions, specifies an address to receive the return value from the specified routine. The called routine must be written to return 0 to ter- minate its execution within the procedure list, and ST_FOREACH_CONTINUE to continue to the next procedure. Specifies a routine to be called for each procedure in the specified file (st_foreach_proc), object (st_foreach_obj_proc), or sorted procedure table (st_fore- ach_obj_proc_sorted). Specifies data to be input to or output from the routine to be called for each object in the specified file (st_foreach_proc), object (st_foreach_obj_proc), or sorted procedure table (st_foreach_obj_proc_sorted). DESCRIPTION
These functions return information about a procedure or perform operations on the procedures in an object or file. The st_is_alternate_entry function determines whether the specified procedure is an alternate entry or nested routine. The st_proc_addr function returns the starting address of the procedure as it would reside in memory when the object is executing. The st_proc_sym function returns the symbol handle for the specified procedure. The st_proc_name function returns the null-terminated string of the procedure name. Because the returned procedure name is for read-access only, the calling program should make any modifications to a copy of this string. The st_proc_to_file function returns the handle of the file that contains the specified procedure. The st_proc_start_line and st_proc_end_line functions return the numbers of the line in the source file at which the procedure is defined and the line at which the procedure ends, respectively. The st_outer_proc_end_line function returns the ending line number of the proce- dure that contains the specified procedure, and is used for procedures that are alternate entries or nested procedures. If the specified procedure is neither an alternate entry or a nested procedure, st_outer_proc_end_line returns the line at which the specified procedure ends. The st_check_proc_line_range function determines whether the specified line number is within the range of lines in the specified procedure. If the line number is within range, the function returns the procedure handle. If not, it returns -1 to the location to which oproc points. Use st_check_proc_line_range when calling st_foreach_proc, st_foreach_obj_proc, st_foreach_obj_proc_sorted to operate on all the procedures in a given file, object, or sorted procedure list. The line parameter is defined as an unsigned long to allow it to be used for this purpose. The st_proc_sort function creates a table of procedures sorted by increasing address order. It includes all procedures in the specified object in the sorted procedure table. The sorted procedure table enables faster search operations on the procedure addresses by other rou- tines. Any libst routines that require a sorted procedure table implicitly call st_proc_sort if the table does not already exist. The st_proc_sorted_start function returns the handle of the first procedure in the sorted procedure table. The st_proc_sorted_next function returns the handle of the procedure that follows proc in the sorted procedure table. Note Use the st_obj_proc_start and st_obj_proc_next functions to access procedures in the order that they appear in the object, that is, in their link order. Use the st_proc_sorted_start and st_proc_sorted_next functions to access procedures in ascending address order. Note that some objects may have multiple procedures at the same address. Use st_obj_proc_count to determine the number of procedures in an object or sorted procedure table. The st_foreach_proc, st_foreach_obj_proc, and st_foreach_obj_proc_sorted functions provide a means of calling a routine once for each pro- cedure in the specified file, object, or sorted procedure table. You must write the called routine so that it returns 0 to terminate its execution within the procedure list, and ST_FOREACH_CONTINUE to continue to the next procedure. RETURN VALUES
All functions indicate success by returning a value of 0 (zero). A positive return value is an errno value from a system call. A negative return value is a library error or informational code. The library codes are documented in st.h. Return parameters are set to 0 or -1 when an error occurs. Address parameters are set to 0 while file and procedure handles are set to -1. An exception to this is if a NULL pointer for the object or other return parameter is input. In these cases, the return parameters will be unchanged. A non-zero return status is the recommended method for detecting an error return from a libst function. The st_foreach_proc, st_foreach_obj_proc, and st_foreach_obj_proc_sorted functions return ST_E_NULL_ARGUMENT if a null object handle or routine pointer are supplied. It returns a value of 0 (zero) when the called routine returns 0 to it. Otherwise, it returns ST_OBJ_END to indicate that it has reached the end of the procedures without a successful return from the called routine. FILES
header file that contains all definitions and function prototypes for libst.a functions header file that controls name-demangling opera- tions for C++ objects RELATED INFORMATION
Commands: atom(1) Functions: libst_intro(3), st_addr_to_file(3), st_file_lang(3), st_obj_open(3), st_obj_file_start(3), st_objlist_append(3), st_sym_value(3) delim off st_proc_addr(3)
All times are GMT -4. The time now is 11:21 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy