This discussion is archived
9 Replies Latest reply: Jan 31, 2013 2:51 AM by Niket Kumar RSS

Cron Job

973374 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,
    Pls, reply.


    Regards,
    Rajeev
  • 6. Re: Cron Job
    Niket Kumar Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,
    Pls reply....


    Regards,
    Rajeev
  • 9. Re: Cron Job
    Niket Kumar Pro
    Currently Being Moderated
    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

Legend

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