How to modify the prompt in Oracle

How to modify the prompt in Oracle

When sqlplus is started, it reads the glogin. SQL script and writes it in the $ Oracle_HOME/sqlplus/admin directory.

Here is an example.

[Php]

--

-- Copyright (c) Oracle Corporation 1988,200 0. All Rights Reserved.

--

-- NAME

-- Glogin. SQL

--

-- DESCRIPTION

-- SQL * Plus global login startup file.

--

-- Add any sqlplus commands here that are to be executed when a user

-- Starts SQL * Plus on your system

--

-- USAGE

-- This script is automatically run when SQL * Plus starts

--

-- For backward compatibility

Set serveroutput on size 1000000

SET TRIMSPOOL ON

Sets LONG 5000

Set linesize 100

Set pagesize 9999

Set sqlpluscompatibility 8.1.7

COLUMN global_name new_value gname

SET TERMOUT OFF

Select lower (user) | '@' | decode (global_name, 'oracle8. WORLD ', '8. 0', 'ora8i. WORLD ', '8i', global_name) global_name from global_name;

Set sqlprompt '& gname>'

SET TERMOUT ON

-- Used by Trusted Oracle

Column rowlabel format A15

-- Used for the show errors command

Column line/col format A8

Column error format A65 WORD_WRAPPED

-- Used for the show sga command

COLUMN name_col_plus_show_sga FORMAT a24

-- Defaults for SHOW PARAMETERS

COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for set autotrace explain report

COLUMN id_plus_exp FORMAT 990 HEADING I

COLUMN parent_id_plus_exp FORMAT 990 HEADING p

COLUMN plan_plus_exp FORMAT a80

COLUMN object_node_plus_exp FORMAT a8

COLUMN other_tag_plus_exp FORMAT a29

COLUMN other_plus_exp FORMAT a44

[/Php]

D: \ Temp>; sqlplus

SQL * Plus: Release 9.2.0.4.0-Production on Tue Dec 16 12:07:06 2003

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger@ora921.hl3

Connected:

Oracle9i Enterprise Edition Release 9.2.0.4.0-Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0-Production

Welcome to Seraphim's SQL * PLUS!

You are connected to ORA921.IAGENT as SCOTT

SQL>;

To achieve this, add the following lines to glogin. SQL:

Set heading off

Prompt Welcome to Seraphim's SQL * PLUS!

Select 'you are connected to '| global_name | 'as' | user from global_name;

Set serveroutput on

Set heading on

Or

COLUMN host_name new_value hname

SET TERMOUT OFF

Select SYS_CONTEXT ('userenv', 'host') host_name from dual;

Set sqlprompt '& hname> ;'

SET TERMOUT ON