Oracle Database Discussions

Oracle 10gR2

After reading this guide, Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files

my understanding is that non-SYS jobs run as the "nobody" user/group.

So I create a procedure in the SYS schema that creates/runs the job at runtime. And granted execute to that procedure to the user I want to run the procedure.

But I'm getting an error ORA-27369: job of type EXECUTABLE failed with exit code: Not owner

Here is the code:

-- SYS procedure to create/run job
CREATE OR REPLACE PROCEDURE sys.p_create_job_oe (   i_nonactive_user  IN         VARCHAR2
                                                  , o_msg             OUT NOCOPY VARCHAR2
                                                )
IS
   v_nonactive_user VARCHAR2(25) := i_nonactive_user ;
   v_job_exists     NUMBER(1) ;
BEGIN
  -- create job
   BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
        job_name              =>  'OE_TO_DEMO_JOB',
        job_action            =>  '/home/oracle/bin/oe_to_prod.sh',
        job_type              =>  'EXECUTABLE',
        number_of_arguments   =>  1,
        auto_drop             =>  TRUE,
        comments              =>  'Transfer data to prod server');
   END;
  
   -- set argument
   dbms_scheduler.set_job_argument_value(   job_name            => 'OE_TO_DEMO_JOB'
                                          , argument_position   => 1
                                          , argument_value      => v_nonactive_user ) ;
                                   
   DBMS_SCHEDULER.enable('OE_TO_DEMO_JOB') ;
  
   -- run the job
   DBMS_SCHEDULER.run_job( job_name => 'OE_TO_DEMO_JOB') ;
  
   o_msg := 'Job OE_TO_PROD completed' ;

   EXCEPTION
      WHEN   others   THEN
      o_msg := sqlerrm ;
         RAISE ;

END p_create_job_oe ;
/

grant execute on p_create_job_oe to OE ;
grant create any job to OE ;
grant create external job to OE ;

-- as OE user
create or replace synonym p_create_job_oe FOR sys.p_create_job_oe ;

CREATE OR RPLACE PROCEDURE p_exp_imp (   i_nonactive_user  IN  VARCHAR2
                                       , o_msg             OUT  NOCOPY VARCHAR2 )
AS
   msg varchar2(4000) ;
BEGIN  
    -- do the import/export
    p_create_job_oe ( i_nonactive_user, msg ) ;

  o_msg := msg;

  EXCEPTION
  WHEN  OTHERS  THEN
     o_msg := msg ;
     RAISE ;
END ;
/

SQL> sho user
USER is "OE"

SQL> var o_msg varchar2(512)
SQL> exec p_exp_imp(:o_msg)
BEGIN p_exp_imp(:o_msg); END;

*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Not owner

Is there a work around to this, or am I missing something?

Message was edited by: orclrunner corrected typo