Shell + PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Shell + PL/SQL

  1. #1
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    Unhappy Shell + PL/SQL

    Hi,

    I have a problem with unix and procedure in oracle.

    in package i have a procedure, which just create a file a write in it some information.

    i call this procedure from unix shell script.

    and the problem is...

    when i run the script from shell command line everything is ok, the file is created.
    BUT
    when i put script into crontab... the file is NOT created...

    in shell script and in oracle procedure every path is absolut.
    the script is running (exactly by crontab specification) all echos are written into crontaboutput.file, sql log is ok -> procedure succesfully...
    Everything works, only the file which created procedure doesn't exist....

    CRONTAB ------------------------------------------------------------
    00 10 * * * /u03/app/DEV2/janjaci/monitoring/jmon > cronoutput.file

    SHELL SCRIPT -------------------------------------------------------
    #!/bin/sh
    #
    #
    ARPATH="/u03/app/DEV2"
    JMONPATH="$ARPATH/janjaci/monitoring"
    USER="ar"
    PASS=`cat $ARPATH/.arpw`
    DB="BP_DEV2"
    echo Generating report html file...
    sqlplus /nolog > $JMONPATH/jmon_sqlplus.log < conn $USER/$PASS@$DB
    set serveroutput on
    execute osk\$jmon.ahaha
    show error
    EOF
    echo report done
    echo sending mail
    mail `cat $JMONPATH/jmonlist` < $ARPATH/data/disp/out/jmon_report.html
    echo mail sent
    echo delete report from directory
    rm $ARPATH/data/disp/out/jmon_report.html
    echo jmon done
    exit

    PROCEDURE AHAHA -------------------------------------------------
    PROCEDURE ahaha
    IS
    vfile utl_file.file_type;
    BEGIN
    vfile := utl_file.fopen('/u03/app/DEV2/data/disp/out','jmon_report.html','w');
    utl_file.put_line(vfile,'halo halo');
    utl_file.fclose(vfile);
    END ahaha;



    JMON_SQLPLUS.LOG ----------------------------------------------
    SQL*Plus: Release 8.0.4.0.0 - Production on Wed Feb 25 10:10:34 2004

    (c) Copyright 1997 Oracle Corporation. All rights reserved.

    SQL> Connected.
    SQL> SQL>
    PL/SQL procedure successfully completed.

    SQL> No errors.
    SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - Production


    CRONOUTPUT.FILE -------------------------------------------------
    Generating report html file...
    report done
    sending mail
    mail sent
    delete report from directory
    jmon done

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

    I don't know what should be wrong... from shell command line it works without any problems BUT from crontab doesn't create jmon_report.html file....

    HEEELP !!

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    You must set ORACLE_HOME, ORACLE_SID etc.
    cron does not run your .profile, you need to run it yourself. The easiest way is call it with absolute path from inside your script before calling sqlplus, e.g. ". /u03/app/DEV2/janjaci/.profile"
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    ?

    I don't think so that there is a problem, because...

    the procedure was run => ORACLE_HOME, ORACLE_SID is ok

    in my .profile -> there isn't anything (only information for mail...)

    :(

  4. #4
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    i'm sorry

    you're right, sorry for doubt raising...

  5. #5
    Join Date
    Feb 2004
    Posts
    77
    If you are running the script through crontab, .profile is not executed before the shell script exceution. When you execute the script from unix prompt, your env is setup by execution of .profile (at the time of login).

    I would suggest to add ". .profile" to your script and see if it works.

    Add it after "#!/bin/sh".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width