Re: HELP with CRONTAB and SQLPLUS

From: Mauro Fossa <fossa_at_its.it>
Date: 1995/11/29
Message-ID: <49h9gp$pdc_at_galileo.polito.it>#1/1

pmccarth_at_rpc.unb.ca (Patrick McCarthy) wrote:

>I am running Solaris 2.4 and Oracle 7.1.4. We get customer updates from our
>accounting system and we place them on an NFS mounted drive. I am attempting
>to run the following script file from crontab to run sqlplus and do the
>updates. Unfortunately sqlplus does not seem to run. I know the script run
>because the file gets moved and stored in an update directory. I have run the
>script as the root user and it works. What can I do to get it to work from
>CRONTAB or is it not possible to run sqlplus from a crontab ?

Patrick,

I've tried the following on my AIX 3.2.5/Oracle 7.1.6 system and it does seem to work. I don't think Solaris 2.4 would make any difference as far as crontab is concerned.

---

$ login root
$ crontab -e

##
## added a line to root's crontab to start at 10:15
## an SQLplus script as user fossa, who is an Oracle user and has the
## Oracle environment, etc. set up properly
##

15 10 * * * /bin/su - fossa -c sqlplus system/password _at_a.sql > a.out

##
## The following is the script (fossa must be able to see it, so I
## made user fossa create the file
## The file simply describes the contents of a view (COL), but
## obviously you could put whatever you want
##

$ login fossa
$ cat > ~/a.sql
desc col
exit
^D

##
## Wait for execution...
##

$ date
Wed Nov 29 10:16:06 CET 1995

##
## Done !!!!
##

$ cat /a.out  

SQL*Plus: Release 3.1.3.7.1 - Production on Wed Nov 29 10:27:17 1995  

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.    

Connected to:
Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed and replication options PL/SQL Release 2.1.6.2.0 - Production  

 Name                            Null?    Type
 ------------------------------- -------- ----
 TNAME                           NOT NULL VARCHAR2(30)
 COLNO                           NOT NULL NUMBER
 CNAME                           NOT NULL VARCHAR2(30)
 COLTYPE                                  VARCHAR2(9)
 WIDTH                           NOT NULL NUMBER
 SCALE                                    NUMBER
 PRECISION                                NUMBER
 NULLS                                    VARCHAR2(19)
 DEFAULTVAL                               LONG

Disconnected from Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed and replication options PL/SQL Release 2.1.6.2.0 - Production

---

Note that the redirection in the /bin/su above is done by root, and not by fossa, so it is not necessary for fossa to be able to write on the / directory.

Hope it helps,

Mauro Fossa
fossa_at_its.it

Standard disclaimers apply Received on Wed Nov 29 1995 - 00:00:00 CET