Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help needed with KSH and SQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help needed with KSH and SQL
# 1  
Old 07-05-2010
Help needed with KSH and SQL
Hi All

I have a problem with one of my scripts which checks for he status of an Oracle database.

Here's the script:

Code:
#!/bin/ksh

sqlplus -s  '/nolog' <<EOF
connect / as sysdba
set heading off
spool db_status.txt
select OPEN_MODE from v\$database
/
exit
EOF

db_status=$(more ~/db_status.txt)

rm ~/db_status.txt
echo "|DB Status (Open Mode)|`date`|" $db_status "|"; > $MESSAGE_LOCATION/$$.$ORACLE_SID.dbstatus.txt_tmp
exit

If the database is open, mounted etc then the script works as it should do. However, if the db is shutdown, I get the following:

Code:
|DB Status (Open Mode)|Mon Jul  5 09:59:11 BST 2010| select OPEN_MODE from v$database 0022_create_roles.sql 0032_create_users.sql 0035_user_perms.sql admin afiedt.buf cfgtoollogs db_status.txt diag disk_usage.sh flash_recovery_area instchkc.err instchkc.out instchkc.txt jnettrace.jar oradata oradiag_oracle ora_env.sh oraInventory product run_0022_create_roles.lst run_0032_create_users.lst run_0035_user_perms.lst ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 |

It looks like it's printing the contents of the current working directory into the text file. Is this is korn shell 'bug' or is it something I'm missing?

Any help would be greatly appreciated!

TIA
Jake.
# 2  
Old 07-05-2010
There is no error, it's the normal behaviour.

If you don't want to get anything other the "ORA-....", in case of oracle shutdown , check the "SET " options in sqlplus , to turn off the sql appearing in the output.
# 3  
Old 07-05-2010
Quote:
Originally Posted by panyam
There is no error, it's the normal behaviour.

If you don't want to get anything other the "ORA-....", in case of oracle shutdown , check the "SET " options in sqlplus , to turn off the sql appearing in the output.
Hi Panyam

I don't understand how this could be normal behaviour - I've not specified anywhere in the script that I want the contents of the dir to be printed.

Unfortunately, I'm unable to format the output using the SET functionality in sqlplus as I'm not just looking for ORA codes but other statuses like 'READ WRITE', MOUNTED etc.

I was hoping this would be a straightforward one!!
# 4  
Old 07-05-2010
The error message which you captured in db_status had a "*" in it. Change
Code:
echo "|DB Status (Open Mode)|`date`|" $db_status "|"

to
Code:
echo "|DB Status (Open Mode)|`date`| $db_status |"

Anyhow, you should put something like:
whenever sqlerror exit 1
whenever oserror exit 1
at the beginning of your sql scripts. Then check the exit code of sqlplus.
# 5  
Old 07-05-2010
Quote:
Originally Posted by huskie69
...
I don't understand how this could be normal behaviour - I've not specified anywhere in the script that I want the contents of the dir to be printed.
...
binlib is right; check this thread with a detailed response to a similar problem:

https://www.unix.com/shell-programmin...ion-error.html

tyler_durden

A short testcase follows -

Code:
$ 
$ # show all the files in current directory
$ ls -1
file1
file2
file3
file4
file5
$ 
$ # set the value of shell variable x
$ # such that it has a "*" in there
$ x="HELLO * WORLD"
$ 
$ # now print the value of x within double quotes
$ echo "x = $x"
x = HELLO * WORLD
$ 
$ # now print the value of x without double quotes
$ echo x = $x
x = HELLO file1 file2 file3 file4 file5 WORLD
$ 
$ 
$ # that's because the shell expands "*" to the list of files in current directory
$ 
$

Last edited by durden_tyler; 07-06-2010 at 12:14 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Code needed to get sql queries

Hi i need code to get sql queries through a shell script for a text file input which contain the service ids iputfile I-H-2048-10GB-M I-H-4096-12GB-M I-H-2048-p1000-M the code should contain below queries among which service_id is replacable with value from input file. ... (4 Replies)
Discussion started by: surender reddy
4 Replies

2. Programming

sql guide needed

SQL> select 2 distinct qc_m|| ',' || 3 to_char(logon_dt, 'YYYY-MM-DD HH24:MI:SS')|| ',' || 4 CASE WHEN logon_dt IS NOT NULL THEN 'LOGON' ELSE 'LOGOFF' END 5 from qc_ct2 qc left join v_tls_equipment_logon tls on qc.qc_m=tls.equipment_id where 6 qc.terminal_c='T'and... (1 Reply)
Discussion started by: ment0smintz
1 Replies

3. Shell Programming and Scripting

Guidance needed for a typical shell script with sql query

Hi , I have a txt file with contents like: 1234 2345 3456 7891 I need to write a script which takes input file as txt file..run a sql query for that number and place the output of query in another file.. select * from bus_event where acct_nbr='1234'( from input txt file) the query... (20 Replies)
Discussion started by: Rajesh Putnala
20 Replies

4. Shell Programming and Scripting

Help needed in ksh scripting

I got a task to do today, I want to write a script for the following 1) Archive log more than 2 days old 2) Number of days the current Archive logs present 3) Total number of archive logs 4) Size of each archive log 5) When was last successful backup happened? Can I get a general script... (8 Replies)
Discussion started by: Rambo
8 Replies

5. Shell Programming and Scripting

ksh scripting help needed.

I am trying to create a script to manipulate numerous file and at first I thought it would be a simple task but at this point i am ready to break my computer! I am new to unix scripting so hopefully someone out there thinks this is super easy and can help me out! A sample of the problem file is... (2 Replies)
Discussion started by: theqcup
2 Replies

6. Shell Programming and Scripting

Confirmation needed while running the sql's

Hi, I need confirmation whether the same functionality exists when running the sql's. For ex. let us consider there exists a sql file with name june.When running in sql mode does: @june.sql and start june.sql does the same functionality. Someone help me. Thanks Ashok. (1 Reply)
Discussion started by: Ashok_oct22
1 Replies

7. Shell Programming and Scripting

what is the uses in sql script in ksh

hi, iam new of unix. how to call sql script in ksh script (1 Reply)
Discussion started by: naveeng.81
1 Replies

8. Shell Programming and Scripting

Help needed in script and sql file

Hi all, I have a script which takes in sqlfile as argument and executes the sql execSqlFile() { sqlFile=$1 sqlplus -S $DBLOG/$DBPWD@$DBSVR < $sqlFile } This works fine if the sql file is plain, simple and pre-defined statment. But i want to have a sql file which itself... (2 Replies)
Discussion started by: jakSun8
2 Replies

9. Shell Programming and Scripting

Dynamic SQl in KSH

My requirement is to create a KSH to generate the SQL select statement in oracle with all the columns and optional where condition if given the table name as input to the program Have any of you worked with a similar requirement? Can you give me some inputs? Regards, Kousikan (2 Replies)
Discussion started by: kousikan
2 Replies

10. AIX

help needed on aix ksh

i am trying to convert .ksh script to .bat in windows box , it says awk is not recognized as internal or external command. this is the sample .ksh script , even if i change awk to gawk it does not work its giving me syntax error, at line one ---- it's pointing ":" and "fund:" some one plzz help.... (0 Replies)
Discussion started by: 2.5lt V8
0 Replies
Login or Register to Ask a Question