topic Re: sqlplus ksh strange problem in Operating System - Linux

sqlplus ksh strange problem

Leo The Cat — Fri, 20 Aug 2010 21:04:00 GMT

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

Re: sqlplus ksh strange problem

Leo The Cat — Fri, 20 Aug 2010 22:01:41 GMT

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

Re: sqlplus ksh strange problem

Steven Schweda — Fri, 20 Aug 2010 22:38:53 GMT

> 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$

Re: sqlplus ksh strange problem

Dennis Handly — Sat, 21 Aug 2010 00:18:58 GMT

>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

Re: sqlplus ksh strange problem

Viktor Balogh — Mon, 23 Aug 2010 12:16:00 GMT

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