Re: sqlplus
- From: Jared Still <jkstill@xxxxxxxxx>
- To: joan.hsieh@xxxxxxxxx
- Date: Tue, 6 Oct 2009 09:18:42 -0700
On Tue, Oct 6, 2009 at 8:20 AM, Joan Hsieh <joan.hsieh@xxxxxxxxx> 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"@$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 ============
Other related posts:
