0 Replies Latest reply: Oct 18, 2012 8:19 AM by Andrew Tulley RSS

    DBMS_SCHEDULER.run_chain() only works when connected as SYS

    Andrew Tulley
      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;