Mailing-List: contact cygwin-help AT cygwin DOT com; run by ezmlm List-Subscribe: List-Archive: List-Post: List-Help: , Sender: cygwin-owner AT cygwin DOT com Mail-Followup-To: cygwin AT cygwin DOT com Delivered-To: mailing list cygwin AT cygwin DOT com Message-ID: <3E26D7F6.661AC785@nmmcc.com> Date: Thu, 16 Jan 2003 09:04:06 -0700 From: James Schnedar Organization: NMMCC X-Accept-Language: en MIME-Version: 1.0 To: cygwin AT cygwin DOT com CC: Chiranth , "Peter A. Castro" , Igor Pechtchanski Subject: Re: cron, Oracle, and sqlplus References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > > I am experiencing a problem with running Oracle sqlplus within a shell > > script using cron. The shell script runs correctly from the command > > line, but once I add it to cron, the shell script will execute up until > > the sqlplus command. Therefore, cron is working correctly as well, but > > the addition of the sqlplus command causes it to hang. The sqlplus > > session has started, but appears hung. > > > > ps -ef yields: > > > > UID PID PPID TTY STIME COMMAND > > SYSTEM 3296 552 ? 14:53:00 /usr/sbin/cron > > Administ 3312 3296 ? 14:53:00 /usr/bin/sh > > Administ 3388 3312 ? 14:53:00 /usr/bin/sh > > Administ 3472 3296 ? 14:53:01 /usr/sbin/ssmtp > > Administ 3508 3388 ? Dec 31 /cygdrive/d/ORACLE/806/bin/sqlplus > > > > Any suggestions? > > Thanks, > > James Schnedar Reply #1: Igor Pechtchanski wrote: > > James, > > Is sqlplus trying to open a window or access a network share? Try > checking the "Allow service to interact with the desktop" box for the cron > service,and see if it helps (having restarted cron, of course). > Igor Reply #2: "Peter A. Castro" wrote: > > sqlplus is a native windows app, so perhaps you are trying to invoke it > with a cygwin-ized path to a command file? If you can send us a section > of your shell script, where it's invoking sqlplus, perhaps we can figure > out what's going on. Also, there are some environmental variables that > can effect sqlplus execution, and cron doesn't invoke things in a full > environment. > > > -- > Peter A. Castro or > "Cats are just autistic Dogs" -- Dr. Tony Attwood Reply #3: Chiranth wrote: > > sqlplus needs some environment variables to be set, like ORACLE_HOME ( I > am not sure what others). Andd cron does not set these variables while > starting your process. Hence sqlplus hangs. > > Solution: Use the line #!/bin/bash as the first line of your shell script > and define the required environment variables in your .bashrc file. Ig > you are using the Korn shell, ensure that these variables are set in > your .profile and use #!/bin/ksh as the first line of your shell script. > It will ensure that the correct shell interpreter is invoked and the > environment variables read appropriately. > > -- > Chiranth M.C. > Netkraft Pvt Ltd All, Thank you for the replies. Through researching these replies, I discovered: 1) sqlplus does indeed open a window. Therefore, the "Allow service to interact with the desktop" box needs to be clicked. Since I am coming from a UNIX environment and am fairly ignorant of Windows, here's how to get to it (for Windows 2000) (this portion primarily for archiving on Cygwin mailing list): a) right click My Computer b) select Manage. This will open Computer Management window. c) select/double click Servies and Applications on right side d) select/double click Services on the right side e) Assuming cron has been set up correctly, you should see it as a service. Right click on cron. f) select Properties g) select Log On tab h) click "Allow service to interact with desktop" box. Done. It also took me awhile to start cron, so in case someone comes across this post to starting cron: cygrunsrv -I cron -p /usr/sbin/cron -a -D cygrunsrv -S cron to stop cron: cygrunsrv -E cron Also, if you are not logging in as Adminstrator, then you will be required to have an entry in /etc/passwd for cron to run your scripts. Administrator already exists, so copy that line and change Administrator to your user name and give yourself a new user ID. These actions allowed the cron service to run my shell script. But, see below for further sqlplus information. 2) ORACLE_HOME is inherited from the Window's repository. I did not (and should not) reset it in the shell script. I also should not set LD_LIBRARY_PATH. 3) Evidentally, sqlplus in windows environment behaves differnently than a unix environment. Primarily, it opens a new window, end of story. In UNIX, the -s option is silent mode and not interactive. For windows, the command line sqlplus command is "plusxx" where xx is the version you are on. This allows for the -s option and works with directing files in (< in_file) and redirecting output out (> out_file). 4) While working with sql scripts and running Oracle Reports from Cygwin shell scripts, once control is passed to the Oracle environments (spool'ing in sqlplus, Oracle Reports, etc.), it requires window's path names (i.e. d:\cygwin\tmp) as opposed to relying on Cygwin to interpret the path name. Background information: We are in the midst of moving from a Unix backend to a Linux backend. Oracle 9i is certified on Red Hat Advance Server 2.1. Oracle ReportWriter is not certified as stand alone on Linux. You have to buy Oracle 9i App Server (that's how they get you). Oracle Report Writer 6i is certified as stand alone on windows. Therefore, overnight batch process that is done on our current Unix backend needs to run on windows with ReportWriter 6i connecting to 9i database on linux. Cygwin is proving very beneficial in this move because we are doing very minimal rewrites to our overnight shell scripts. I may post this to Oracle, but they haven't been very helpful. Thanks for your help, it has been very benficial. -- -Jim Schnedar New Mexico Mutual Casualty Company jims AT nmmcc DOT com -- Unsubscribe info: http://cygwin.com/ml/#unsubscribe-simple Bug reporting: http://cygwin.com/bugs.html Documentation: http://cygwin.com/docs.html FAQ: http://cygwin.com/faq/