Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Executing procedure using script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Executing procedure using script
# 1  
Old 10-01-2012
Executing procedure using script
Hi,

I want to have a automted script to exceute 20 procedures one by one.
Suppose below is one procedure once it get executed script will write
"PL/SQL procedure successfully completed." to a log

for ex-
Code:
exec dbms_stats.gather_table_stats();

Now later procedure starts executing only when previous one gets completed.
Can it be possible to have such script

Your prompt help would be much appreciated.
Moderator's Comments:
Mod Comment Please do not use odd fonts, just code tags
# 2  
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:
sv0081493
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
Login or Register to Ask a Question