Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Executing set of query in shell script

SOLVED
Go to solution
file system
Frequent Advisor

Executing set of query in shell script

Hi all

Does anyone know executing plsql query In oracle?
I have sets of query in script but, it dose not run because system dose not recognize sets of query is sequential.

how can I set the script to run sets of query is sequential.

First. Login in oracle
Second. Run mutiple lines of query sequential

script is below..
===================
sqlplus /nolog << EOF > /oracle/x
connect / as sysdba
set linesize 200
select 'db_name ' || value from v$parameter where name = 'db_name';
disconnect
EOF
==========================
But the output is disappointed

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 12 16:35:19 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

SQL> Connected.
SQL> SQL> select 'db_name ' || value from v where name = 'db_name'
*
ERROR at line 1:
ORA-00942: table or view does not exist

3 REPLIES
Elmar P. Kolkman
Honored Contributor
Solution

Re: Executing set of query in shell script

Your problem is caused by the $parameter, which is interpreted by the shell instead of being forwarded to the sqlplus command.

This can be solved by escaping the '$' character with a backslash.
Your line will become:
select 'db_name ' || value from v\$parameter where name = 'db_name'

Another solution, though a bit dirty: insert before the sqlplus command the following:
parameter='$parameter'

Good luck
Every problem has at least one solution. Only some solutions are harder to find.
Oviwan
Honored Contributor

Re: Executing set of query in shell script

Hy

Try it like that:

sqlplus -s "/ as sysdba" < set heading off feedback off verify off
select 'db_name' || value from v\$parameter where name = 'db_name';
exit;
EOF

Regards


Yogeeraj_1
Honored Contributor

Re: Executing set of query in shell script

hi,

running PL/SQL is equally easy:

$ sqlplus -s "/ as sysdba" << EOF
> declare
> mvar date;
> begin
> select sysdate into mvar from dual;
> dbms_output.put_line(mvar);
> end;
> /
> exit
> EOF
12-JAN-07

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
$


or
$ sqlplus -s "/ as sysdba" << EOF
> declare
> mvar varchar2(30);
> begin
> from v\$parameter where name = 'db_name';
> dbms_output.put_line(mvar);
> end;
> /
> exit
> EOF
db_name mydb

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
$


hope this helps too!

kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)