Prompt & Accept in sqlplus

With prompt & accept, it is possible to interactively set a value for a user variable in SQL*Plus.

Sample 1

set echo off;
set serveroutput on size 999999;

Prompt ptian test script

prompt Please enter employee number?:;
accept p_emp_no;

declare
val varchar2(30);

begin
	select ENAME into val
	from emp 
	where EMPNO = &p_emp_no
	and rownum = 1;

  dbms_output.put_line('Employee is ' || val);

exception
  when others then
    dbms_output.put_line('Encounter a error,ORA ERROR: '||SQLCODE||' '||substr(SQLERRM,400) );  
    --dbms_output.put_line('Encounter a error!!!');  
end;
/

SQL> @prompt.sql
ptian test script
Please enter employee number?:
7839
原值    7:      where EMPNO = &p_emp_no
新值    7:      where EMPNO = 7839
Employee is KING

PL/SQL 过程已成功完成。

SQL>

Sample 2

accept filesave prompt "Save File As: "
accept code prompt "Enter Carrier Code: "
accept startdate prompt "Enter Start Date: "
accept enddate prompt "Enter End Date: "
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL C:\reports\'&filesave'.csv;
SELECT /* The purpose of this query is to ...... Always good policy to annotate queries */
carrier_id, carrier_name, calls, minutes, turnover FROM
carriers_data
WHERE
trunc(adj_start_time) >= '&startdate'
AND trunc(adj_start_time) < '&enddate'
AND carrier_id = '&code' ;

ACCEPT Syntax

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12005.htm

PROMPT Syntax

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12032.htm#sthref2369