title: Generating procedure logfile and inserting date time
body: <p>Friends...</p>
  
  <p>DB: Oracle11gR2</p>
  
  <p>OS: Linux</p>
  
  <p>I have created package with couple of procedure, procedure executes alter table move... , index rebuild command on database.</p>
  
  <p>I'm doing below</p>
  
  <ol>
  <li>Run ksh shell script -> execute procedure</li>
  <li>Procedure runs alter table, rebuild index commands on database</li>
  <li>Procedure completes</li>
  <li>Shell script ends.</li>
  </ol>
  
  <p>I can generate logfile for the shell script but whatever gets executed by procedure doesn't get recorded inside shell script logfile. I understood since db session created by procedure it won't record anything in shell logfile.</p>
  
  <p>So how can I</p>
  
  <ol>
  <li>Record everything in logfile which is executed by both procedures in the same package?</li>
  <li>Also trying to put current datetime within procedure dmbs_out.put_line command?</li>
  <li>Is it possible to run both procedure after connecting database once instead of 2 time connecting database and executing procedure?</li>
  </ol>
  
  <p>There might be table/table partition move syntax error but I'm only trying to trap when table move started and when finished with datetime so to identify total time taken.</p>
  
  <pre><code>*** ksh script
  #!/bin/ksh
  ...
  ...
  ...
  $LOG_FILE = move_tbs.log
  
  echo -e "set serveroutput on\n exec move_tbs.moveTable;"|$ORACLE_HOME/bin/sqlplus/@db_alias | head -l
  
  echo -e "set serveroutput on\n exec move_tbs.moveTablePart;"|$ORACLE_HOME/bin/sqlplus/@db_alias | head -l
  </code></pre>
  
  <p>DB Package / Procedure </p>
  
  <pre><code>*** Procedure
  create or replace package move_all
  
  procedure moveTable
  
  dbms_output.put_line("CURRENT TIME" 'alter table '|| owner || '.' || table_name || 'move');
  
  Execute immediate 'alter table '|| owner || '.' || table_name || 'move';
  dbms_output.put_line("COMPLETED TIME" : CURRENT_TIME);
  
  end moveTable;
  
  -------------------------------------------
  procedure moveTablePart
  
  dbms_output.put_line("CURRENT TIME" 'alter table '|| owner || '.' || table_name || 'move');
  
  Execute immediate 'alter table '|| owner || '.' || table_name || 'move partition';
  dbms_output.put_line("COMPLETED TIME" : CURRENT_TIME);
  
  end moveTablePart;
  end move_all
  /
  </code></pre>