Skip to main content

using cron to execute sqlscript in oracle

Using cron to run sql script is a good idea to apply with a periodic database job. When running any scripts, cron doesn't know any environment variable. So, in the script, we always
-use full path with execute command.
-define all environment variable.

ex. to see data in customer table in oracle database and log in customer_rpt.log, I create sql script[testsc1.sql] like this
[oracle@oracle1 ~]$ cat testsc1.sql
spool /home/oracle/customer_rpt.log
select * from customer;
quit;
[oracle@oracle1 ~]$


next create shell script file to run sql script file.
[oracle@oracle1 ~]$ cat mydbshell.cron
#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1; export ORACLE_HOME;
ORACLE_SID=my_sid; export ORACLE_SID;
/u01/app/oracle/product/10.1.0/db_1/bin/sqlplus user/password @/home/oracle/testsc1.sql
[oracle@oracle1 ~]$
note that oracle_home, oracle_sid variable must be defined and sqlplus command called with full path.

Now, we define new cron job by 'crontab -e' in console and make it look like this[this is only example you need to modify it as you wish. to learn more about crontab type 'more crontab' ]
[oracle@oracle1 ~]$ crontab -l
0 4 * * * /home/oracle/mydbshell.cron
[oracle@oracle1 ~]$

Comments

Popular posts from this blog

Converting dat to mpeg file, to burn vcd on ubuntu

Creating vcd [from dat file] on WinXP, I use "Nero Burning Rom". It can burn dat file to vcd directly. On ubuntu, AFAIK, No burning cd software to burn dat to vcd. I use k3b but it can't burn dat file. It support only mpeg-1 and mpeg-2 format. Dat file need to convert to mpeg first. Thist post will show how I install and use vcdgear to convert dat file to mpeg file. pnix@pnix-a7n:~/progdl$ wget http://www.vcdgear.com/files/vcdgear176-040415_linux.tar.gz --04:21:14-- http://www.vcdgear.com/files/vcdgear176-040415_linux.tar.gz => `vcdgear176-040415_linux.tar.gz' Resolving www.vcdgear.com... 66.98.192.104 Connecting to www.vcdgear.com|66.98.192.104|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 442,937 (433K) [application/x-tar] 100%[====================================>] 442,937 105.23K/s ETA 00:00 04:21:20 (87.02 KB/s) - `vcdgear176-040415_linux.tar.gz' saved [442937/442937] pnix@pnix-a7n:~/progdl$ tar xzf vcdgear

using Class.getResource() load resource file in Eclipse

There are many ways to load resource file in java app. What sun recommended is using Class.getResource(" resource_name ") or Class.getResourceAsStream(" resource_name ") then you will get URL and InputStream respectively. If resource_name is specified without "/", it will be prepend with Class package. So resource file must be in same place[folder structure] as the Class. What I love to do is call getResource() with "/" and put resource file at the root of package. This way i can have separate resource folder. below is in Eclipse, 1. From Package explorer right click src folder->click import 2. In import dialog, Choose General->File System ->next 3. from directory:->Browse to your resource folder. 4. to folder:-> I add "resource" as a folder name under src folder. then click "Finish". In the code, load resource with this.getClass().getResource("/resource/buttons1.png") or this.getClass().getResourceAs

Fixing sendmail take a long time to start

I notice that my database box[FC6+Oracle10.2] take along time to startup. Sendmail and sm-client very very slow to start[ about 5 minutes ]. There's something wrong in /etc/hosts file. 'newalises' command take long time to update and finish with error below. [root@ora10g ~]# newaliases WARNING: local host name (ora10g) is not qualified; see cf/README: WHO AM I? /etc/aliases: 76 aliases, longest 10 bytes, 765 bytes total [root@ora10g ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost 192.168.1.55 ora10g [root@ora10g ~]# To fix this, custom hostname[ora10g] need to append to localhost line in /etc/hosts. [root@ora10g ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost ora10g 192.168.1.55 ora10g [root@ora10g ~]# newaliases /etc/alia