5 Replies Latest reply: Sep 6, 2013 12:53 PM by Angelina84 RSS

    problems with executing shell script within oracle procedure

    Angelina84

      I have procedure that's owned by Semantic user. This procedure calls job scheduler (owned by USER1) which in turn executes a shell script load_semantic.sh (owned by USER1).


      Now, there is a shell script(checkCount.sh)inside of this one (owned by this USER1) that has following contents:


      checkCount.sh

      #!/bin/bash
      #
      MODEL=$1
      sqlplus -S user/pass << EOF
      
      whenever sqlerror exit 1;
      set echo on
      set verify off
      MERGE INTO SEMANTIC.COUNT_STATISTICS s
      USING (SELECT '$MODEL' AS MODEL, 0 AS NEW_COUNT, SYSDATE AS NEW_DATE, 0 AS OLD_COUNT, SYSDATE AS OLD_DATE FROM dual) t
      on (s.MODEL = t.MODEL)
      when not matched then
      INSERT (s.MODEL, s.NEW_COUNT, s.NEW_DATE, s.OLD_COUNT, s.OLD_DATE)
      VALUES (t.MODEL, t.NEW_COUNT, t.NEW_DATE, t.OLD_COUNT, t.OLD_DATE);
      COMMIT;
      
      
      UPDATE SEMANTIC.COUNT_STATISTICS SET  MODEL = '$MODEL', NEW_COUNT = (SELECT COUNT(*) FROM TABLE(SEM_MATCH('{?s ?p ?o}',SEM_Models('$MODEL'),NULL,SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC#')),NULL)) ),NEW_DATE  = SYSDATE,
      OLD_COUNT = NEW_COUNT, OLD_DATE  = NEW_DATE WHERE MODEL = '$MODEL' AND NEW_DATE = (select max(NEW_DATE) from SEMANTIC.COUNT_STATISTICS where MODEL = '$MODEL');
      COMMIT;
      exit;
      EOF
      ...
      

      So when I execute SEMANTIC.PROCEDURE:


      SEMANTIC.PROCEDURE => USER1.JOBSCHEDULER =>USER1.load_semantic.sh =>USER1.checkCount.sh

      It never populates the table SEMANTIC.COUNT_STATISTICS.

      I tried giving all of the permissions but I am probably missing something...


      Maybe fresh eyes can see something that I can't.

        • 1. Re: problems with executing shell script within oracle procedure
          DK2010

          Hi,

           

          What abot the USER1.JOBSCHEDULER =>USER1.load_semantic.sh =>USER1.checkCount.sh is it working

           

          Did you try the Query is it working alone.. i think you should try comment the line

          --whenever sqlerror exit 1; 

          and Try may you will get any error related to sql Query.


          HTH

          • 2. Re: problems with executing shell script within oracle procedure
            Catch_22

            A child process, such as sqlplus, does not know about shell environment variables unless you export them. Consider the following example:

             

            set -x

            export p1="status"

            sqlplus -s /nolog <<-EOF

            connect / as sysdba

            select $p1 from v\$instance;

            EOF

             

            It looks like you should export your MODEL variable.

             

            And btw, it is not a good idea to provide username/password at the sqlplus command line, since anyone can see that using the ps command. It's better to use a connect statement.

             

            Please note that the scope of shell variables between parent and child processes is correct, but it does apparently not apply here due to the "start here" document marker. To export the variable in this case is therefore not necessary. I have corrected this in my follow-up response.

            • 3. Re: problems with executing shell script within oracle procedure
              Angelina84

              When I call checkCount.sh within linux, it works just fine.

              But if I call it from within the job scheduler that gets called within the procedure ,

              echoing works but the actual merge and insert that happens within sqlplus doesn't work...

               

               

              I opened job scheduler log and I found this:

              STANDARD_ERROR="./checkCount.sh: line 6: sqlplus: command not found

              ./checkCount.sh: line 22: sqlplus: command not found

              ./checkCount.sh: line 39: [: : integer expression expected

              ./checkCount.sh: line 58: sqlplus: c"

              • 4. Re: problems with executing shell script within oracle procedure
                Catch_22

                When I call checkCount.sh within linux, it works just fine.

                Interesting. I guess this must be due to the "start and end of the here document (EOF)" marker, which is resolved in the current shell, rather inside sqlplus, and therefore shell variables do not need to be exported.


                Regarding your error, at line 22 (the rest are follow-up), it seems to be an issue with the Oracle shell environment. Check the following:

                 

                $ ./sqlvar

                ./sqlvar: line 5: sqlplus: command not found

                 

                $ ORACLE_SID=test

                $ ORAENV_ASK=NO

                $ . oraenv -s

                $ ./sqlvar

                STATUS

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

                OPEN

                 

                So perhaps adding the lines in bold to your script will fix this problem. Not the space between the . (dot) and oraenv, which is required to source execute the script.

                • 5. Re: problems with executing shell script within oracle procedure
                  Angelina84

                  I can't believe I am going to say this but the reason it was not working was because I removed oracle environment path from my script by mistake.

                  . /etc/profile.d/oracle.sh

                  Ugh...

                  Now it is working perfectly.

                  Btw, sqlplus does know about shell environment variables.


                  THANK YOU GUYS!!! YOU ARE THE BEST!!!!!!!!!!!!!!!!!!

                  I ONLY HAVE YOU TO TALK TO WHEN I AM STUCK ON A PROBLEM.