Visit Our UNIX and Linux User Community


SQL vs Crontab


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users SQL vs Crontab
# 1  
Old 09-20-2002
SQL vs Crontab

I've been trying to execute a script which call some SQL Queries for midnight report, using crontab I got the next mesage:

Message file sp1<lang>.msb not found
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
Error 6 initializing SQL*Plus

If I use the "at" command it works perfectly but if I use crontab doesnt do anything.

The sp1us.msb file is located into
$ORACLE_HOME/sqlplus/mesg/

I've already added this path into the oracle user environment but simply it didnt work

Any idea?Smilie
# 2  
Old 09-20-2002
What does your script look like, most importantly the part that calls SQLPlus?
# 3  
Old 09-20-2002
Scipt

this is as easy as it seems:
$ more Report.sh
sqlplus user/passwd @script1.sql
sqlplus user/passwd @script2.sql

at the begining I remembered that cron does not know the binaries path so I changed the script to this one:

/oracle/ora8/bin/sqlplus user/passwd @script1.sql
.
.
and so on.
It didnt work as well.

Later on I wrote tha absolute path of the queries:
/oracle/ora8/bin/sqlplus us/pass @/home/script1.sq
.
.

and so on, every change I did every test I performed with the "at" command and it worked out pretty fine, but if I use crontab it doesnt.

and the message was related to the sp1<lang>.msb file Im using US as lenguaje, so the file to be search must be ps1us.msb it is located at
$ORACLE_HOME/sqlplus/mesg
(oracle/ora8/sqlplus/mesg)

I hope it helps.
# 4  
Old 09-26-2002
Hi
Have you tried su in crontab:

su user -c "/oracle/ora8/bin/sqlplus us/pass @/home/script1.sq"

j
# 5  
Old 09-26-2002
i think the problem is that the crontab evironment of a user is not the same evironment when the same user is logged in.

When you set the correct PATH in the script with you fire by the crontab it will work.

Previous Thread | Next Thread
Test Your Knowledge in Computers #691
Difficulty: Medium
UnixWare is a Unix operating system originally released by Univel.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. UNIX for Dummies Questions & Answers

Crontab + Script + .sql

Hi guys today i'll bring to you a new problem that i need to execute. So what i need to do it's create a script that: conect to some database logon run a .sql script logoff and close the connection after that, put this script on the crontab To set up the crontab it's ok for me, i think... (3 Replies)
Discussion started by: Newer
3 Replies

3. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

4. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

5. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

6. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

7. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

8. Shell Programming and Scripting

Access SQL with crontab

Hi, I wrote a script shell whose function is to make a remove of directories depending on the result of a query on Oracle database. When I execute my script shell directly from unix, it works fine. But, when I put it in a line of a crontab, it doesn't work no more, because of the sql... (1 Reply)
Discussion started by: tbeghain
1 Replies

9. Shell Programming and Scripting

crontab and shell script that executes a sql.

Problem: I have a crontab and when it kicks off, xxx.sh shell script is called. Which has a nohup sqlplus session call. Problem is sql does not get executed and a text file is not getting created. Only a empty log file is getting created. Are there any constraints for crontab to open a sql... (6 Replies)
Discussion started by: radhika
6 Replies

10. UNIX for Dummies Questions & Answers

running sql in crontab

Hi, i have a scripts that update an SQL DATABASE using sqlplus command. when i run it in a interactive mode its ok but when try to run it using the crontab i get an a messege : "Must be attached to terminal for 'am I' option" (there is no "who am i" command in the script) and the DB... (4 Replies)
Discussion started by: dorilevy
4 Replies

Featured Tech Videos