This discussion is archived
0 Replies Latest reply: Oct 18, 2012 6:19 AM by 741425 RSS

DBMS_SCHEDULER.run_chain() only works when connected as SYS

741425 Newbie
Currently Being Moderated
Database Version: 10.2.0.4.0
Operating System: Windows 2003

Hello,

I have a problem with DBMS_SCHEDULER.run_chain where the Chain to be run references an external Program.

The Chain runs correctly when run via SQLPLUS when you are connected to the database as SYS but fails to run when you are connected to the Database as the User who owns the Chain.

The complete steps to recreate the problem are below. Any thoughts or suggestions gratefully received.


---------------------------
STEP 1: Login with sys as sysdba and create a new user
---------------------------
CREATE USER chaintest
IDENTIFIED BY password
PROFILE DEFAULT
ACCOUNT UNLOCK;

GRANT MGMT_USER TO chaintest;
GRANT CONNECT TO chaintest;
GRANT IMP_FULL_DATABASE TO chaintest;
GRANT SCHEDULER_ADMIN TO chaintest;
GRANT MANAGE SCHEDULER TO chaintest;
GRANT EXECUTE ANY CLASS TO chaintest;
GRANT CREATE ANY DIRECTORY TO chaintest;
GRANT CREATE ANY JOB TO chaintest;
GRANT CREATE TABLE TO chaintest;
GRANT EXECUTE ANY PROGRAM TO chaintest;
GRANT CREATE JOB TO chaintest;
GRANT CREATE EXTERNAL JOB TO chaintest;

---------------------------
STEP 2: Create a very simple batch file
---------------------------
Create a folder called c:\chaintest and create a text file in there whose entire contents
is "notepad.exe" followed by a carriage return. The idea is that if this batch file is
called a notepad Window will be opened so we can clearly see if the batch file has run.

---------------------------
STEP 3: Create a DBMS_SCHEDULER Program
---------------------------
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'CHAINTEST.CHAINTEST_PROGRAM',
program_type => 'EXECUTABLE',
program_action => 'C:\WINDOWS\SYSTEM32\CMD.EXE /q /c c:\chaintest\chaintest.bat',
number_of_arguments => 0,
enabled => true);
END;

---------------------------
STEP 4: Create a chain
---------------------------
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (chain_name => 'CHAINTEST.CHAINTEST_CHAIN');
END;

---------------------------
STEP 5: Create the only Chain Step
---------------------------
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP(chain_name=>'CHAINTEST.CHAINTEST_CHAIN'
, step_name=>'STEPA'
, program_name=>'CHAINTEST.CHAINTEST_PROGRAM');
END;

---------------------------
STEP 6: Create Chain Rules
---------------------------
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name=>'CHAINTEST.CHAINTEST_CHAIN'
, condition=>'TRUE'
, action=>'START STEPA');

DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name=>'CHAINTEST.CHAINTEST_CHAIN'
, condition=>'STEPA COMPLETED'
, action=>'END');
END;

---------------------------
STEP 7: Enable the Chain
---------------------------
BEGIN
DBMS_SCHEDULER.enable('CHAINTEST.CHAINTEST_CHAIN');
END;

---------------------------
STEP 8: Run the Chain whilst still connected as sys and observe that a Notepad window opens
confirming that the Batch file ran successfully.
---------------------------
BEGIN
DBMS_SCHEDULER.run_chain(
chain_name=>'CHAINTEST.CHAINTEST_CHAIN'
, start_steps=>NULL);
END;

---------------------------
STEP 9: Connect to the database as the CHAINTEST user and run the same command as in STEP 8 above.
Observe that even though you receive the message "PL/SQL procedure successfully completed." and do
not see any error messages that no Notepad Window is opened indicating that the Batch file was NOT run.
---------------------------
BEGIN
DBMS_SCHEDULER.run_chain(
chain_name=>'CHAINTEST.CHAINTEST_CHAIN'
, start_steps=>NULL);
END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points