Using Shell

windows bat %1~%9
linux bash $1~$9
linux perl

passing parameter

sqlplus
bind parameter
substitute parameter

set autocommit off;
set serveroutput on;
set echo off;
set feedback off;
set lines 120;
set colsep '|';
set verify off;
whenever sqlerror exit sql.sqlcode;
spool exec.log;
prompt ********************************************************************************
prompt *                  title                      *
prompt * Date      author   created                                         *
prompt ********************************************************************************
prompt

accept exec_flag char prompt 'confirm to execute?(yes/no)';
begin
    if '&exec_flag' is null or lower('&exec_flag') = 'yes' then
        null;
    else
        raise_application_error( -20001, 'canceled by user');    
    end if;
end;
/
COLUMN STATE_ID NEW_VALUE STATE_ID NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') STATE_ID FROM DUAL;

EXPLAIN PLAN SET STATEMENT_ID = '&STATE_ID' FOR
SELECT    /**/
        *
FROM    SCOTT.EMP
/
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
/
DELETE FROM    PLAN_TABLE
WHERE    STATEMENT_ID = '&STATE_ID'
/
COMMIT
/
EXIT
/
param.sql
select '&1' param1 from dual
/
select '&2' param2 from dual
/
exit
/

sqlplus scott/tiger @param.sql param1 param2
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License