topic Re: Executing set of query in shell script in Operating System - HP-UX

Executing set of query in shell script

file system — Fri, 12 Jan 2007 02:47:55 GMT

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

Re: Executing set of query in shell script

Elmar P. Kolkman — Fri, 12 Jan 2007 02:56:44 GMT

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

Re: Executing set of query in shell script

Oviwan — Fri, 12 Jan 2007 03:08:46 GMT

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

Re: Executing set of query in shell script

Yogeeraj_1 — Fri, 12 Jan 2007 04:17:19 GMT

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