Hi Tom,

I followed your guidelines above but I am still encountering a somewhat strange problem. Just to set the scene, this is what is required:

A Solaris script named inc0_backup.sh is to be run as an external program by DBMS_SCHEDULER. This was accomplished using the following methodology:

1. This server is a test server and normally DBMS_SCHEDULER is not enabled. To re-enable it I did the following:

exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','false');

2. Create a program for the external script:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'ODB_INC0_PROG',
program_type => 'EXECUTABLE',
program_action => '/u02/oracle/fast_recovery_area/ORCL/scripts/inc0_backup.sh',
enabled => TRUE,
comments => 'Full database backup'
);
END;
/

3. Configure a scheduler job:

BEGIN
dbms_scheduler.create_job(
job_name => 'ODB_INC0_JOB',
job_type => 'executable',
job_action => '/u02/oracle/fast_recovery_area/ORCL/scripts/inc0_backup.sh',
start_date => to_date('2012-12-21 11:23:00','yyyy-mm-dd hh24:mi:ss'),
repeat_interval => 'freq=daily;interval=1',
enabled => TRUE
);
END;
/


4. Check the job after the run:

select job_name, error#, log_date, status from all_scheduler_job_run_details where job_name like 'ODB_%' order by log_date desc;

which returns:

JOB_NAME ERROR# LOG_DATE STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------- ------------------------------------------------------------------------------------------
ODB_INC0_JOB 255 21-DEC-12 11.23.02.118403 AM +01:00 FAILED


The error number 255 seems to point towards a log problem but I am completely at a loose end to understand what that problem might be.

The script in its native form works perfectly when executed from Solaris with ./inc0_backup.sh.

Do you have any ideas what the problem might actually be?

Your help as always is gratefully appreciated.

Regards,

Kev

Cheers Tom, you've done it again!

Happy New Year and until my next little dilemma!

Regards,
Kev

Tom -

I would like to execute this job ( job_test) from another user ( let us say usr2) with minimal privilege .
Usr2 is only responsible for running the job ; he cannot / should alter job contents.

What is the appropriate privilges to achieve this ?

Per the documentation ,
To give it a try , Initially , I tried giving privilegs as alter job ( though I dont want usr2 have alter privilege )

GRANT ALTER job_test TO usr2
Error report:
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 - "missing or invalid privilege"


Test Case:

drop table t_test purge ;

create table t_test ( c char(2000) ) ;


create or replace procedure p_test as
begin
insert into t_test values ( to_char(sysdate , 'CCYY-MM-DD HH:MI:SS'));
end ;



JOB_DEFINITION
===============

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => '"USR1"."job_test"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
p_test();
end ;',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
job_class => '"SYS"."DEFAULT_JOB_CLASS"',
enabled => FALSE,
auto_drop => FALSE,
comments => '',
credential_name => NULL,
destination_name => NULL);




SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"USR1"."job_test"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);


SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"USR1"."job_test"',
attribute => 'max_run_duration', value => INTERVAL '1' HOUR);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"USR1"."job_test"',
attribute => 'schedule_limit', value => INTERVAL '1' HOUR);
SYS.DBMS_SCHEDULER.enable(
name => '"USR1"."job_test"');
END;
/



thanks .

This job will be executed from SQL Server jobs ( lot of dependency between SQL Server jobs and Oracle jobs) .

Linked Server ( in Oracle terms , database link ) is configured only for usr2. I don't want to give this user any more privilege ( like creating dbms scheduler job , creating procedure , .... ) than "running the job" privilege .

I wasn't clear.

There are few steps involved.

These steps needs to happen only on a specific days ( as defined in the SQL Server table )

a) Data processing happens in Oracle ( based on the oracle data elements - that does not exist in the SQL Server)

b) Data is transferred to SQL Server for further processing.

c) so on and so forth .

For
a) - I have oracle job

b) - I have a linked server setup to pull in the processed data in step a)

c) I have bunch of stored procedures that runs on the data set retrieved in step 2.

Are you suggesting to invoke Oracle process ( aka package ) from SQL Server Job? . Please let me know.

In addition to the above , if I run the stored procedure , I am getting the following error in SQL Server .


Execute ( 'call t_pkg.p_t') AT ORADB_LINK

OLE DB provider "OraOLEDB.Oracle" for linked server ORADB_LINK returned message "ORA-06576: not a valid function or procedure name".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'ORADB_LINK'.


But , I can however the job ( if I wrap the procedure in the job )

Execute ( 'call dbms_scheduler.run_job(''JOB_TEST'')') AT ORADB_LINK

Hi Tom!

Well, after following your advice my job works perfectly now. On that point, many thanks.

The question I have as a follow-up is: Why do I need to create a program to run an external file? Why can't I simply reference the file directly from my job without the program?

Many thanks in advance!

Regards,
Kev

Hi Tom,
You always rock :-)

I am running some processes parallely using DBMS_SCHEDULER package. For Example:

begin
//Lets assume, we are Starting Jobs immediatly by setting start_date=>SYSDATE;enabled=>TRUE

for i in 1..4
loop
dbms_scheduler.create_job(...);
end loop;

//Implement the logic for waiting for jobs to continue

//More processing
....
end;

Could you please how can i implement a logic (without looping through the all_scheduler_jobs) to wait for all DBMS_SCHEDULER to complete before i proceed to "More Processing" section similar to JOIN() to Java or Perl multi-threading.

Is that DBMS_ALERT or DBMS_LOCK packages can be utilized?
Or Is that looping is most efficient option ; Since code becomes very complex, Hence looking for an Oracle's Built IN funtionality(if available).

Thanks in advance.

Regards,
Dillip

    loop
        select count(*) into l_cnt from all_scheduler_jobs where rownum = 1 and...;
        exit when l_cnt = 0;
        dbms_alert.waitone( 'some name', l_msg, l_status, 600 );
    end loop;