Visit Our UNIX and Linux User Community


KSH Sqlplus problem


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting KSH Sqlplus problem
# 1  
Old 03-30-2007
KSH Sqlplus problem

Hi,

trying this

===============================
Temp=`sqlplus -s user/passwd <<EOF
WHENEVER SQLERROR EXIT 1
set serverout on
set feedback off
set heading off
select nam from tabel1
where x=y;
EOF`

echo Temp>>$logfile
=================================

The select statement is wrong on purpose (the column doesn't exist)

The output I am getting is:

select nam from tabel1xyz.logbgt.out........etc....lots of filenames of the current directory....where x=y....ORA-XXX ERROR.

Also if I write the select statement as

select nam from tabel1 where x=y;

The output is

select nam from tabel1 where x=yxyz.logbgt.out........etc....lots of filenames of the current directory........ORA-XXX ERROR.

The garbage prints after the first newline I think.

This doesn't happens when the select statement is corrected.

Will appreciate any hint on this behavior ..thanks..

Amit
# 2  
Old 03-31-2007
Quote:
Originally Posted by amitkr
===============================
Temp=`sqlplus -s user/passwd <<EOF
WHENEVER SQLERROR EXIT 1
set serverout on
set feedback off
set heading off
select nam from tabel1
where x=y;
EOF`

echo Temp>>$logfile
=================================

The select statement is wrong on purpose (the column doesn't exist)

The output I am getting is:

select nam from tabel1xyz.logbgt.out........etc....lots of filenames of the current directory....where x=y....ORA-XXX ERROR.

Also if I write the select statement as

select nam from tabel1 where x=y;

The output is

select nam from tabel1 where x=yxyz.logbgt.out........etc....lots of filenames of the current directory........ORA-XXX ERROR.

The garbage prints after the first newline I think.

This doesn't happens when the select statement is corrected.

Will appreciate any hint on this behavior ..thanks..

If the slect statement is wrong, then correct it.

I don't use sqlplus, so I don't know what it takes to correct it, but the correct way to write the shell command is:
Code:
Temp=`sqlplus -s user/passwd` <<EOF
WHENEVER SQLERROR EXIT 1
set serverout on
set feedback off
set heading off
select nam from tabel1
where x=y;
EOF

# 3  
Old 03-31-2007
Hi ,

Looks like I foud the fix...

I case of an error in the select statement sqlplus puts a * below the column name.. and unix expands this wildcard, listing all the filenames.

to fix this ....
quote the variable and stop the wilcard expansion.

echo "$Temp"

thats it..

Thanks
Amit

Previous Thread | Next Thread
Test Your Knowledge in Computers #796
Difficulty: Medium
Fractions in binary arithmetic terminate only if 2 is the only prime factor in the denominator.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Hi, I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop. The first SQL file is basically a connection test and it is supposed to sort... (2 Replies)
Discussion started by: newbie_01
2 Replies

2. Shell Programming and Scripting

Line break in sqlplus output through ksh script

Hi, I am new to shell script programming. I have written a ksh script to run the sql File placed in server directory and spool the output in destination directory. Below Command: $ORACLE_HOME/bin/sqlplus -s $ora_uid @$sqlfile_loc$testquery.sql > $opfiledirectory It is generating the output... (6 Replies)
Discussion started by: Sumit Arora
6 Replies

3. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

4. Shell Programming and Scripting

Problem in quitting/exiting from sqlplus

Hello, This is my first post and I would be very thankful if you can help me. I've already searched in the forum and I've found a very similar thread in wich my problem is solved, but the thread is closed and the solution given in it doesn't work in my shell: 153194-problem-quitting-sqlplus ... (11 Replies)
Discussion started by: JuanPerez
11 Replies

5. Solaris

sqlplus output from ksh.

Hi All, I have the below simple script. It runs just fine by itself when I manually invoke it. But once I put it in the crontab with entry: * * * * * /users/myuser/test.ksh >> /users/myuser/log/test.txt" It does NOT print the returned value ($REMAIN) from the DB!? The result in the... (3 Replies)
Discussion started by: steve701
3 Replies

6. AIX

SQLPLUS problem

Hi guys, Here is the error i get by running a "sqlplus -v" after installing an oracle client 10.2.0.5 on an AIX 5.3.9 server. Could not load program sqlplus: Symbol resolution failed for sqlplus because: Symbol __pthread (number 307) is not exported from dependent ... (3 Replies)
Discussion started by: Chapel
3 Replies

7. Shell Programming and Scripting

problem in quitting from sqlplus

Hi all, I have a scenario where im connecting to sqlplus executing a query,redirecting the output to a file and manipulating the file MY CODE IS AS FOLLOWS sqlplus -s /nolog << EOF > $UTILITIES_HOME/temp/analyze.temp.log & set linesize 3000 set trimspool on set pagesize... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

8. Shell Programming and Scripting

How to acheive ALT+Enter of xls in Unix ksh/sqlplus

Hi, I am using sqlplus (called inside my ksh) to create a file which is mailed to the users as an xls. There is one DESCRIPTION column which is currently coming as wrapped, but, the users want a new line for each of the fields present in the desricription column. Below is an example - ... (11 Replies)
Discussion started by: Sree_2503
11 Replies

9. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies

10. Shell Programming and Scripting

Need help with ksh script that uses sqlplus, called from PHP

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file... (7 Replies)
Discussion started by: j2owilson
7 Replies

Featured Tech Videos