| accessing v$ tables from shell script [message #98192] |
Mon, 03 May 2004 05:16  |
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 #98196 is a reply to message #98193] |
Mon, 03 May 2004 23:48  |
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 |
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 |
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"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
