11 Replies Latest reply: Apr 4, 2014 4:02 AM by GregV RSS

    DBMS_SCHEDULER and Shell Script on 10.2.0.1

    taohiko

      Hi All

       

      My env, Oracle 10.2.0.1 and OS is Solaris.

       

      My script can run on OS that is work without error that contain create file, delete file, call sqlplus, function to get value from database and return to OS variables.

       

      In shell script some code as below

      -------------------------------------------------------------------------------------------------------------------------------------

      get_val () {

      sqlplus -s /nolog <<EOF

      conn test/test

      set heading off

      set feedback off

      set pages 0

      SELECT SUBSTR (SYS_CONNECT_BY_PATH (Z, ' '), 2) z

            FROM (SELECT NAMEDQUERY , ROW_NUMBER () OVER (ORDER BY Z) rn,

                         COUNT (*) OVER () cnt

                    FROM X

                    WHERE Z LIKE '%FULL')

           WHERE rn = cnt

      START WITH rn = 1

      CONNECT BY rn = PRIOR rn + 1;

      EOF

      }

       

      vx=$(get_val)

       

      if [ ${#vx} -gt 0 ]; then

       

      for xfile in $vx

      do

       

      rm -f $CURRDIR/$xfile.txt 2> /dev/null

       

      done

      fi

      -------------------------------------------------------------------------------------------------------------------------------------

       

      When run by dbms_scheduler that show below error

      ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

      STANDARD_ERROR="/home/oracle/script/test.sh: syntax error at line 26: `vx=$' unexpected

       

      How to resolve above error?  (owner of $CURRDIR/$xfile.txt is oracle user)

       

      One more question, How to force dbms_scheduler that using user oracle (OS) to run shell script?, as I know, default user is nobody and Oracle version 10.2.0.2 later that have external.job.ora to set it but 10.2.0.1 doesn't have it.

       

      Thank you in advance,

      Hiko