cancel
Showing results for 
Search instead for 
Did you mean: 

sqlplus ksh strange problem

Leo The Cat
Regular Advisor

sqlplus ksh strange problem

Hi guys


I'm doing this:

spout=`$ORACLE_HOME/bin/sqlplus /nolog << EOF
set echo off feedb off
SET PAGESIZE 80
SET LINESIZE 190
connect / as sysdba
/
select version from v$instance;
exit success
EOF`



Now if do
echo $spout

I have this:

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 20 17:53:31 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> SQL> SQL> SQL> Conne
cted. SQL> SP2-0103: Nothing in SQL buffer to run. SQL> select version from v SR28132.log aa.sql afiedt.buf automation cbarba dje.tmp echin imports li
st_unix_space_gb.ksh mbox mholkar rename_files.sh setup_ede shortcuts.sh smit.log smit.script smit.transaction sqlnet.log sqlplus timezone ERROR at li
ne 1: ORA-00942: table or view does not exist SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With
the Partitioning, Data Mining and Real Application Testing options


!!!

I suppose that some output characters like * are expanded by OS.

Hos to eliminate this bad behaviour ?


Bests Regards
Den
4 REPLIES
Leo The Cat
Regular Advisor

Re: sqlplus ksh strange problem

Hi again

I have find a workaround. In fact the $instance (particulary the $) is a problem here.

I have replaced with that:

spout=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
set echo off feedb off
SET PAGESIZE 80
SET LINESIZE 190
connect / as sysdba
select property_value from database_properties where property_name ='NLS_RDBMS_VERSION';
exit success
EOF`


Thats's work now but I'm not entirely satisfied.

Have a nice week-end Guys
Den
Steven Schweda
Honored Contributor

Re: sqlplus ksh strange problem

> I suppose that some output characters like
> * are expanded by OS.

Where is the "*"?

> select version from v$instance;

I'd expect the shell (not the OS) to expand
$instance
here, but I don't know what value that might
have. If you were trying to specify a
literal "v$instance" there, then you might
try quoting or escaping "$" appropriately,
like, for example:

select version from v\$instance;

What were you expecting to do there?

Using "bash", for example:

alp$ echo $HOME
/ALP$DKA0/SMS

alp$ cat hd.sh
#!/bin/sh

cat <$HOME
\$HOME
EOF
alp$

alp$ ./hd.sh
/ALP$DKA0/SMS
$HOME
alp$
Dennis Handly
Acclaimed Contributor

Re: sqlplus ksh strange problem

>I suppose that some output characters like * are expanded by OS.

Yes, here documents are expanded by the shell.

>How to eliminate this bad behaviour?

Besides quoting each "$", etc as Steven mentions, you can quote the EOF word:
spout=`$ORACLE_HOME/bin/sqlplus /nolog <<\EOF
Viktor Balogh
Honored Contributor

Re: sqlplus ksh strange problem

Hi,

If you are using the ksh shell, you can check what gets expanded. You only need to set the shell variables "verbose" and "xtrace".

# set -vx
# echo $0
echo $0 # this line is the command you issued - verbose mode
+ echo -ksh # this line is the command after the shell expansions - xtrace
-ksh # this is the output
#

Regards,
Viktor
****
Unix operates with beer.