ACCEPT and PROMPT commands

Usage of ACCEPT and PROMPT commands in SQL script.

SQL> PROMPT 'Please enter your name:'

SQL> ACCEPT name CHAR FORMAT a20 

Here is a script which prompts for username and accepts it.

It also creates user with that username and a table

 UNDEF username

ACCEPT username PROMPT 'Enter username:'

--Create user
CREATE USER &username IDENTIFIED BY passwd;
GRANT CREATE SESSION TO &username;
GRANT CREATE TABLE TO &username;
ALTER USER &username QUOTA 5M ON users;

-- Create table
CREATE TABLE &username..test AS SELECT * FROM dual;

SQL> @create.sql
Enter username:harish
old   1: CREATE USER &username IDENTIFIED BY passwd
new   1: CREATE USER harish IDENTIFIED BY passwd

User created.

old   1: GRANT CREATE SESSION TO &username
new   1: GRANT CREATE SESSION TO harish

Grant succeeded.

old   1: GRANT CREATE TABLE TO &username
new   1: GRANT CREATE TABLE TO harish

Grant succeeded.

old   1: ALTER USER &username QUOTA 5M ON users
new   1: ALTER USER harish QUOTA 5M ON users

User altered.

old   1: CREATE TABLE &username..test AS SELECT * FROM dual
new   1: CREATE TABLE harish.test AS SELECT * FROM dual

Table created.

Please not that I have used two dots in create table script after schema name.