Date: Tue, 6 Oct 2009 09:18:42 -0700
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" ?
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
any other sqlplus or oraenv that may (possibly maliciously) be in the PATH.
- cut here ============ :
USERNAME=scott PASSWORD=tigress DATABASE=dv11
# setup local oracle environment
# 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.
$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 ============