sqlplus: could it be dirrected do not printout anything, beside results?

Shell Programming and Scripting
1

I try to prepare a sufficient function to execute a sql-statement, getting back ONLY retrieved results!

What I can't figured out how to make the sqlplus not printing the 'Connected to ...', 'Disconnected from...' and the executed statements after 'SQL> '.

I am under impression that having the sqlplus suppress that information by itself, than filtering it outside of the sqlplus by 'grep' or 'sed', will be quicker

I have tried the
'sqlplus $cred @sql_script' and
'sqlplus $cred <<EofSQL'
Last one works better, but I am spooling and filtering the '^SQL> ' lines.

I would like to do not involve the 'sed ' and spool file.
Would be the best to make sqlplus avoid printing beginning and end information and accept SQL-statements silently!

Does anyone know how and if it possible at all to do?
That what I have by now (just for reference)

run_sql ()
{
    #change the SQL-way-comments (--...\n )  to C-style: (/* ... */\n)
  sql_cmd=$(echo "$*" |sed ' /--/s/$/\*\//;/--/s/--/\/\*/g;');
  sqlplus $AIM_PSWD >/dev/null <<STOP_SQLPLUS
      -- do not display summary of sql-command execution
    set FEEDBACK OFF
      -- do not display collumn names in sql execution results
    set HEADING OFF
      -- followed 2 should improve performance
    set APPINFO OFF
    set DEFINE OFF
      -- this one sets the secondary prompt to main one (that is used when sql-statement use more than 1 line)
    set SQLNUMBER OFF
    set linesize 1000
    spool sqlplus.rsl
    $sql_cmd
STOP_SQLPLUS

    rc=$?;
    sed "/SQL/d; /^$/d; /^[ ]*$/d" sqlplus.rsl
return $rc
}
2

set auto off
set echo off

3

Appreciate replay, vbe, but doesn't work. (Actualy, I have tried that already. Maybe there is something that prevent that sets to work?)

--44672:/export/home/dca0701/develop/src/ReLINK> run_tst()
> {
> sqlplus $AIM_PSWD <<!
> set FEEDBACK OFF
> set HEADING OFF
> set AUTO OFF
> set ECHO OFF
> $*
> !
> }
--44674:/export/home/dca0701/develop/src/ReLINK>
--44674:/export/home/dca0701/develop/src/ReLINK> run_tst "select sysdate
> from dual
> ;
> "

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Jul 10 10:51:31 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> SQL> SQL> SQL> SQL>   2    3
10-JUL-09
SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

I need just like this:

--44675:/export/home/dca0701/develop/src/ReLINK> run_sql "select sysdate
> from dual
> ;
> "
10-JUL-09
--44676:/export/home/dca0701/develop/src/ReLINK>

Is there any way to make those sets to work, or any other way to eliminate those stuff??

4

Can you please explain, what exact output you need and what you get?..

5

Maybe something like this ?

$ 
$ cat run_sql
#!/bin/bash
sqlplus -s /nolog <<EOF >sqlplus.rsl
connect test/test
set heading off
$1
EOF

$ 
$ ./run_sql "select sysdate
> from dual
> ;
> "
$ cat sqlplus.rsl

11-JUL-09

$ 
$

tyler_durden

6

Yes, durden_tyler, that '-s' is what I was looking for!
Appreciate it!!

One difference from your example is that for me the 'connect ..' command always shows '>connected' output (but I did not redirect to file; I use std-output; but, it shouldn't be a matter, right)

src> typeset -f run_his
run_sql1 ()
{
    sqlplus -s /nolog  <<EOF
connect $AIM_PSWD
set FEEDBACK OFF
set HEADING OFF
set APPINFO OFF
set DEFINE OFF
set linesize 1000
$*
EOF

}
src>
src> run_sql1 "
;
> select sysdate
> from dual
> ;
> "
Connected.

13-JUL-09
src>

It is not a problem, just surprising. I have changed '/NOLOG' to the connection string (in my exml it is $AIM_PSWD) and 'connected ' disapeared.