Home » Infrastructure » Unix » accessing v$ tables from shell script
accessing v$ tables from shell script [message #98192] Mon, 03 May 2004 05:16 Go to next message
Kieron
Messages: 6
Registered: October 2000
Junior Member
Hello,

I am trying to get information from the v$database table on a database into a unix variable. Because the "$" is a reserved character in the shell, it is throwing an error saying it does not know what the v, table is. e.g.

VALUE=`sqlplus -silent username/pass <<END
       set pagesize 0 feedback off verify off heading off echo off
       select name
         from v$database;
         exit;
END`

It throws a
         from v,
              *
ERROR at line 2:
ORA-00942: table or view does not exist

Anyone know how to get around this?

Thanks in advance,
Kieron
Re: accessing v$ tables from shell script [message #98193 is a reply to message #98192] Mon, 03 May 2004 07:31 Go to previous messageGo to next message
Al Glick
Messages: 2
Registered: May 2004
Junior Member
You need to put an escape character before the $.

select name
from v$database;
Re: accessing v$ tables from shell script [message #98196 is a reply to message #98193] Mon, 03 May 2004 23:48 Go to previous messageGo to next message
Kieron
Messages: 6
Registered: October 2000
Junior Member
Hi Al,

Thanks for the tip, but I had already tried that. Here is the whole script:

#!/bin/sh
VALUE=`sqlplus -silent username/pass << END
select name from v$database;
exit;
END`
echo "$VALUE"

And when I run it, I get:

select name from v
*
ERROR at line 1:
ORA-00942: table or view does not exist

Is it due to the shell I am using or something?

Thanks,
Kieron
Re: accessing v$ tables from shell script [message #98197 is a reply to message #98196] Tue, 04 May 2004 00:26 Go to previous messageGo to next message
Chetan
Messages: 21
Registered: November 2000
Junior Member
This is because oracle is trying to directly process the query with out replacing the special character. The query should be in a .sql file in unix and this file has to be passed as parameter to sqlplus command

'select name from v$database;'
Re: accessing v$ tables from shell script [message #98202 is a reply to message #98196] Fri, 07 May 2004 16:14 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try this. I think you need two \ because there are two levels of interpretation going on becuase of the `expression`. Not sure how to post two "<", so replace my "~~" with two of them.

#!/bin/ksh
sqlplus -s scott/tiger@dev ~~EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
set serveroutput on
select name from v$database;
begin
dbms_output.put_line('Hello world');
end;
/
exit;
EOF

while read reslt_line
do
echo "==>"$reslt_line
done < tmp.txt

#############################################
VALUE=`sqlplus -silent scott/tiger@dev ~~ END
set heading off
select name from v\$database;
exit;
END`
echo "$VALUE"
Previous Topic: lsnrctl start command fails
Next Topic: Looking for CDROM for ORacle 8.0.5 Standard Edition for SUN Solaris
Goto Forum:
  


Current Time: Thu Mar 28 08:58:35 CDT 2024