. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/ACE2_History.sql

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

spool /edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY/deletelog.txt

truncate table ACE2_DELETED_LIST;

exit;

col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
/
spool &filename
<do work>
spool off

col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
spool &filename
begin
dbms_output.put_line('hi');
end;

spool off

. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/ACE2_History.sql

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'


truncate table ACE2_DELETED_LIST;

exit;

truncate table ACE2_DELETED_LIST;

PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;




PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'


Begin

spool &filename

truncate table ACE2_DELETED_LIST;


spool off

exit;

PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;

whenever sqlerror exit 1
set feedback off

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

spool &filename

truncate table ACE2_DELETED_LIST;

prompt Success!

spool off

exit;

. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/run_delete_list.sql

PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;




PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

set feedback off


#spool &filename



truncate table ACE2_DELETED_LIST;



prompt 'success!!'


spool off

cat ACE2_DELETED_LIST_20080530042741.log
truncate table ACE2_DELETED_LIST
               *
ERROR at line 1:
ORA-00942: table or view does not exist


'success!!'

sh deletelist.sh

SQL*Plus: Release 10.2.0.2.0 - Production on Fri May 30 05:22:36 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

'**************************************************'
'Generating Log File
'**************************************************'

FILENAME
------------------------------------
ACE2_DELETED_LIST_20080530052236.log

'**************************************************'
'cleaning the deleted lists from ACE2_DELETED_LIST '
'**************************************************'
truncate table ACE2_DELETED_LIST
               *
ERROR at line 1:
ORA-00942: table or view does not exist

whenever sqlerror exit 1
spool my_main_log.log
...
...
spool end_was_reached_ok.flg

#!/usr/bin/ksh
user=scott
pass=tiger
db=devdb

t_name=xyz
log=my_log`date '+%Y%m%di'`.log

##sqlplus -s /nolog <<EOF >$log
sqlplus -s /nolog <<EOF >/dev/null
  whenever sqlerror exit 1
  connect $user/$pass@$db
  truncate table $t_name;
EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
  echo "Successfully truncated $t_name"
else
  echo "ERROR. Failed to truncate $t_name"
fi

exit $rv

. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%di'`.log
touch $LOG_FILE
sqlplus -s  <<EOF >$LOG_FILE
#sqlplus -s /nolog <<EOF >/dev/null
whenever sqlerror exit 1
connect  $leninlogin

#sqlplus sven_lenin_prod/sven_lenin_prod@edw2

truncate table ACE2_DELETED_LIST;

EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
echo "Successfully Truncated"
else
echo "ERROR. Failed to complete the process"
fi

exit $rv

sh  new_createwithlog.sh
Retval is 1
ERROR. Failed to complete the process
/edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY>ls
ACE2_DELETED_LIST_20080531i.log  Listoutput_History.sh            new_createwithlog.sh
ACE2_History.sql                 Listoutput_History_log.sh        run_delete_list.sql
ACE2_History_log.sql             deletelist.sh
/edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY>cat ACE2_DELETED_LIST_20080531i.log
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%d'`.log
touch $LOG_FILE
sqlplus -s /nolog<<EOF >$LOG_FILE
#sqlplus -s /nolog <<EOF >/dev/null
whenever sqlerror exit 1
connect  $leninlogin



set feedback off

truncate table ACE2_DELETED_LIST;

prompt 'Successfully Truncated'

EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
echo "Successfully Truncated"
else
echo "ERROR. Failed to complete the process"
fi

cat ACE2_DELETED_LIST_20080531.log
SP2-0734: unknown command beginning "sqlplus -s..." - rest of line ignored.
'Successfully Truncated'

. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%d'`.log
touch $LOG_FILE
sqlplus -s /nolog<<EOF >$LOG_FILE
whenever sqlerror exit 1
connect  $leninlogin

set echo off
prompt Trying to truncate ACE2_DELETED_LIST now...
truncate table ACE2_DELETED_LIST;

EOF
rv=$?

#echo Retval is $rv

if [ $rv -eq 0 ]; then
  echo "Successfully Truncated"
else
  echo "ERROR. Failed to complete the process"
  cat $LOG_FILE
fi

# exit with return code, so that your scheduler knows
# if this script failed (1-255) or succeeded (0)
exit $rv