9 Replies Latest reply: Jan 31, 2013 4:51 AM by Niket Kumar RSS

    Cron Job

    973374
      Hi,
      I am writing a cron job in Oracle 10g(Linux).
      So, i have created a script of inseration a data in a table using a cron tab.
      sql script which i created is:
      vi insert.sql
      conn / as sysdba
      insert into t1(id) values(01);
      commit;
      exit

      cron Job script :

      10 5 * * * /u01/home/insert.sql

      but, when i execute this particular file then data is not getting inserted in that particular table.

      Please help

      Regards,
      Rajeev
        • 1. Re: Cron Job
          spajdy
          SQL script is not shell script. To run any SQL script agains ORACLE database you have to use SQL*Plus. It is cmdline utitlity.
          You can run script in this way sqlplus /nolog @/u01/home/insert.sql. But you have to set your PATH environment variable to contraint folder with ORACLE binaries.
          On *nix it is $ORACLE_HOME/bin.
          For details see documentation.

          Edited by: spajdy on Jan 30, 2013 2:07 PM
          • 2. Re: Cron Job
            973374
            Hi,
            In Which file i have to set environment variable(give me the example).
            and also pls tell me after doing this i will be able to insert the data with the help of cron job.


            Regards,
            Rajeev.
            • 3. Re: Cron Job
              Niket Kumar
              you cannot insert / as sysdba in your sql script...make a shell script and schedule it......
              check belo link and schedule shell script....
              http://www.oracle-base.com/articles/misc/oracle-shell-scripting.php
              • 4. Re: Cron Job
                973374
                Hi,
                That means i have to write a insert shell script like this:

                #!/bin/bash
                sqlplus /nolog << EOF
                connect scott/tiger
                insert into t1(id) values(01);
                commit;

                Now,pls tell me it wil work or not.
                waiting for your prompt reply..

                Regards,
                Rajeev
                • 5. Re: Cron Job
                  973374
                  Hi,
                  Pls, reply.


                  Regards,
                  Rajeev
                  • 6. Re: Cron Job
                    Niket Kumar
                    below will work check article properly...
                    #!/bin/bash
                    sqlplus /nolog << EOF
                    connect scott/tiger
                    insert into t1(id) values(01);
                    commit;
                    exit;
                    EOF
                    test it now...
                    • 7. Re: Cron Job
                      973374
                      Hi,
                      i have tried but the error which i am getting
                      /var/spool/mail/oracle location
                      i.e
                      /bin/sh: /u01/home/insert.sh : bin/bash: bad interpreter : no such file or directory

                      Pls help.

                      Regards,
                      Rajeev
                      • 8. Re: Cron Job
                        973374
                        Hi,
                        Pls reply....


                        Regards,
                        Rajeev
                        • 9. Re: Cron Job
                          Niket Kumar
                          first try to execute script manually

                          sh /u01/home/insert.sh

                          if it give error try

                          ksh instead of bash
                          #!bin/ksh



                          always try manually running the task before scheduling cron tab..

                          Edited by: Niket Kumar on Jan 31, 2013 4:21 PM