How to script

A real shell Unix calling Oracle
#!/bin/ksh
sqlplus "/ as sysdba" <<ENDOFSQL
  spool ./start_work.log
  whenever sqlerror exit sql.sqlcode;
  variable result number
  declare
    s varchar2(20);
  begin
    select in_progress into s from etl.load_status;
    if(s='Y') then
      raise_application_error(-20001, 'Process in progress');
    end if;
    update etl.load_status set in_progress='Y', process_date=sysdate;
    commit;
    :result := 1;
  end;
  /
  exit 1;
ENDOFSQL
echo $?

Using SqlPlus
SQL>variable mgr varchar2(5);
SQL>execute :mgr:='A';
SQL>select * from aldo.aziende where nomefiliale = :mgr;

Template

--FILENAME                 :
--Author                   :
--Date                     :
--Application              :
--Run this script with user:
--Notes                    :

--create views with FORCE option

whenever oserror exit SQL.OSCODE rollback
whenever sqlerror exit SQL.SQLCODE rollback
spool NOMEFILE.log
Set autocommit off timing on echo on feedback on

--your sql
--commit

spool off
exit

unix>vi scriptSQL.sql

spool ./scriptSQL.log
select 'operation', to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
execute DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'ALDO, nohup', action_name => 'Split elenco_telefonico_t');
--place here your sql
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
spool off
exit;

unix>nohup sqlplus "/ as sysdba" @scriptSQL.sql &

Template using progress indicator, only for begin - end blocks

spool ./scriptSQL.log select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual; declare --Progress variables rindex binary_integer; soFar binary_integer := 0; slNo binary_integer; total number; operation_name varchar2(200); m_units varchar2(100) := 'Operations'; --Application variables procedure prog is begin soFar := soFar + 1; operation_name := 'Operation ' || soFar; dbms_application_info.set_session_longops(rindex=>rindex, slno=>slNo, op_name=>operation_name, context=>0, sofar=>sofar, totalwork=>total, target_desc=>total ||' operations to do', units=>m_units); end; begin DBMS_APPLICATION_INFO.SET_MODULE(module_name => '&&3', action_name => '&&1'); total := &&2; rindex := dbms_application_info.set_session_longops_nohint; prog; --Place your code here dbms_stats.gather_table_stats(ownname=> 'VPO', tabname=> 'GARANZIA_S', partname=> NULL , estimate_percent=> 49, cascade=>true ); prog; --Place your code here dbms_stats.gather_table_stats(ownname=> 'VPO', tabname=> 'GARANZIA_S', partname=> NULL , estimate_percent=> 49, cascade=>true ); prog; --Place your code here dbms_stats.gather_table_stats(ownname=> 'VPO', tabname=> 'GARANZIA_S', partname=> NULL , estimate_percent=> 49, cascade=>true ); end; / select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual; spool off exit;

unix> nohup sqlplus "/ as sysdba" @scriptSQL.sql 'Analyzing' 3 'ALDO' &

Another way to launch a script
echo "execute EODPHASE.EOD.insertAndLaunch(mBankId=> 'NLH', mVerifyDate=>sysdate);" | sqlplus "eodphase/eodphase@mta-a-st-grid"

Allow to specify logfile using a parameter
spool check_trand_DB_&&1..log