Solved

SQL developer in an Oracle fix - best way to schedule a SqlPlus script to run?

Posted on 2014-02-07
22
1,149 Views
Last Modified: 2014-02-10
I am not an Oracle/linux developer but am working on an Oracle/linux project for a remotely hosted database.  I've succeeded in creating two sqlplus scripts (.sql) which create text output using spool, and can download these via sftp and import the results into SQL Server on my own planet.  

The problem is, I need one script (main.sql) to run nightly and the other (today.sql) to run every 5 minutes.  

What is the best way to approach this?  Cron or DBMS_SCHEDULER?  Obviously I will need examples--I haven't had to fool with a cron in 15 years and am only just getting the feel of Oracle.

Version info:

Linux 2.6.32-279.9.1.el5.vs2.3.0.36.29.6.23.JRB.8.x86_64 x86_64


Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


Thanks!
0
Comment
Question by:jaw0807
  • 11
  • 4
  • 4
  • +1
22 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yourcrontab might look something like this...

0 0 * * *                                  your_nightly_script
0,5,10,15,20,25,30,35,40,45,50,55 * * * *  your_five_minute_script

Open in new window

0
 
LVL 23

Expert Comment

by:David
Comment Utility
Whereas in OEM scheduler there are similar constraints to easily set up your SQL script - even if you want to add an O/S mailx command to transport your spool files.  I tend to push cron for little, quick things that are being tested.  Downside, one typo can break your entire cron schedule.
0
 

Author Comment

by:jaw0807
Comment Utility
Thanks sdstuber, but... are you saying I can refer to an Oracle sql script (main.sql, today.sql) in the cron by name and not have to call the oracle service somehow?   If it needs to be run from within another script I need to know that, and what the script should look like.

As I've said, I don't do much in the linux/Oracle world, and this is a hosted environment which I have some access to for this project.
0
 

Author Comment

by:jaw0807
Comment Utility
Thanks DVZ, it sounds like you are saying it's better to schedule from within Oracle?  I would prefer that too as I don't want to impact the system any more than is necessary.  I don't need the automation to do anything but run the script, since I can automatically download the spooled files via SFTP on a schedule.

Can you tell me what steps I need to do to have Oracle run the jobs, if that is indeed what you are saying?
0
 
LVL 23

Accepted Solution

by:
David earned 300 total points
Comment Utility
Go with sdstuber (ID: 39843073) for simplicity.  What I believe you need, then, is the shell wrapper that goes around the SQL scripts.  LOTS of examples on Internet and on our E-E knowledge base.

Basically edit (vi) in your O/S account:

export ORACLE_SID =<database service name> # note no space between = and the right side
export ORACLE_HOME=<whatever, like /u01/app/oracle/product/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH
cd xxx #whichever location you want the output to go to
$ORACLE_HOME/bin/sqlplus >> EOF
username / password
SPOOL youroutput
whatever sql commands
SPOOL OFF
exit
EOF
0
 

Author Comment

by:jaw0807
Comment Utility
Thanks.  I have the spooling handled in the sql file already though.  Would it be like this then?
----------------------------------------------------------
export ORACLE_SID =<database service name> # note no space between = and the right side
export ORACLE_HOME=<whatever, like /u01/app/oracle/product/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus >> EOF
username / password
/exlibris/aleph/u20_1/lva50/print/main.sql
exit
EOF
----------------------------------------------------------

Also, the commands ot find the oracle sid don't work.  This is what I found in the oratab:

aleph20:/exlibris/app/oracle/product/11:Y

Does this mean aeph20 is the CID and exlibris/app/oracle/product/11 is the Oracle_home?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yes

Remember to include the leading "/" on the path

exlibris/app/oracle/product/11

is not the same thing as

/exlibris/app/oracle/product/11
0
 

Author Comment

by:jaw0807
Comment Utility
I'm not having success with my script.

Here's the SQL:

spool main.txt
clear columns
set echo off
set termout off
set trim on
set wrap off
set feedback off
set pagesize 0
set linesize 30000
set colsep on
set colsep "|"
set heading off

sELECT Z303_REC_KEY || '|' || rtrim(Z303_NAME) || '|' ||
Z305_BOR_TYPE || '|' ||
Z305_BOR_STATUS  || '|' ||
Z303_OPEN_DATE || '|' ||
Z305_EXPIRY_DATE   || '|' ||
substr(Z308_REC_KEY,1,2)  || '|' ||
substr(Z308_REC_KEY,3,20) || '|' ||
substr(Z308_STATUS,1,2) || '|' ||
to_char(systimestamp) || '|'
from Z303, Z305, Z308
WHERE Z303_REC_KEY=substr(z305_rec_key,1,12)
and Z303_REC_KEY=Z308_ID
and Z305_BOR_STATUS In ('01','02','03','05')
AND Z305_EXPIRY_DATE>=to_char(sysdate,'YYYYMMDD')
AND substr(Z308_REC_KEY,1,2)='01';


spool off;

Open in new window



Works great when I run it from a sqlplus prompt just by doing

@main




Here's my shell script:

#!/bin/sh
export ORACLE_SID=aleph20
export ORACLE_HOME=/exlibris/app/oracle/product/11
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus >> EOF
myuserid / mypwd
/exlibris/aleph/u20_1/lva50/print/patronBCmain.sql
exit
EOF

Open in new window


This is the error I get

runPBCmainBatch.sh: line 6: myuserid: command not found
runPBCmainBatch.sh: line 7: /exlibris/aleph/u20_1/lva50/print/main.sql: Permission denied

Open in new window



I'm not sure the cron approach will be simpler.
0
 
LVL 23

Expert Comment

by:David
Comment Utility
To call a script within SQLPLUS, precede the line with @.  That should clear the line 6 as well.
0
 

Author Comment

by:jaw0807
Comment Utility
I found another example and was able to get this script to run while logged in as the user ID we have for doing basic data operations.

#!/bin/sh
user="sqlplusID"
pass="sqlplusPWD"
sqlplus -S $user/$pass <<EOF
  @patronBCmain;
 exit;
EOF

Open in new window


I can get this one to execute without problems.  The other one (which I had tried first with the @ and then without before posting last night) gives me permission errors no matter what.  

I can't run either when I am logged in the way they ask me to for root access.  This makes me nervous about trying to add this script to the cron.   This method we are trying also introduces a lot of linux-based variables (file permissions and locations etc) that are only going to confuse matters because I'm not knowledgeable about linux.

Since I haven't been able to get certain things, like the system commands to get the oracle SID, to work, I think this server is not configured in a predictable way.  It's a large site that hosts a large number of databases for its customers.  They've locked down a lot.

Can we discuss the other option for getting this to run, please?  The OEM/DBMS_SCHEDULER sounds like the Oracle equivalent of the SQL Server Agent, and  makes the most sense to me since database is where I live.
0
 

Author Comment

by:jaw0807
Comment Utility
Re the scripts:  I now have both of these runnable using the usual login, however the first one appears to hang and does not return me to a prompt.  However it finishes the sql script fine, which I can see once I type 'quit'  The second one finishes and returns me to a prompt.  Pretty sure it's an EOF thing, I don't know how to apply it with my changes.

maintest.sh
 
#!/bin/sh
export ORACLE_SID=aleph20
export ORACLE_HOME=/exlibris/app/oracle/product/11
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus -s sqlUID/sqlPWD @/exlibris/aleph/u20_1/lva50/print/patronBCmain.sql;
exit;

Open in new window


This one returns me to a prompt.

runPCBmain.sh
#!/bin/sh
user="sqlUID"
pass="sqlPWD"
sqlplus -S $user/$pass <<EOF
  @patronBCtoday;
 exit;
EOF

Open in new window


I don't necessarily want to use these if using a shell script can be bypassed by using the Oracle scheduling tools.  Just updating on "progress".
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
Comment Utility
>> however the first one appears to hang and does not return me to a prompt

Last line in patronBCmain.sql should be an exit.
0
 

Author Comment

by:jaw0807
Comment Utility
Last line in patronBCmain.sql should be an exit.

Ok, thank you, that change does allow the .sh to run and returns to a prompt, although it also kicks me out of SQLplus if I run it when logged into sqlplus.  That's fine if it's necessary to get it to execute from a shell. I had thought the exit on line 6 of maintest.sh should have taken care of that, since it did on line 6 of runPBCmain.sh.  (?)

Setting that aside for the moment, since I appear to have two working shell scripts that will take care of calling the Oracle service and running the sqlplus scripts, am I correct that this is what I need to add to the crontab to schedule them (since I'm not getting any responses on using the DBMS_SCHEDULER)?


# run main barcode collection at midnight
00 00 * * * /exlibris/aleph/u20_1/lva50/print/runPBCmain.sh

# run today's barcodes every 5 minutes
*/05  * * * * /exlibris/aleph/u20_1/lva50/print/runPBCtoday.sh

Open in new window



Incidentally, I still cannot run these under any linux login other than the one we use to access the data and run reports (which dos not have access to the cron).  Therefore I'll be pretty surprised if these run after being added to crontab.
0
 
LVL 23

Expert Comment

by:David
Comment Utility
Jaw, I regret I wasn't available over the weekend -- but as our thread expanded, I tried to express that your "lock down" situation was far better suited to cron (ID: 39843419).  Cron may be run by any account, provided the sys admins permit it.  BTW, both Windows and UNIX have an "AT" command for testing cron-like jobs.

An example of Oracle's tool, and overly simplified:
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

Hope this helps get you to a simple, sustainable solution.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>although it also kicks me out of SQLplus if I run it when logged into sqlplus.

As it should:  It 'exit's sqlplus.

>>That's fine if it's necessary to get it to execute from a shell. I had thought the exit on line 6 of maintest.sh should have taken care of that, since it did on line 6 of runPBCmain.sh.  (?)

Two different programs involved:  a SHELL and sqlplus.  You need to explicitly exit sqlplus to return control back to the SHELL.
0
 

Author Comment

by:jaw0807
Comment Utility
Since nobody said my cron examples would blow up the planet,  I went ahead and added the second job (every 5 minutes) to the cron and a few minutes later got the prompt that I had new mail in /var/spool/mail/root.  I did a cat to get the mail and this was the message:


Mon, 10 Feb 2014 14:40:01 -0500
Date: Mon, 10 Feb 2014 14:40:01 -0500
Message-Id: <201402101940.s1AJe11a016378@chivs01aleph01.hosted.exlibrisgroup.com>
From: xxxx (Cron Daemon)
To: xxxx
Subject: Cron <xxxx> /exlibris/aleph/u20_1/lva50/print/runPBCtoday.sh
Content-Type: text/plain; charset=ANSI_X3.4-1968
Auto-Submitted: auto-generated
X-Cron-Env: <PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin>
X-Cron-Env: <SHELL=/bin/bash>
X-Cron-Env: <HOME=/root>
X-Cron-Env: <LOGNAME=root>
X-Cron-Env: <USER=root>

/bin/bash: /exlibris/aleph/u20_1/lva50/print/runPBCtoday.sh: Permission denied

Open in new window



By searching around I found that I needed to make runPBCtoday.sh executable; once this was done the job was able to run and no further emails were sent.  I added the other line after making the main script executable.  I anticipate that this will run without issue.

@slightwv, I still don't quite understand why the exit could not be controlled from maintest.sh  as it was in runPBCmain.sh (in my examples above) but I appreciate the fix you suggested.  

@dvz, thanks, I certainly didn't expect answers over the weekend.  I did see some basic  examples of using DBMS_SCHEDULER like the one you recommended but apparently there are tricks to using it with SQLPlus scripts rather than PL/SQL, for which I could not find any complete examples of creating such a job and then scheduling it.

Between you two and a lot of searching, it is done, and I hope that's it for a while.  It'll be nice to switch back to the mssql side of this project!  

Thanks for your help.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I still don't quite understand why the exit could not be controlled from maintest.sh  as it was in runPBCmain.sh (in my examples above)

When you executed sqlplus, you passed control from the shell to sqlplus.  The shell then sits back and waits for control to be passed back.
0
 

Author Comment

by:jaw0807
Comment Utility
Can you further explain the functional difference (for the purpose of this question) between the two versions?  The second version returned control after executing the SQL.  Again, maybe it's that I don't understand what the EOF in the second one is doing.  For maintest.sh I cobbled together what dvz gave me with some things I found elsewhere but as mentioned, could not guess how to correctly replace the EOFs he supplied in the oiginal.

Again, the critical problem appears to be resolved, so I don't want to flog this too much, but I would like to understand.  


Thanks.


maintest.sh
 
#!/bin/sh
export ORACLE_SID=aleph20
export ORACLE_HOME=/exlibris/app/oracle/product/11
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus -s sqlUID/sqlPWD @/exlibris/aleph/u20_1/lva50/print/patronBCmain.sql;
exit;

Open in new window


runPCBmain.sh

#!/bin/sh
user="sqlUID"
pass="sqlPWD"
sqlplus -S $user/$pass <<EOF
  @patronBCmain;
 exit;
EOF

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
The first is executing sqlplus in it's own shell (sort of) and it then waits.

The second is a here document (described below) and redirects all the commands into sqlplus.

>>understand what the EOF in the second one is doing

It's called a HERE document.

It uses redirects '<' and '>' to read input from the HERE doc into the command.

The 'here' got is name because it executes from:
here
....all the way down to
here

Where the 'here' above is whatever tag you set up.  In your example you use the string EOF so it will pull in everything from the first EOF until the second.

http://en.wikipedia.org/wiki/Here_document
0
 

Author Comment

by:jaw0807
Comment Utility
That at least gets me to the point of planning a couple of experiments when this is over.  Thanks for taking the time to explain.  

Have a great week~
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Why the penalty grade?
Also, if you went with cron, then a split for the cron schedule seems appropriate.
0
 

Author Comment

by:jaw0807
Comment Utility
I was aware of the cron as an option (not the preferred one) before I posted my question, and if you look you will see that the scheduling method I used was not the one you suggested.  I found the methods for backing up, editing and adding a schedule to the cron on my own, and you didn't seem to have read my question; I said that I had some SqlPlus scripts, which cannot apparently be run from the cron directly.  I'm sorry, I just did not find that your answer helped in working the solution.  If I just wanted to know how to add an already working shell script to the cron, I could have found that myself  (and once I had the already working shell scripts, I did so).

I gave the EE experience on this question overall a B grade because I felt I had to fill in a lot of gaps myself that perhaps experienced users would forget to mention to someone who doesn't work in their platform all the time.   Sometimes you get the magic wand, other times it's just not quite so immediate.  I thought a B appropriate, not insulting.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now