SQL & PL/SQL :: DBMS_SCHEDULER Calling Shell Script
Sep 13, 2011
I am facing issues with DBMS_SCHEDULER..I am receiving error while executing the "DBMS_SCHEDULER.run_job"..The OS folder has full permission.
OS : UNix
Database: 10g
====================================================================
Error Received:
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit 274664
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
THE CODE IS LISTED BELOW:
[code]....
View 24 Replies
ADVERTISEMENT
Jul 22, 2013
I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.
Below is my procedure:
create or replace
PROCEDURE RD_ODS_REFRESH_LOG
IS
BEGIN
FOR i IN
(
SELECT RESULT FROM RD_REFRESH_LOG
[code].....
And here is CALL_SHELL job that my procedure calls:
BEGIN
SYS.dbms_scheduler.create_job (
job_name => 'CALL_SHELL',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/load_semantic.sh',
[code].....
My ELSE statement never gets executed, why?
View 18 Replies
View Related
Nov 26, 2006
I am trying to call a shell script through external procedure. To summarize the steps that I followed:
Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.
Then I logged into the sqlplus. Created one library pointing this .so file.
create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';
After this created the external procedure. This got compiled without any errors.
SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2)
as external
library ext_proc
NAME "execute_cmd"
parameters (in_script string);
/
Procedure created.
I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.
However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows
SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>
View 5 Replies
View Related
Mar 25, 2013
I would like to use Dbms_Schedule to call SqlPlus script in Linux .
BEGIN
sys.Dbms_Scheduler.Create_Job (
job_name => 'TEST_SQL_JOB',
[Code]....
This is giving me the error: ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory
BUT when I issue the command: /usr/local/bin/sqlplus -s s/ibs @/usr2/dms/run/lkp_sql_test.sql
it works fine.
View 4 Replies
View Related
Jan 31, 2013
I am trying to schedule a job using DBMS_SCHEDULER.
The job has to start on SATURDAY 12:00 midnight and should run every on SATURDAY 12:00 midnight.
Even if I create the job on MONDAY first it should start on coming sunday and following sundays.
But I am getting the below error.
ORA-27452: BEGIN APPS_GLOBAL.POPULATE_TARGET144('APPS_XX','APPS_BE',5959); end; is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
The Job script is
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'TEST_RAM',
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959); END;',
[code]......
View 6 Replies
View Related
Aug 20, 2011
I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.
My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..
SQL> select job_name, next_run_date, last_start_date, last_run_duration from user_scheduler_jobs;
HISASAV_JOB
20-AUG-11 12.30.41.000000 PM ASIA/CALCUTTA
20-AUG-11 12.20.41.122959 PM ASIA/CALCUTTA
+000000000 00:00:01.307071
[code]....
View 4 Replies
View Related
Apr 23, 2012
During the daylight savings time our scheduler jobs are running either an hour before/after depending upon the time switch.
I went through the Oracle documentation and found below suggestions which I have already tried in vain.
Document says scheduler first picks the timezone from the start date of the job if provided so i tried setting the start date using the TO_TIMESTAMP_TZ('2012/01/22 18:50:00 US/Eastern','yyyy/mm/dd hh24:mi:ss tzr') which did not fix the problem. I have noticed that oracle automatically converted into tzh:tzm format.
second solution: setting default timezone of scheduler to the TZR i.e (US/Eastern) instead of the TZh:TZM value. I did that using below script
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute (
attribute => 'default_timezone',
value => 'US/Eastern');
END;
above 2 solutions did not work for me. I have read on internet from some article that below query should return something like
"4/23/2012 11:02:13 US/Eastern" after setting the default timezone of scheduler to TZR but I am still getting "4/23/2012 11:02:13.715816000 AM -04:00".
select dbms_scheduler.stime
from dual;
View 5 Replies
View Related
Feb 25, 2013
I have create the following procedure to create individual jobs dynamically on each schema.
CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN
[Code]....
While executing this procedure I am getting the below error.
PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:
View 5 Replies
View Related
Mar 13, 2011
I have create a schedule using dbms_scheduler and the problem is its not running on exact time that is start time , i even changed the attribute as following but its not.Its running using dbms_scheduler.run_job(veh_insp) when run directly.The schedule is as follows.
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'INTERVAL_DAILY_2000',
attribute => 'start_date',
value => TRUNC(SYSDATE)+9.50/24);
END;
View 6 Replies
View Related
Oct 13, 2011
I have created a job using DBMS_SCHEDULER and I want it to run every 30 seconds:
begin
dbms_scheduler.create_job(job_name => 'jobu',
job_type => 'PLSQL_BLOCK',
[Code]....
My question is how can I take the value 30 from a configuration table? Let's say I have a query like select value from config_table where property = 'job_interval' that returns the number 30. How can I set this value to be the repeat interval for my job?
View 9 Replies
View Related
Apr 23, 2013
We use AIX 6.1 and 10.2.0.4.0 - 64bit. When we create an Job it will be execute as OS User Oracle, but this is not wanted.The file permisions are set as described in Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files
rdbms/admin/externaljob.ora
-rw-r----- 1 root dba rdbms/admin/externaljob.ora
egrep -v '^#|^$' rdbms/admin/externaljob.ora
run_user = nobody
run_group = nobody
bin/extjob
[code].....
We tried different Users in the rdbms/admin/externaljob.ora file but every time the same behavior, the job runs as oracle.
View 0 Replies
View Related
Sep 6, 2013
I see example for a dbms_scheduler setting in parameter values but what about if it is an out parameter ?
create or replace procedure vd_tst (v_id out number) asbegin v_id := to_number(to_char(sysdate,'YYYYMMDDHH24MISS'));end vd_tst
how do I set a hook to do an alert let say for dbms_ schedulerlet sayif v_id mod(9)==0 then <alarm>
View 9 Replies
View Related
Jun 27, 2012
I connected to Scott user & I am trying to create a job using DBMS_SCHEDULER as mentioned below.
begin
dbms_scheduler.create_job(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'Insert into tab2 values(70,''TR'');',
start_date => sysdate,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
end;
When i execute this code i'm getting an Error Message as
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2
I have given grant manage scheduler to scott; Manage Scheduler privilege from SYS User.
View 5 Replies
View Related
Oct 21, 2011
I'm trying to call a simple batch file from dbms_scheduler, the job created successfully but I it doesn't invoke the batch file I've created.
This is the contents of my batch file :
EXEC hr.load_employees
And this is the job attempting to invoke it :
begin
dbms_scheduler.create_job
(job_name => 'EMP_HIST',
job_type => 'EXECUTABLE',
job_action => 'C:sql_scriptsemployees_load.sql > nul',
start_date => SYSDATE,
-- repeat_interval => 'FREQ=DAILY;BYHOUR=23', -- Every night at 23:00.
repeat_interval => 'FREQ=SECONDLY; INTERVAL=2', -- Every Minute
enabled => true,
comments => 'Copy the emp table to history');
end;
/
View 7 Replies
View Related
Oct 10, 2013
I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.
The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by (and as far as I understand, supposed to be used with) SYS user. what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users?
View 11 Replies
View Related
Mar 1, 2010
I have created a job using DBMS_SCHEDULER with named schedule.
After i have modified the schedule using the below queries,
exec dbms_scheduler.disable( 'JOB1' );
exec dbms_scheduler.set_attribute_null('JOB1','schedule_name');
exec DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'JOB1', attribute => 'repeat_interval', value => 'freq=WEEKLY;BYDAY=SUN,WED,FRI;BYHOUR=05');
exec dbms_scheduler.enable( 'JOB1' );
I am using the client system to change the setting and the time zone differs from that of production. Even though the job is scheduled to run at 5 AM it show the start date as 6:30 PM which is the client system time.
View 1 Replies
View Related
Aug 17, 2012
I have a job that i want to run on demand. I create the job with disabled status.
sys.dbms_scheduler.create_job(
job_name => 'myjob"',
job_type => 'PLSQL_BLOCK',
[Code]...
I call dbms_scheduler.run_job('myjob') when i have to run the job. It runs the job and does my action. But if i query select * from user_scheduler_jobs;
last_start_date and last_run_duration column values are null. I want to know how long my job runs to perform the action.
View 10 Replies
View Related
Feb 27, 2013
I have a procedure that has a number of "optional" parameters.
procedure get_files(
file_name_in in varchar2 default 'dummy_file',
layout_in in number default 1,
client_in in number default null,
data_supplier_in in number default 99999
);
This procedure can be called with any combination of the input parameters.I can set up program(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'PLSQL_BLOCK' like this:
begin
sys.dbms_scheduler.create_program(
program_name => 'GET_MY_FILES',
program_action => '
declare
begin
get_files( layout_in => 11111, client_in => 2222 );
end ;',
program_type => 'PLSQL_BLOCK',
number_of_arguments => 0);
end;
My question is: Can I set up programs(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'STORED_PROCEDURE' when I have "optional" parameters? It appears that ALL of the program input parameters must be defined and there is no way to indicate that a parameters is "optional".
View 8 Replies
View Related
Jul 17, 2012
I need to execute the DBMS_SCHEDULER.run_job on click of a button .I tried creating a process (On submit) with DBMS_SCHEDULER.run_job('JOBNAME')
when i tried executing directly in db there is no problem, but when i create the process in apex i am running into the following error
ORA-06550: line 2, column 19: PLS-00222: no function with name 'RUN_JOB' exists in this scope ORA-06550: line 2, column 1: PL/SQL: Statement ignored.
View 2 Replies
View Related
Sep 6, 2011
find the sample script and the error i face while running from shell. Actually i want to check the health of the database.
Sample script:
--------------------
export ORACLE_SID=GMX
export ORACLE_HOME=/opt/app/oracle/product/9.2.0
export PATH=/home/oracle/dbgen/PM/bin:/usr/bin:/opt/ansic/bin:/usr/ccs/bin:/usr/contrib/bin:/opt/nettladm/bin:/opt/pd/bin:/op
[code]....
error messege:
Oracle Server Manager Release 2.3.4.0.0 - Production
Message 4505 not found; No message file for product=SVRMGR, facility=MGR
Error while trying to retrieve text for error ORA-12545
Error while trying to retrieve text for error ORA-12545
Attached File(s)
Script.txt ( 997bytes )
Number of downloads: 3
View 1 Replies
View Related
Feb 10, 2012
I've a requirement of running shell script in pl/sql procedure. I thought of doing this by using dbms_scheduler package. I believe while executing the jobs using dbms_scheduler the owner has to be part of DBA group, but as per my company policy (security issue) only DBA users has to be part of this group and i'm not able to . So is there anyway i can execute shell script in pl/sql procedure other than using dbms_scheduler.
View 2 Replies
View Related
Nov 10, 2010
This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.
The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.
The procedure uses utl_file to create an .xls file
We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges drwxrwxrwx.
We have a job scheduler as the attachment which in turn triggers a procedure(check the code attachment for the scheduler and the procedure).
In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.
The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.
We capture the exceptions in a table. In the table the following exception is logged :ORA-29283: invalid file operation
The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..
Also the files are created when we manually run the same procedure in the path.
The Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
The OS flavour is SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V490 -------
View 1 Replies
View Related
May 22, 2013
Recently we come across DBMS_SCHEDULER issue while passing SYSDATE as default value. Job executed with corect date value on creation time, but further schedule its not incrementing date value.
Snap of the Program
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => '"OBIEE_ADM"."First_Job"',
job_type => 'STORED_PROCEDURE',
job_action => '"OBIEE_ADM"."SF_INTERFACE_PKG"."SF_EXTRACT"',
number_of_arguments => 1,
start_date => NULL,
[code]....
View 2 Replies
View Related
Jul 25, 2013
I created a procedure with cursor to inquiry a table. There is more than 1 million records on the table. For each record, I would like to transfer two columns on table as parameter to shell and feedback to procedure as varcahr2.
This is my sample(I only use 10 records for the testing).
create or replace procedure task_file(task_check in varchar2,file_name out varchar2) is
k number :=0;
v_task_number number :=0;
v_task_filename varchar2(30) := null;
cursor jobchk is select task_number,task_filename into v_task_number,v_task_filename from my.tasktable
where task_filename=task_check and rownum <=10;
[code]........
View 1 Replies
View Related
Apr 12, 2011
I was trying to take/schedule a RMAN backup of database using crontab in REDHAT Linux 5. I was able to execute the script manually and the backup completed successfully with out any issue. (execute ./script.sh from command prompt) But, this script is not getting executed on scheduled time from crontab..
daily_backup.sh
#!/bin/bash
export ORACLE_SID=traxmp
export ORACLE_HOME=$ORACLE_HOME
# Connect to the database. Change this to Sys logon if not using /
rman target sys/*******@traxmp @'/backup/rman_script/daily_backup.rman'
[code].....
View 4 Replies
View Related
Apr 17, 2013
I have a requirement to generate the AWR report daily between (10AM - 06PM , 10AM - 01PM , 01PM - 10PM) . I have the below script for this . But the issue is this script is working only when i run it two times and before running i have to delete snap_list.lst file.
Script :
-------------
dt=`date +%d%m%Y
cd /orabkp/awr_report
chmod 777 *
rm -rf snap_list.lst
touch snap_list.lst
[code]....
View 6 Replies
View Related
Sep 4, 2012
I have to write a script to do the following requirement. There is a file called BUSINESS_DATE.TXT. This file get updated once the oracle partition created. In Oracle, artition will be created every day. There is a seperate script scheduled to take care ORACLE partition creation. The above file will have only one row. i.e. 03092012
If Oracle partition creation job failed, the above file won't be updated. My requirement is, I have to check whether the BUSINESS_DATE file is updated today or not. If yes, I will have to move the files from common area to input file directory to process those files. All file name will amend with current date. i.e .
LIDDIFD03092012.TXT
The key part is to check the BUSINESS_DATE.TXT file is updated properly or not. We don't know what time the file will get updated. So we are planning to schedule the new script to run for every 15 mins to check whether the file is updated or not
But...I just thought instead of writing a shell script to do the above one, Why shouldn't I capture the date in a table (New table needs to be created) and use ORACLE TRIGGER to run the shell script to move the files from common area to input file directory to process those files?
My Proposal in ORACLE :
Create table business_date
(
rep_date varchar(15),
curr_timestamp timestamp
)
Once the oracle partition created , one row will be inserted into the above table. This adjustment needs to be implemented partition script. Once this table get record, TRIGGER should call SHELL SCRIPT to move the files from common area to input area.
If I implement ORACLE TRIGGER, The script which will check whether the file got updated or not for 15 mins is not required.
View 3 Replies
View Related
Dec 24, 2009
I have written a shell script for block corruption checks. I want to run the script from OEM grid control, so that I can get an email alert if there is anything wrong. how to add the shell script at OEM Grid?
View 1 Replies
View Related
Apr 27, 2012
I have a requirement where i need to execute a unix shell script "a.sh" having permissions user1,group1 and no access to public. This should be executed from Oracle function.
So i wrote a routine in Java and it works fine when i execute a command like Date etc.
But when i execute "a.sh" from oracle client, its returning error that "could not be executed-permission denied". This is because once login happened through the "sqlplus" the shell takes user as "oracle" and group as "dba" but the script "a.sh" having only permissions user1,group1. Unfortunately we are not allowed to change the permission for "a.sh" to give execute permission to all(public).
After searching in internet, i understand that one way is to make the password (/etc/passwd) for the user "user1" as no password and can use "su" command so that it wont ask password while invoking it.
Is there any other way apart from this doing changes in password file at UNIX level when executing a script from oracle client?
if further information is required.
Oracle: version: 11.2.0.1
UNIX : AIX -5.9
View 3 Replies
View Related
Sep 12, 2012
I have a shell script which triggers a batch in our application (an application which is not integrated with any other scheduler and is trying to integrate with OEM for the first time).
Is it possible to schedule a job to run this shell script in OEM ?
View 1 Replies
View Related
