SQL Plus Input Ouput

Input to sqlplus

set verify off
set serveroutput on
accept new_dept_name char prompt 'Enter new department name '
declare
dept_name varchar2(100);
begin
dept_name := &new_dept_name; -- assign the value taken from user
end;
/

SET AUTOPRINT ON
variable maximum_department_number number
declare
max_dept_num number;
begin
select max(deptno) into max_dept_num from dept;
:maximum_department_number := max_dept_num; -- pl/sql value assigned to sqlplus variable
end;
/

set autoprint print the sqlplus variable at the end of the execution
to explicitly print

print maximum_department_number

Useful SQL Plus formats

set echo off -- suppress showing sql in result set
set feedback off -- eliminate row count message
set linesize 100 -- make line long enough to hold data
set pagesize 0 -- suppress headings and page breaks
set sqlprompt '' -- eliminate SQL*Plus prompt from output
set trimspool on -- eliminate trailing blanks

Restrict / as sysdba

add

SQLNET.AUTHENTICATION_SERVICES=(NONE)

to sqlnet.ora

Changing SQL prompt

Add the following to end of $ORACLE_HOME/sqlplus/admin/glogin.sql

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL>"

below shown code can also be added to get the same output and even run custom query at sqlplus login. As they rely on database tables cannot be used when database is down.

define _editor=vi
set termout off
column prompter new_value m_prompt

select sys_context('USERENV','CURRENT_USER') '@' sys_context('USERENV','DB_NAME')' SQL>' prompter from dual;

set sqlprompt '&m_prompt'
undefine m_prompt
commit;

set termout on