Sponsored Content
Top Forums Shell Programming and Scripting Executing oracle procedure using cronjob Post 302515151 by milink on Tuesday 19th of April 2011 08:39:12 AM
Old 04-19-2011
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.

Code:
#!/usr/bin/bash

user_name="oracdb"
password="ora123"
tns="localdb"
ORACLE_BASE="/oracle"
ORACLE_HOME="/oracle/v9.0.1"
ORACLE_SID="mydb"
PATH="/oracle/v9.0.1/bin:/usr/bin:"

set `sqlplus -s $user_name/$password@$tns<<EOS
exec dbms_job.broken(118,TRUE);
exit;
EOS`

Why the above script is not working when executed using the cronjob ?

Regards
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Oracle stored procedure.

I am using sqlplus. I have the stored procedure name. How can i print the stored procedure content? (2 Replies)
Discussion started by: kamil
2 Replies

2. Shell Programming and Scripting

How to execute an Oracle procedure using shell

Hi , i have created an .sh file that has the following code: #!/bin/ksh sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql & sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF exit EOF The mentioned Procedure_Execute.sql file inside has the following code: exec TEST; ... (5 Replies)
Discussion started by: vins_san
5 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. UNIX for Dummies Questions & Answers

cronjob not executing...

Hi All. I created a cronjob entry and for some reason it is not executing. The script that it is suppose to call sends me an email when it is done. When I run the job manually it executes fine. This how my cron entry looks: 00 14 * * * /home/oracle/scripts/bin/free_space/free_space.sh 2>$1... (5 Replies)
Discussion started by: shaseeb
5 Replies

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

6. Shell Programming and Scripting

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- exec dbms_stats.gather_table_stats(); Now later procedure starts executing... (1 Reply)
Discussion started by: sv0081493
1 Replies

7. Programming

Oracle Procedure approach

HI All , I am new to oracle procedures. Please help me for the approach to satify the requirement. I need to create procedures. with parameters passed ( say report,type,identities,country ) It should also call sql query within the procedures and passed parameters should be used in where clause... (2 Replies)
Discussion started by: Perlbaby
2 Replies

8. Shell Programming and Scripting

Processing values from Oracle procedure

Hi all, My oracle procedure returns more than one value. i need to get one value at a time upto ending value ina shell script. Please help me..... (9 Replies)
Discussion started by: pmreddy
9 Replies

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

10. 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
ns_sched(3aolserver)					    AOLserver Built-In Commands 				      ns_sched(3aolserver)

__________________________________________________________________________________________________________________________________________________

NAME
ns_after, ns_cancel, ns_pause, ns_resume, ns_schedule_daily, ns_schedule_proc, ns_schedule_weekly, ns_unschedule_proc - commands SYNOPSIS
ns_after seconds {script | procname ?args?} ns_cancel id ns_pause id ns_resume id ns_schedule_daily ?-thread? ?-once? hour minute {script | procname ?args?} ns_schedule_proc ?-thread? ?-once? interval {script | procname ?args?} ns_schedule_weekly ?-thread? ?-once? day hour minute {script | procname ?args?} ns_unschedule_proc id _________________________________________________________________ DESCRIPTION
ns_after run the specified script or procedure after the specified number of seconds ns_after returns an id which can be used with the ns_pause, ns_cancel and ns_resume apis. ns_cancel stops the scheduled running of the id returned by an ns_after returns 1 if unscheduled 0 if the script of procedure couldn't be unscheduled ns_pause pauses the scheduled running of the id returned by an ns_after returns 1 if paused, 0 if the script of procedure couldn't be paused ns_resume resumes the scheduled running of the id returned by an ns_after returns 1 if resumed, 0 if the script of procedure couldn't be resumed ns_schedule_daily ns_schedule_daily runs the specified Tcl script or procedure (procname) once a day at the time specified by hour and minute. The hour can be from 0 to 23, and the minute can be from 0 to 59. Specify -thread if you want a thread created to run the procedure. This will allow the scheduler to continue with other scheduled procedures. Specifying -thread is appropriate in situations where the script will not return immediately, such as when the script performs network activity. Specify -once if you want the script to run only one time. The default is that the script will be re-scheduled after each time it is run. ns_schedule_daily returns an id number for the scheduled procedure that is needed to stop the scheduled procedure with ns_unsched- ule_proc. ns_schedule_proc ns_schedule_proc runs the specified Tcl script or procedure (procname) at an interval specified by interval. The interval is the number of seconds between runs of the script. Specify -thread if you want a thread created to run the procedure. This will allow the scheduler to continue with other scheduled procedures. Specifying -thread is appropriate in situations where the script will not return immediately, such as when the script performs network activity. Specify -once if you want the script to run only one time. The default is that the script will be re-scheduled after each time it is run. ns_schedule_proc returns an id number for the scheduled procedure that is needed to stop the scheduled procedure with ns_unsched- ule_proc. ns_schedule_weekly ns_schedule_weekly runs the specified Tcl script or procedure (procname) once a week on the day specified by day and the time speci- fied by hour and minute. The day can be from 0 to 6, where 0 represents Sunday. The hour can be from 0 to 23, and the minute can be from 0 to 59. Specify -thread if you want a thread created to run the procedure. This will allow the scheduler to continue with other scheduled procedures. Specifying -thread is appropriate in situations where the script will not return immediately, such as when the script performs network activity. Specify -once if you want the script to run only one time. The default is that the script will be re-scheduled after each time it is run. ns_schedule_weekly returns an id number for the scheduled procedure that is needed to stop the scheduled procedure with ns_unsched- ule_proc. ns_unschedule_proc id ns_unschedule_proc stops a scheduled procedure from executing anymore. The scheduled procedure to be stopped is identified by its id, which was returned by the ns_schedule* function that was used to schedule the procedure. EXAMPLES
ns_after ns_cancel ns_pause ns_resume This example illustrates a web interface used to manage jobs. Depending on the action provided a job can be created, cancelled, paused or resumed. set action [ns_queryget action] set job [ns_queryget job] switch $action { create { set job [ns_after 10 [ns_queryget script]] ns_puts "Job created with id: $job" } cancel { if {[ns_cancel $job]} { ns_puts "Job $job cancelled" } else { ns_puts "Job $job not cancelled" } } pause { if {[ns_pause $job]} { ns_puts "Job $job paused" } else { ns_puts "Job $job not paused } } resume { if {[ns_resume $job]} { ns_puts "Job $job resumed" } else { ns_puts "Job $job couldn't be resumed" } } default { ns_puts "Invalid action $action" } } ns_schedule_daily This example defines a script called rolllog that uses ns_accesslog to roll the access log to a file with an extension containing the current date. The ns_schedule_daily function is used to execute the rolllog script on a daily basis. # Script to roll and rcp log file to host "grinder" proc rolllog {} { set suffix [ns_strftime "%y-%m-%d"] set new [ns_accesslog file].$suffix ns_accesslog roll $new exec rcp $new grinder:/logs/[file tail $new] } # Schedule "rolllog" to run at 3:30 am each morning ns_schedule_daily -thread 3 30 rolllog ns_schedule_proc proc dosomething blah { ns_log Notice "proc with arg '$blah'" } ns_schedule_proc 10 dosomething $arg1 SEE ALSO
KEYWORDS
schedule pause resume unschedule cancel after AOLserver 4.0 ns_sched(3aolserver)
All times are GMT -4. The time now is 09:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy