Re: sqlplus

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 6 Oct 2009 09:18:42 -0700
Message-ID: <bf46380910060918x6d4b8cf1m710be40d89b23aaf_at_mail.gmail.com>

On Tue, Oct 6, 2009 at 8:20 AM, Joan Hsieh <joan.hsieh_at_tufts.edu> wrote:

> Hi List,
>
> I have a shell script report the following output for my daily check
> routine. I found the SQL> SQL> SQL> is very annoying, is it possible to get
> rid off the "lines from coonected to:" to 2 3 4...13" ?
>
> Thanks,
>
>

Here's an example of using a shell here document to call sqlplus.

There are some comments embedded to explain what is going on.

Note that the password will not appear on the command line. This is quite important, as there are still versions of unix on which the oracle password will appear to ps.

I know that in 10g and 11g on Linux the password will not appear, but that doesn't necessarily mean it is unavailable.

Also please note that scripts(oraenv) and binaries (sqlplus) are referenced via full path. This is intentional. Doing so prevents inadvertently running
any other sqlplus or oraenv that may (possibly maliciously) be in the PATH.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

• cut here ============ :

USERNAME=scott
PASSWORD=tigress
DATABASE=dv11

# setup local oracle environment
export ORACLE_SID=dv14
export ORAENV_ASK=NO

. /usr/local/bin/oraenv

SQLPLUS=$ORACLE_HOME/bin/sqlplus

# unsetting SQLPATH prevents login.sql from running
# this may or may not be what you want.
# I unset it because there are a number of SQL statements
# in my login.sql that will fail if not connected to a database
# this means that a full path must be used to reference any SQL file.

unset SQLPATH

$SQLPLUS -S /nolog <<-EOF
-- set feedback off and heading off to avoid output from setup set heading off feedback off
connect $USERNAME/"$PASSWORD"_at_$DATABASE alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss'; -- set heading and feedback on for reporting set heading on feedback on
select sysdate, name from v\$database;
select sysdate, instance_name from v\$instance; EOF

• cut here ============

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 06 2009 - 11:18:42 CDT