# TSTsql.def: Tests SQL Settings
# $Id: TSTsql.def,v 1.13 2009/01/13 16:47:47 ldens Exp $
# ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/TSTsql.def,v 1.13 2009/01/13 16:47:47 ldens Exp $
#
# Change History
# 20090113  LDE  Change the copyright notice.

=head1 NAME

TSTsql - Tests SQL*Plus Settings and Gets some Database Configuration
Information

=head1 DESCRIPTION

=cut

echo tput('bold'),'Processing SQL test module ...',tput('off')

echo 'Setting logins ...'

# Get the current login
var $log = setSqlLogin()

# Define the SQL logins
loop $key (grepSetting('^SQL_PASSWORD_'))
{echo "  Set the password for '",substr($key,13),"'"
 call setSqlLogin(substr($key,13),unpack('u',getSetting($key)))
}
loop $key (grepSetting('^SQL_PASS_\d+$'))
{var ($usr,$sid) = split('\@',getSetting(replace($key,'_PASS_','_USER_')),2)
 echo "  Set the password for '",$usr,cond($sid,concat('@',$sid)),"'"
 call setSqlLogin($usr,unpack('u',getSetting($key)),$sid)
}

# Restore the current login
call setSqlLogin($log)

=pod

This tool loads the passwords that are encoded in the setup file and collects
the following information:

=over 4

=item o Current user

=item o Current SQL*Plus settings

=item o Content of the C<v$version> table

=item o Content of the C<v$database> table

=item o Content of the C<v$pwfile_users> table

=item o Value of the C<remote_login_passwordfile> parameter

=item o Records of the C<dba_triggers> table with a null C<table_name>

=back

=cut

call setSqlTrace(setTrace())
set $sql
{PROMPT Current User:
"PROMPT =============
"SELECT USER
" FROM dual;
"
"PROMPT
"PROMPT Current SQL*Plus Settings:
"PROMPT ==========================
"SHOW all
"
"COLUMN value FORMAT a20
"COLUMN owner FORMAT a10
"COLUMN trigger_name FORMAT a20
"COLUMN status FORMAT a10
"COLUMN triggering_event FORMAT a16
"COLUMN trigger_type FORMAT a16
"SET wrap on
"SET heading on
"SET linesize 80
"
"PROMPT
"PROMPT v$version
"PROMPT =========
"SELECT *
" FROM v$version;
"
"PROMPT
"PROMPT v$database
"PROMPT ==========
"SELECT *
" FROM v$database;
"
"PROMPT
"PROMPT v$pwfile_users
"PROMPT ==============
"SELECT *
" FROM v$pwfile_users;
"
"PROMPT
"PROMPT remote_login_passwordfile Parameter
"PROMPT ===================================
"SELECT value
" FROM v$parameter
" WHERE name = 'remote_login_passwordfile';
"
"PROMPT
"PROMPT dba_triggers (null table_name)
"PROMPT ==============================
"SELECT owner,trigger_name,triggering_event,trigger_type,status
" FROM dba_triggers
" WHERE table_name IS NULL
" ORDER BY 1,2;
}
if loadSql($sql)
{loop $lin(grepLastSql('.*'))
 {echo $lin
 }
}
if getSqlMessage()
 echo last

=head1 COPYRIGHT NOTICE

Copyright (c) 2002-2009, Oracle. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle
Corporation and/or its affiliates. Other names may be trademarks of their
respective owners.

=cut