Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Lisa.

Asked: July 18, 2000 - 9:04 pm UTC

Last updated: March 28, 2018 - 1:42 am UTC

Version: 8.0.5.0.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I notice in the Oracle Magazine that you have your SQL prompt displaying "user@instance". Please tell me how you accomplish this. I can get it to display the username OR the instance name but not both. Thanks!

-Lisa

and we said...

Sure, in my login.sql I have the following query:


column global_name new_value gname
set termout off
select lower(user) || '@' ||
decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
'8i', 'ORA8IDEV.WORLD', 'dev8i', global_name ) global_name from global_name;
set termout on
set sqlprompt '&gname> '


I just concatenate together whatever I want -- and that's my prompt.

If you want the SID instead of the global_name, just use

select instance from v$thread

instead...

Rating

  (71 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

login.sql

Sudhakar, March 21, 2001 - 11:36 pm UTC

Hi Tom,
just came to know this place for oracle doubts and clarifications, a few days ago. its been very useful.

The login.sql way for sqlprompt was obvious but i woul like to kno can we have any trigger which will give me sqlprompt when i connect to some other user or database. Because login.sql will get accesed when we login to sqlplus first time.

For instance

first login
system/manager@test
system@test>
'well this is fine'
system@test> connect scott/tiger@test1
system@test>
' the prompt doesnt change to scott@test1'

Hope u understood what i am trying to say

Thanks
Sudhakar



sql prompt

mo, August 20, 2002 - 9:17 pm UTC

Tom:

1. Can you change the prompt if you connect internally to a second instance? do you have to write ONCOnnect trigger?

thanks

Tom Kyte
August 20, 2002 - 9:40 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:928829365838 <code>

a trigger CANNOT affect a sqlplus (generic program) prompt.

2 prompts instead of One SQL> Prompt

Vikas Khanna, September 06, 2002 - 7:41 am UTC

Hi Tom,

I have been using this for quite some time, but everytime I work with it, found that instead of one sql prompt we get 2 sql prompts if the glogin.sql or login.sql has that text written for changing that SQL prompt.

Instead of regular SQL> prompt just one which we get by default,I always get something like this :

SCOTT@Oracle8.1.7>
SCOTT@Oracle8.1.7>

Where I am wrong in my script? Please Clarify my doubt.
Thanks
 

Tom Kyte
September 06, 2002 - 7:46 am UTC

Well, my super special eye goggles that let me see long distances (all of the way to your monitor) are broken right now. Hence, I cannot comment on what might be wrong with your script.

I can only say that if you cut and paste what I have AS IS and use it to start - it shouldn't happen (never has for me)

that and it is nothing more then a very tiny, minor "annoyance" at best....

using CASE instead of DECODE

Paul Druker, September 06, 2002 - 9:21 am UTC

I've modified Tom's script a little bit -- to remove the second part (.WORLD) of the global_name value. This is the modified select statement:

select lower(user) || '@' ||
case when instr(global_name,'.')>1
then substr(global_name,1,instr(global_name,'.')-1)
else global_name end
global_name from global_name;


Customising SQL prompt !

Rama, September 06, 2002 - 11:32 am UTC

Tom,

I have Oracle 9 on Win 2000 on my personal comp. Now, I searched for login.sql, instead I found glogin.sql and there's no connect.sql there. Is login.sql replaced with glogin.sql in Oracle 9. And what about connect.sql ?? Your response would be highly appreciated.

Tom Kyte
September 06, 2002 - 1:34 pm UTC

login.sql is a "personal" login.sql. glogin.sql is a GLOBAL login.sql. They both have existed for as long as I can remember.

connect.sql is something I wrote that just does:

connect &1
@login




A Reader, September 16, 2002 - 7:21 am UTC

Tom,

If we do the changes in login.sql or glogin.sql to customise SQL Prompt but the effects of it do appear in the SQL Worksheet when we do use it.

How could we eliminate that script not to run when we are executing SQL Worksheet and to run when user is connecting to database via SQL*PLUS.

I am working with two Oracle Homes 8.0.5 and 8.1.7. In 8.0.5 for Sql Worksheet we get the results of the execute via fetch in one line / record.

But in 8.1.7 even in SQL Worksheet it does behave like SQL*PLUS as the results to appear are distorted and one record can consume many lines depending upon the length of the record.

Please clarify.

Tom Kyte
September 16, 2002 - 9:27 am UTC

I never use sqlworksheet. Suggest you refer to the documentation for sqlworksheet.

A reader, January 15, 2003 - 6:58 pm UTC

Hi Tom,

I am new to unix.
How can I customize sql prompt on UNIX.

Thanks,

Tom Kyte
January 16, 2003 - 8:11 am UTC

depends on your shell. In the csh I use

set prompt = "\
`/bin/date`\
($USER@`/usr/ucb/hostname`) `pwd`\
> "




Other version of connect.sql

Chuck Jolley, January 16, 2003 - 9:46 am UTC

The prompt part of my connect.sql looks like this:

connect &1
set termout off
col prom new_value prom
select lower(sys_context('USERENV','SESSION_USER'))||
'@'||
lower(sys_context('USERENV','DB_NAME'))||
'.'||
lower(machine) prom
from SYS.V_$SESSION where sid = 1;
set sqlprompt "&prom> "
set termout on

The way things are set up here, this works fine.
But, are there any pitfalls in getting the server name the way I do?
For instance when we go from 8.1.7 to 9i?

thanks!


Tom Kyte
January 16, 2003 - 10:05 am UTC

it'll work but I would use DUAL instead of v$session personally.

I didn't aks clearly

Chuck Jolley, January 16, 2003 - 10:15 am UTC

I guess my real question was:
Is there a better way to get the name of the DB server than

select machine
from SYS.V_$SESSION where sid = 1;

and will sid = 1 always give the correct session?

Thanks,
Chuck


Tom Kyte
January 16, 2003 - 10:43 am UTC

select host_name from v$instance

Next lines

Wit, February 07, 2003 - 10:03 am UTC

Thanks to all.
Is there a way to form the prompt next to first one (with line numbers)? It's not handy to use mouse for copying. Thanks.

Prompt get stuck

Lucky lucky, March 31, 2003 - 4:20 pm UTC

Tom,

I have your script for glogin.sql and login.sql for customizing my sql prompt. This script makes use of v$instance (or v$thread) to read the instance name, so I have a prompt like:

myname@myinstance>

However, when I log in and the instance is not up, then the prompt gets "stuck" and I have to ctl-c to go on -- since the v$ views are not accessible in shutdown mode. That is when we usually get the "connected to an idle instance" message.

I saw in a recent posting </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8913476988509 <code>,you wrote:

idle>

Is there any workaround so my prompt doesn't get stuck at this point ?

Tom Kyte
March 31, 2003 - 4:55 pm UTC

define gname=idle

at the top -- after the set termout off, before the query.

Prompt Does Not Change

Hien Luong, April 01, 2003 - 5:22 am UTC

Hi Tom,

I have noticed that in 8i(8.15-8.17) the prompt get changed correctly when you first started sqlplus but once you have been connected to a database and then switch to another the prompt remains the same.

For example,

$sqlplus scott/tiger@TEST
scott@TEST >

It works fine so far ..
then in sqlplus you switch to another database, say

scott@TEST > connect scott/tiger@PROD

the you would get

connected.
scott@TEST >

Note that the prompt has not been changed at all.

Can you please tell us how to change the prompt dynamically.

Thanks
Hien Luong




Tom Kyte
April 01, 2003 - 7:36 am UTC

see the first two comments at the top.

sqlprompt that works

Hien Luong, April 01, 2003 - 8:55 am UTC

Tom,

Yeah, a little trick that works beautifully. Hahhah!

Thanks
Hien Luong

excellent...

James Blanding, January 20, 2004 - 11:20 am UTC

> define gname=idle

> at the top -- after the set termout off, before the query.

very cool. this helps prevent confusion when sql*plus fails to connect to the instance you specify. my prompt variation looks like:

instance_name(host_name) >
or
NOT CONNECTED >


column sqlprompt_col new_value sqlprompt_value
set termout off
define sqlprompt_value='NOT CONNECTED'
select instance_name || '(' || host_name || ')'
as sqlprompt_col
from v$instance;
set termout on
set sqlprompt '&sqlprompt_value > '

Check out 10g!

Mark J. Bobak, March 04, 2004 - 1:47 pm UTC

oracle@dvlpq1 [/home/admin/oracle]. oraenv
ORACLE_SID = [ora10g] ?
oracle@dvlpq1 [/home/admin/oracle]sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Mar 4 13:45:24 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SYS@ora10g>connect adds@dvl1
Enter password:
Connected.
ADDS@dvl1>connect adds@pep1
Enter password:
Connected.
ADDS@pep1>connect system@prd1
Enter password:
Connected.
SYSTEM@prd1>show sqlprompt
sqlprompt "_user"@"_connect_identifier>"
SYSTEM@prd1>

Cool, eh? Seems to me, this should have been a feature
of SQL*Plus years ago!

Tom Kyte
March 04, 2004 - 2:34 pm UTC

you can even "spool filename append" in 10g ;)

Spool append?

Mark J. Bobak, March 05, 2004 - 2:34 am UTC

Excellent, I did not know that. Now, the real bummer is
that we won't be on 10g for any substantial systems for
at least 2 years! ;-)

Removing feedback info when connecting within SQL*Plus

ht, March 17, 2004 - 6:05 pm UTC

Tom,
I'm using a shell script to connect to an instance and setting a shell env variable.

I used to connect with "sqlplus -s user/password" but am now connecting within a sql script to prevent command line password sniffing.

I can use awk to get rid of the "Connected." string but is there a SQL*Plus setting that will allow me to not display the connection confirmation?

conn scott/tiger;
Connected.

TIA


Tom Kyte
March 17, 2004 - 6:34 pm UTC

if you are using a script, set termout off will surpress the display.

set termout off doesn't suppress the "Connected." display within a script.

ht, March 17, 2004 - 6:42 pm UTC

Tom,
It doesn't seem that "set termout off" suppresses the display when connecting within the shell script. I'm using 2 connections (the first one uses scott/tiger but the second one is the id/password that I don't want snooped on the command line).

Besides using awk to remove "Connected.", is there another solution?


$ versiontest1.sh > versiontest1.log
$ cat versiontest1.sh
#!/bin/ksh

function check_db_version {
DBVERSION=$(
sqlplus -s scott/tiger <<- eof8
set linesize 300;
set echo on;
set heading off;
set feedback off;
set verify off;
set termout off;
connect user2/pass2
select substr(&_O_RELEASE,1,1) from dual;
exit;
eof1
)
}
check_db_version
echo "xyz:$DBVERSION:123"
exit
if [ $DBVERSION = "7" ];
then
echo v7 db
else
echo not a v7 db
fi
# ---------------------------------------------------------------- #


$ cat versiontest1.log
xyz:Connected.

9:123
$


Tom Kyte
March 17, 2004 - 6:49 pm UTC

it works if SQLPLUS is running a script.

so, either put the sets and all in a script like /tmp/$$.sql and run that or

select 'LOOK_FOR_ME' || substr( .... ) from dual;


and "grep" that in the sqlplus command... I'd offer to script it -- but your script doesn't see to run "as is" on linux? (and I don't do ksh...)

login.sql

robert james hanrahan, April 14, 2004 - 7:19 pm UTC

hi Tom,

first of all I wanted to say that your site is very
helpful for the many DBAs across the world :)

anyway,
I changed the login.sql according to the setting about the
global_name and it works fine until I founded another
instance that had the same global_name so I work it around
like this:

column INSTANCE_NAME new_value gname
set termout off
select lower(user) || '@' ||
UPPER(decode(INSTANCE_NAME, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD', '8i', 'ORA8IDEV.WORLD', 'dev8i', INSTANCE_NAME ))
INSTANCE_NAME
from v$instance;
set termout on
set sqlprompt '&gname> '

this way I get the instance_name instead of global_name.
Maybe this Devel DB was made from the Production one.

cheers

rjh


Tom Kyte
April 15, 2004 - 8:16 am UTC

instance_name = ORACLE_SID = same issue in general as global name :)


use what works for you of course, I keep my global names globally unique (and the same as the sid in fact)...

Using decode to dynamically print version info?

ht, April 19, 2004 - 8:20 pm UTC

Hi Tom,
How can we convert the output of "902000300" to "9.2.0.3" dynamically? In other words, I can use "decode" to reformat the output of &_O_RELEASE but I would like to dynamically print "." instead of "0" so "902000400" wouldn't need a decode to print "9.2.0.4".

Thanks,
ht

>select &_O_RELEASE from dual;
old 1: select &_O_RELEASE from dual
new 1: select 902000300 from dual

902000300
----------
902000300

1 row selected.

select decode(902000300,'902000300','9.2.0.3') from dual

DECODE(
-------
9.2.0.3



Tom Kyte
April 20, 2004 - 8:18 am UTC

ops$tkyte@ORA9IR2> select trunc( (&O/100000000)) || '.' ||
  2         trunc( mod((&O/1000000),100)) || '.' ||
  3         trunc( mod((&O/10000),100)) || '.' ||
  4         trunc( mod((&O/100),100))
  5    from dual;
old   1: select trunc( (&O/100000000)) || '.' ||
new   1: select trunc( (902000300/100000000)) || '.' ||
old   2:        trunc( mod((&O/1000000),100)) || '.' ||
new   2:        trunc( mod((902000300/1000000),100)) || '.' ||
old   3:        trunc( mod((&O/10000),100)) || '.' ||
new   3:        trunc( mod((902000300/10000),100)) || '.' ||
old   4:        trunc( mod((&O/100),100))
new   4:        trunc( mod((902000300/100),100))
 
TRUNC((
-------
9.2.0.3
 

A reader, April 20, 2004 - 9:55 am UTC


A reader, May 14, 2004 - 12:38 pm UTC


Oracle10g

Laurent Schneider, June 29, 2004 - 5:36 am UTC

Hi,
In Oracle10g, you can do :

set sqlp "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

Which is simple and dynamic.

SQL> set sqlp "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
SYS@LSC62 AS SYSDBA> conn scott/tiger
SCOTT@LSC62 > disc
@ > quit

However, the prompt buffer is limited to 50 characters, so if you have "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE" in your prompt, you have only 12 character left.

Please note that _USER is not equivalent to &_USER because &_USER is executed only during the "set sqlp" and _USER is executed for each prompt. _USER may use more space then &_USER.

Ok, I give my colorfull login.sql for unix terminals in 10g, largely inspired from this thread !

Regards
Laurent

col "_myprompt" new_value myprompt
set termout off
select
    chr(27)||'[31m'||
    substr('&_USER@&_CONNECT_IDENTIFIER'||rtrim(' &_PRIVILEGE')||'/'||
        trunc(&_O_RELEASE/100000000) || '.' ||
        mod(trunc(&_O_RELEASE/1000000),100) || '.' ||
        mod(trunc(&_O_RELEASE/10000),100) || '.' ||
        mod(trunc(&_O_RELEASE/100),100),
        1, 36)
    ||chr(27)||'[m'||chr(10)||'SQL> ' "_myprompt"
from dual;
set termout on
set sqlp "&myprompt"

 

take care of those login.sql

Laurent Schneider, June 29, 2004 - 7:47 am UTC

Hi,
I have meet some side effects ...

If you use "set termout off" and then retrieve a &variable with a select and then use that variable in your prompt, all that in your login.sql script, then Oracle Grid Control will no longer be able to discover your nodes :-(

To survive, you can do "export SQLPATH=invalid" before starting the agent/deamon.

Regards
Laurent



sqlplus /nolog

Laurent Schneider, June 30, 2004 - 7:59 am UTC

Hi Tom,
Did you try "sqlplus /nolog"? Don't you get a 

Enter value for gname:

To bypass this problem, I give a default value to my prompt before the select

def gname="SQL> "
select ...

Regards
Laurent


 

Tom Kyte
June 30, 2004 - 10:20 am UTC

yes, i've added that over time.

save last command

Laurent Schneider, July 21, 2004 - 9:30 am UTC

Hi,
If you have a 10g client, then your login will be read any time you log in (with connect). In order to keep the sql buffer, I did add

def gname=""
column global_name new_value gname
set termout off
save /tmp/saved
select lower(user) || '@' ||
decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
'8i', 'ORA8IDEV.WORLD', 'dev8i', global_name ) global_name from global_name;
del 1 last
get /tmp/saved
host rm 2>/dev/null /tmp/saved.sql
set termout on
set sqlprompt '&gname> '

HTH
Laurent

add a point after the get

Laurent Schneider, July 21, 2004 - 10:45 am UTC

get /tmp/saved
.


Warning !

Laurent Schneider, July 22, 2004 - 8:28 am UTC

This kind of "set termout off" + Select generating discrete ORA- errors may have side effects.

Imagine someone run the following script on a mounted database on his 10g sqlplus

whenever sqlerror exit 1
connect sys/pw@db as sysdba
alter database open;


His script will fail, because the connect will generate an error, but it will be very difficult for him to identify the "hidden" select in glogin.sql.


Have a great day!
Laurent




One more problem

Arun Gupta, July 23, 2004 - 11:44 am UTC

Tom,
The SQL prompt customization works perfect. In some of our databases, some user accounts do not have access to v$instance . Such accounts always show as idle though they are connected. Is there a workaround?
Thanks


Tom Kyte
July 23, 2004 - 4:23 pm UTC

default gname to "you don't have access to v$instance"?

what would you like it to do?

you could try

select global_name from global_name;

instead of v$instance, i use global name myself. that has public access.

how to surpress & variable substitution message in SQL*Plus

Jianhui, September 09, 2004 - 4:25 pm UTC

Hi Tom,

in SQL*Plus, I dont want to see & variable substitution messages in my spool file, how can i do it? For example, running following commands, 
SQL> spool my.txt
SQL> @test.sql TEST
SQL> spool off

I dont want to see following lines in my spool file my.txt

old   4:   TNSNAME VARCHAR2(32) := '&1';
new   4:   TNSNAME VARCHAR2(32) := 'TEST';

 

Tom Kyte
September 09, 2004 - 5:47 pm UTC

SQL> set verify off 

is it possible to surpress "connected"

Jianhui, September 13, 2004 - 12:48 pm UTC

SQL> connect system/pwd@sverice
Connected.
SQL> 


Tom,
I dont want to see "Connected" in the spool file, while it dumps all output from multiple oracle instances to one spool file in SQL*Plus. Is it possible?

For example,
from command line:
  sqlplus -S /nolog script_for_all_instances.sql
In the script, it looks like:
  spool consolidate_report
  connect user/password@instance1
  @@generate_report.sql
  connect user/password@instance2
  @@generate_report.sql
  ...
  connect user/password@instanceN
  @@generate_report.sql
  spool off

I want to see this in the spool file:
  result of instance 1
  result of instance 2
  ...
  result of instance n
instead of:
  connected
  result of instance 1
  connected
  result of instance 2
  ...
  connected
  result of instance n

If it's impossible, i'll have to write shell script to redirect the output instead of doing it in SQL*Plus script. Just wanted to know if it's possible so i can take a shortcut.
Thanks 

Tom Kyte
September 13, 2004 - 3:23 pm UTC

In 10g, you can do this:

set echo off
connect /
spool foo
select * from dual;
spoo off
connect /
spool foo append
select * from dual;
spoo off


but in 9i and before, i do not know of anyway to surpress it.

You might spool each to a separate file and then cat (or copy) them all into one

RE: is it possible to surpress "connected"

Laurent Schneider, September 20, 2004 - 5:50 am UTC

in your script just add set term on/off


spool consolidate_report
set term off
connect user/password@instance1
set term on
@@generate_report.sql
set term off
connect user/password@instance2
set term on
@@generate_report.sql
...


Tom Kyte
September 20, 2004 - 8:44 am UTC

did you actually try it? (i did)

[tkyte@xtkyte-pc tkyte]$ cat test.sql
spool x.lst

set termout off
connect /
set termout on
select * from dual;

spool off
exit

[tkyte@xtkyte-pc tkyte]$ sqlplus -S /nolog @test.sql

D
-
X

[tkyte@xtkyte-pc tkyte]$ cat x.lst
Connected.

D
-
X

[tkyte@xtkyte-pc tkyte]$



oops

Laurent Schneider, October 18, 2004 - 6:54 am UTC

ok tom, sorry about this, I did test it to quickly, and did look the result only on the screen, not in the spool file!

Still thinking about it ...

termout off in login.sql

Laurent Schneider, March 15, 2005 - 10:24 am UTC

After the connect, login.sql is turning on the terminal output.

login.sql:
set termout off
col p new_v p
select instance_name||'/'||version||chr(10)||'> ' p from v$instance;
set sqlp "&p"
set termout on

x.sql:
set termout off
connect scott/tiger
select * from dual;

$ sqlplus "/ as sysdba" @x
D
-
X

LSC68/10.1.0.3.0
>

Any idea to reset the original setting of termout?


Tom Kyte
March 15, 2005 - 10:26 am UTC

other then echoing the result of 

SQL> show termout 

out to a temp file, no. 

how about the following lines

sql, March 15, 2005 - 6:59 pm UTC

in SQL*PLUS, "set sqlprompt" command is used to customize sql prompt..but is there a command to format the next following lines (Read ` as space character below)

I am looking for output like this

CUSTOM_PROMPT> select 1
`````````````> from
`````````````> dual

but what I always get is

CUSTOM_SQL_PROMPT> select 1
``> from
``> dual

So each time when I have to fine tune any sql I would have to revert to some sqlprompt 3 chars wide ,so that my copy paste activities is not interrupted

Looking for the command please

Thanks in advance


Tom Kyte
March 15, 2005 - 9:33 pm UTC

I've grown so used to

:1,$s/.*PT>/xxxx/

when cutting and pasting myself - makes everything "5" spaces in vi...


not that I am aware of, i didn't see anything in a quick glance.

Possible option for spacing

Mark A. Williams, March 15, 2005 - 9:51 pm UTC

If you "set sqlnumber off" in SQL*Plus, it will continue to use the value for "sqlprompt" on subsequent lines. Is that an option to keep everything "evenly spaced over" for easier copy and paste?

- Mark

Tom Kyte
March 15, 2005 - 9:56 pm UTC

there you go.... excellent idea, that would do what he wants I believe... It looks a little confusing, but makes a "square" cut and paste like in a windows cmd.exe session workable.

there goes my last hope

Venkat, March 15, 2005 - 9:56 pm UTC

Now..now..exactly now..I curse myself for *still* being a "windows" person..and when I was typing this message that "I would move to vi atleast for this" , I see another update posted by Mark..which would perfectly solve my need


Thanks Mark.for your nice tip

Thanks Tom..always been a pleasure visiting your site




thanks mark

Venkat, March 15, 2005 - 10:20 pm UTC

sorry..forgot to change the subject in previous post. I started composing "lost hope" soon I read Tom's reply..Your response/tip is highly appreciated.

Its almost been 6 years now..and still I am learning something new with oracle (again main credit goes to this site, flooded with valuable info)

:1,$s

Matthias Rogel, March 16, 2005 - 3:15 am UTC

:%s saves 1 keystroke ;-)

sorry, even 2

Matthias Rogel, March 16, 2005 - 3:17 am UTC

:-D

sqlprompt customization

Royal Ketchup, April 06, 2005 - 9:18 am UTC

Thank you Tom, this is most useful. I had to make one minor adjustment in the script body:

conn &1 &2 &3

this allowed me to do:

@conn / as sysdba



Tom Kyte
April 06, 2005 - 1:17 pm UTC

I always just

@conn "/ as sysdba"



RE: is it possible to suppress "connected"

Laurent Schneider, April 26, 2005 - 8:41 am UTC

you can delete it afterwards.

spool x.lst
connect scott/tiger
select * from dual;
spool off
host perl -ne 'unless(/^Connected./){print}' -i.bak x.lst



_CONNECT_IDENTIFIER

Yousef, June 08, 2005 - 8:24 am UTC

Dear Tom:
How do Oracle fill the value of the _connect_identifier? Is there a view that I can query to see it? Since I need to read this value in Oracle 8i, but _connect_identifier is available in Oracle 9i and above.

Thanks in advance

Tom Kyte
June 08, 2005 - 9:13 am UTC

it is not available in 8i, suggest you use the global name or instance name as demonstrated above. the tnsnames.ora entry is just a string, global name and instance name really mean something.

A reader, June 08, 2005 - 9:18 am UTC

Thank you for you advice.


On unix (Soalris) I keep getting &gname as the prompt

Paresh, August 14, 2005 - 8:08 pm UTC

Hi Tom,

Always a pleasure visiting your site. Recently I tried to make the sqlprompt trick work on unix(Soalris-ksh) at a client site but I keep getting &gname as the prompt (I am pretty sure this worked in the past).

Here is my login.sql

define gname=idle
column global_name new_value gname
set termout off
select lower(user) || '@' ||
substr(global_name,1,instr(global_name,'.') - 1) global_name from global_name;
set termout on
set sqlprompt "&gname>"

Thanks,
Paresh

Tom Kyte
August 14, 2005 - 9:03 pm UTC

works for me on solaris (shell doesn't matter)

do you have "set define off" or anything like that elsewhere?

GLOBAL_NAME & DB_NAME

Deepak, August 23, 2005 - 2:34 am UTC

Hi Tom,

I read somewhere that GLOBAL_NAME is a combination of DB_NAME and DB_DOMAIN. To check this I executed the following...

SQL> sho parameter db_domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_domain                            string

SQL> sho parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
global_names                         boolean     TRUE

SQL> select name from v$database;

NAME
---------
MYDB1

SQL> select global_name from global_name;

GLOBAL_NAME
----------------------------------------------------------------

HPDB1

But the above results made me confused.

Please help. 

Tom Kyte
August 24, 2005 - 4:01 am UTC

the global name is whatever you would like it to be plus the domain of the last global name, or whatever you would like.



ops$ora9ir2@ORA9IR2> alter database rename global_name to xxx.yyy;

Database altered.

ops$ora9ir2@ORA9IR2> select global_name from global_name;

GLOBAL_NAME
-------------------------------------------------------------------------------
XXX.YYY

ops$ora9ir2@ORA9IR2> alter database rename global_name to ora9ir2;

Database altered.

ops$ora9ir2@ORA9IR2> select global_name from global_name;

GLOBAL_NAME
-------------------------------------------------------------------------------
ORA9IR2.YYY

ops$ora9ir2@ORA9IR2> alter database rename global_name to ora9ir2.hello.world;

Database altered.

ops$ora9ir2@ORA9IR2> select global_name from global_name;

GLOBAL_NAME
-------------------------------------------------------------------------------
ORA9IR2.HELLO.WORLD

 

Deepak you have the answer right in your post!!

Paresh, August 23, 2005 - 3:10 am UTC

Hi Deepak,

GLOBAL_NAME DB_NAME||.||DB_DOMAIN

You wrote...
SQL> sho parameter db_domain

NAME        TYPE        VALUE
----------- ----------- ---------------
db_domain   string

so DB_DOMAIN = '' (above) and hence you got the expected result.

Cheers,
Paresh
Hey Tom I just saved you sometime ;). 

Please review my post

Deepak, August 23, 2005 - 8:12 am UTC

Hi Paresh,

Thanks for your reply. But I am still unclear about your explanation. I am getting different results when I queried for the GLOBAL_NAME and the DB_NAME (Please check the output of the queries again). Why this discrepancy?

Tom Kyte
August 24, 2005 - 4:19 am UTC

the db_name is the database name, the global name is a globally (within your network) name you assign, it is whatever you like it to be.

suppress the Connected. output

Laurent Schneider, March 07, 2006 - 11:38 am UTC

Here is a method which works in 10gR2

sqlplus 10.1.0.5
$ echo connect / as sysdba | sqlplus -s /nolog

Connected.


sqlplus 10.2.0.2
$ echo connect / as sysdba | sqlplus -s /nolog

[no output]


:-)

Ben, April 10, 2006 - 11:14 am UTC

Tom,
I added this to glogin.sql on my Oracle 10gR2 client:
column global_name new_value gname
set termout off
select lower(user) || '@' || global_name into gname from global_name;
set termout on
set sqlprompt '&gname> '

and I get this:
C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 10 10:43:04 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sadst04@uni0
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Enter value for gname:
>

I also tried it while adding a "set define off" command to the glogin.sql script, which gave me this:
C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 10 10:39:44 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sadst04@uni0
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

&gname>

Is there something wrong with my script?

Thanks!

Tom Kyte
April 11, 2006 - 10:25 am UTC

take away the set termout off so you can debug this and see what you see.


but actually, in reading this - where did the "into gname" come from. Where do you see that on any of the above examples?

you have set define off

Reader, April 11, 2006 - 1:53 am UTC

You have
"set define off"

before you change the prompt!!!

eg---------


set define on

..toms bit...

set defin off

--------------

Tom Kyte
April 11, 2006 - 2:27 pm UTC

(in addition to using "select into" which isn't right...)

A reader, April 11, 2006 - 4:11 pm UTC

column global_name new_value gname
--set termout off
select lower(user) || '@' || global_name from global_name;
set termout on
set sqlprompt '&gname> '

gives this result:

C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 11 15:45:19 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sadst04@uni0
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


LOWER(USER)||'@'||GLOBAL_NAME
--------------------------------------------------------------------------------
sadst04@DB_UNI0.WORLD

Enter value for gname:
>

Tom Kyte
April 11, 2006 - 7:24 pm UTC

you didn't alias the column - the column name isn't global_name, the column name is:

LOWER(USER)||'@'||GLOBAL_NAME


you might want to cut and paste my stuff without change to see that it works and then modify it from there.

How to get %ORACLE_HOME% from SQL

Rustam Kafarov, July 27, 2006 - 11:13 am UTC

How to get value of %ORACLE_HOME% from SQL In Oracle 9(!)

Following example works only in 10g:
SQL> set autopri on
SQL> var oracle_home varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)

How to solve this problem in 9i?
  

Tom Kyte
July 27, 2006 - 12:17 pm UTC

a java stored procedure may be able to get it.

Re: How to get %ORACLE_HOME% from SQL

Laurent Schneider, August 11, 2006 - 7:48 am UTC

you can eventually get the oracle_home of the client with HOST in sqlplus

SQL> var oracle_home varchar2(255)
SQL> host echo exec :oracle_home:='%ORACLE_HOME%'>oracle_home.sql

SQL> @oracle_home

PL/SQL procedure successfully completed.

SQL> print oracle_home
ORACLE_HOME
-----------------------------
/opt/oracle/product/10.2/db_1


 

prompt

sam, September 30, 2006 - 3:08 pm UTC

Tom:

I have two databases with same name on 2 machines. One database is 8i and the other is 9i. I want the prompt to show me 8iDBNAME> or 9iDBNAME> depending on which database i log into.

IS there a way to do that? I was trying to get the service name into the prompt since one has a service nam "DBANAME" and the other is "NEWDBNAME" but I could not find the TNS service name in the data dicitonary views.

thanks,

sql prompt

sam, October 02, 2006 - 12:13 pm UTC

Tom:

Do you have any suggestions for the above question.

Tom Kyte
October 02, 2006 - 12:36 pm UTC

other v$ views have db version information.  v$version for example


ops$tkyte%ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> select substr( banner, 1, instr(banner,' ')-1 )
  2  from v$version where banner like 'Oracle%';

SUBSTR(BANNER,1,INSTR(BANNER,'')-1)
----------------------------------------------------------------
Oracle9i
 

Putting TNS Service name in SQL Prompt

Tak Tang, October 05, 2006 - 10:21 am UTC

SQL*Plus 9.2 onwards maintains a substitution variable "_CONNECT_IDENTIFIER".


Here is a fragment of my "login.sql" script:

SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt

SELECT USER || '@' || NVL('&_CONNECT_IDENTIFIER', global_name ) sqlprompt
FROM global_name;

SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON


You can read all about predefined substitution variables in the SQL*Plus manual:
</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12017.htm#sthref2141 <code>


You can list the current substitution variables by typing:

TANGT@opal> define
DEFINE _CONNECT_IDENTIFIER = "opal" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000600" (CHAR)
DEFINE _EDITOR = "vim" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000400" (CHAR)
TANGT@opal>


Tak


Issues with running scripts

rp, February 09, 2007 - 6:59 am UTC

Hi Tom,

this is sort of related but apologies if not.

I have written some scripts that are run by my local (windows) sqlplus against a db on a unix box.

Halfway through running the scripts, i change user and continue:

conn &1/&2@&3

the variables were passed on the command line when sqlplus was called.

This works fine. The DBAs however run the scripts on the actual Unix box where the db is installed. They encounter an error with the '@&3' part. In fact, for them the scripts only work if it is like this:

conn &1/&2

This seems odd to me, how can SQLPlus on the server ever connect to any other instance? Is the server version of sqlplus hardwired or something?

TIA

R
Tom Kyte
February 09, 2007 - 8:26 am UTC

the @tnsnames bit is resolved by the client on the local machine (typically, you can use ldap to centralize this and not use a tnsnames.ora).

Your personal tnsnames.ora has the database alias you use

Theirs does not.

Re: Issues with running scripts

Stew Ashton, February 09, 2007 - 8:46 am UTC

On our Oracle server on AIX, before connecting locally we have to define the instance using an environment variable:
export ORACLE_SID=<instance_name>
If we want to change instances, we have to exit sqlplus, change the variable and then go back in. Using the "host" command from within sql*plus will not work.

Customizing sql prompts

Abraham, July 25, 2007 - 10:04 am UTC

Tom,

In multi user environment, I want to use "my own" customized sql prompt. If I modify glogin.sql, it will affect all users of that particular ORACLE_HOME. Is there anything that I can set in my .profile (name of a different glogin.sql, say) that will help me do this?

thank you.
Tom Kyte
July 26, 2007 - 5:24 pm UTC

just use the login.sql - set your SQLPATH environment variable to some directory, put a login.sql file there and do anything you want.

A reader, January 30, 2008 - 11:49 am UTC

Tom,
If I need to display, the below information, whenever I login to a new sql*plus session, what am I supposed to do?

INSTANCE_NAME HOST SCHEMA_NAME
---------------------------------------
TEST ABC.NET DEMO
Tom Kyte
January 30, 2008 - 1:50 pm UTC

create a login.sql or a glogin.sql file - read the sqlplus documentation for information on those files (or search for them on this site, i have examples here and there)

RE: Putting TNS Service name in SQL Prompt

Duke Ganote, January 31, 2008 - 11:12 am UTC

Sweet!  I like showing server:database\user in my SQLprompt.  However, some accounts don't have access to V$INSTANCE; SQL*PLUS used to prompt me for the SQLPrompt when V$INSTANCE wasn't accessible.

Now I can default to the _CONNECT_IDENTIFIER.  My glogin.sql includes:

    column COUNT(*) format 999,999,999,999,999;
    column CNT format 999,999,999,999,999;
    SET SERVEROUTPUT ON;
    set pagesize 50000;
    set linesize 132;
    set termout off;
    DEFINE sqlprompt=SQL;
    COLUMN sqlprompt NEW_VALUE sqlprompt;
    select 
         '&_CONNECT_IDENTIFIER'
         ||'\'||lower(user)
        as sqlprompt -- default to TNS connectString+user
      from dual;
    select 
         host_name||':'
         ||SUBSTR( global_name
                 , 1
                 , case instr(global_name,'.') 
                        when 0 
                        then 99 
                   else instr(global_name,'.')-1 
                   end
                 ) -- just first portion of global_name
         ||'\'||lower(user) 
        as sqlprompt
      from global_name, v$instance;
    set termout on;
    set sqlprompt '&sqlprompt> ';
    alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';

On limited-authority accounts, it defaults to the connect identifier, otherwise I get the server:database name, as below.

SQL> connect huontest@dw_proto;
Enter password:
Connected.

Session altered.

<b>dw_proto\huontest> </b>connect cidw@cidwdev
Enter password:
Connected.

Session altered.

<b>dwdev1:CIDW\cidw> </b>connect cidwwrite@cidwprod
Enter password:
Connected.

Session altered.

<b>dwprod1:CIDW\cidwwrite> </b>

SYS_CONTEXT

SeánMacGC, February 01, 2008 - 6:40 am UTC

As mentioned further up in this thread, SYS_CONTEXT can be used to avoid the system views altogether:

SELECT LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_NAME') || '.' || SYS_CONTEXT('USERENV','HOST') global_name FROM DUAL;

@SeánMacGC. SYS_CONTEXT vs v$ views

Duke Ganote, February 01, 2008 - 11:00 am UTC

SeánMacGC, thank you! I'd not recently looked at all that SYS_CONTEXT now offers.
http://pages.citebite.com/q2b0u6g4jbpm

This is what I now use in my glogin:

SELECT SYS_CONTEXT('USERENV','SERVER_HOST')
||':'
|| SYS_CONTEXT('USERENV','DB_NAME')
||'\'
|| LOWER(SYS_CONTEXT('USERENV','CURRENT_USER'))
as sqlprompt
FROM DUAL
;

login.sql in 11.1.0.6.0

Rajeshwaran, Jeyabal, June 27, 2010 - 5:51 am UTC

Tom:

I created a login.sql file and placed that in BIN folder and it seems not working properly. Please help me to setup this login.sql correctly.


D:\>sqlplus scott/tiger@10GR2

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 27 16:06:59 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Now the SQL prompt is not set correctly. Below is my environment variable setting.

D:\>echo %path%
 C:\Program Files\Business Objects\common\3.5\bin\NOTES\;
 C:\Program Files\Business Objects\common\3.5\bin\NOTES\DATA\;
 D:\oracle\product\10.2.0\bin\;
 C:\WINDOWS\system32;C:\WINDOWS;
 C:\WINDOWS\System32\Wbem;
 C:\Program Files\TortoiseSVN\bin;
 C:\Program Files\Windows Imaging\

D:\>echo %oracle_home%
 D:\oracle\product\10.2.0\

D:\oracle\product\10.2.0\BIN>dir login.sql
 Volume in drive D is DATA
 Volume Serial Number is 3690-C12D

 Directory of D:\oracle\product\10.2.0\BIN

09/04/2009  03:20 PM             2,641 login.sql
               1 File(s)          2,641 bytes
               0 Dir(s)  19,253,395,456 bytes free


But, If i navigate to BIN folder and connect through sqlplus the login.sql script executes sucessfully.

D:\oracle\product\10.2.0\BIN>sqlplus scott/tiger@10GR2

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 27 16:09:27 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@10GR2>


Please help me to correct this.

-- Rajeshwaran, Jeyabal
Tom Kyte
July 06, 2010 - 11:02 am UTC

the login.sql needs to be in your SQLPATH - not PATH. PATH is for windows to find executables to run.

SQLPlus uses SQLPATH

http://www.oracle.com/pls/db112/search?remark=quick_search&word=sqlpath

Thanks for the question regarding "customizing the SQLprompt", version 9i

armelonzoukson, December 18, 2012 - 8:49 am UTC

Hi,
In oracle 9i tochange my SQL prompt displaying "user@instance" ,do that:
on linux or unix os go to glogin.sql file on $ORACLE_HOME/sqlplus/admin directoy using vi editor for example and copy this sql code:
SET TERMOUT OFF
COL login_prompt new_value prompt_dbname
SELECT SYS_CONTEXT('USERENV','CURRENT_USER')||'@'||SYS_CONTEXT('USERENV','DB_NAME') login_prompt FROM DUAL;
set sqlprompt "&&prompt_dbname>"
SET TERMOUT ON .
So it's ok.
regards

Updated for 12c?

Richard C. Evans, July 19, 2013 - 2:52 pm UTC

I've been using the SQLPROMPT for years now and with the introduction of CDBs and PDBs how would we update your original statement?

I would love to have my prompt be something like "user@cdb:pdb>" so we know exactly what we are connected to.

I investigated updates to the SQLPROMPT variables such as _CONNECT_IDENTIFIER but it does not get updated when we issue a connection to the PDB as "alter session set container = pdb1;"

Regards,
Rich

Tom Kyte
July 31, 2013 - 3:03 pm UTC

I don't see the use of alter session like that to set a container as something you'd be doing.

Rather, you would just connect to the database - pluggable or not - the "old fashioned way". The alter session command wouldn't be practical in life - you don't want a single credentialed user to be able to log in all over the place.

You'd be using good old connect. the alter session set container is good for a connection pooled thing perhaps - but not really for day to day use in my opinion.

Updated for 12c?

Richard C. Evans, August 01, 2013 - 3:44 pm UTC

Thanks Tom,

For better or worse, I use system or / as sysdba for almost every connection I make to any DB I maintain. I rarely login as an application user. In a 12c environment, where we have multiple PDBs for developers I see myself switching between them often to execute scripts, check jobs, create/unlock users, etc. I see us using something like "alter session switch container" quite frequently as DBAs -- maybe there is a more effective way to work on this but as I'm testing this is what I'm leaning on right now. I'm all ears :-)

What I do now is I wrote a couple short scripts to give me information on "where" I am and to "switch" between environments. The switch.sql is still a work in progress because I can't see any other PDBS/CDBS from v$pdbs if I'm in a PDB (i.e. in pdb1 I can't see that pdb2 exists). I'm probably querying the wrong view but I haven't had a chance to delve into it further yet.


[oracle]$ cat where.sql
column where format a30
SELECT 'USER: '||SYS_CONTEXT('USERENV','CURRENT_USER')
||chr(10) ||'SCHEMA: '||SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
||chr(10) ||'CONTAINER DB: '||SYS_CONTEXT('USERENV','CON_NAME')
||chr(10) ||'CONTAINER: '||SYS_CONTEXT('USERENV','CDB_NAME')
"WHERE"
FROM DUAL;

[oracle]$ cat switch.sql
prompt Choose which PDB/CDB you would like to switch to...

SELECT NAME, OPEN_MODE from v$pdbs;

alter session set container = &PDB_NAME;

prompt Show your new environment...

@where.sql

SYS@testdb> connect system
Enter password:
Connected.
SYSTEM@testdb> @where

WHERE
------------------------------
USER: SYSTEM
SCHEMA: SYSTEM
CONTAINER DB: CDB$ROOT
CONTAINER: testdb


SYSTEM@testdb> @switch
Choose which PDB/CDB you would like to switch to...

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE

Enter value for pdb_name: pdb2
old 1: alter session set container = &PDB_NAME
new 1: alter session set container = pdb2

Session altered.

Show your new environment...

WHERE
------------------------------
USER: SYSTEM
SCHEMA: SYSTEM
CONTAINER DB: PDB2
CONTAINER: testdb

SYS@testdb> @switch
Choose which PDB/CDB you would like to switch to...

NAME OPEN_MODE
------------------------------ ----------
PDB2 READ WRITE

Enter value for pdb_name: pdb1
old 1: alter session set container = &PDB_NAME
new 1: alter session set container = pdb1

Session altered.

Show your new environment...

WHERE
------------------------------
USER: SYS
SCHEMA: SYS
CONTAINER DB: PDB1
CONTAINER: testdb

Because I'm using _CONNECT_IDENTIFIER each time I switch PDBs my SQLPROMPT stays the same. I'm thinking I could utilize your command and tweak it to show the cdb:pdb each time. I'll play with the SQL logic more...

Thanks,
Rich

Tom Kyte
August 02, 2013 - 7:21 pm UTC

... For better or worse, I use system or / as sysdba for almost every connection I
make to any DB I maintain. ....

there is no better to it, it is all for the worse. sysdba should *hardly* ever be used. It is just way too dangerous.



you can either

a) use connect
b) use alter

they consume about the same number of characters. If you want sqlplus to behave the way you want sqlplus to behave - use connect.


and seriously- being 100% honest here - get out of the very very bad habit of using sysdba and system day to day, use your OWN DBA account, or maybe not even a DBA account.

Prakash, May 27, 2014 - 5:41 am UTC

try this

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

now type this inside,

set sqlprompt "&_user>"
define_editor=vi





this will definitely work.

Alf, March 26, 2018 - 5:27 pm UTC

Hello Gurus,
For Non sys user custom prompt
The below custom prompt is to help regular users (no sys/system) I’ve shaped this to the point which shows me enough enough information. But, for a reason I can’t yet, the sqlp complains it too long the max is 50.

1) The actual numbers of characters for user-SID-Version-host is 39
2) the number the select returns is 68, there seems to be lost of added un-seem characters.

Tried use TRIM (L and R) did not make any difference.

Any idea what function would help to either undercover the the garage/space or spacial character and formatted with correct light? Thanks in advance for any insight..

rem #
set timing on
set heading off;
set termout off;

DEF p1= "SQL>"
col "p1" new_value gp noprint;
set lines 69
WITH g2 AS
( select chr(27) ||'[93m'|| substr((select user from dual),1,16)||chr(27)||'[m' || '@'||
chr(27) ||'[95m'|| substr(i.instance_name,1,6) ||chr(27)||'[m' || '-'||
chr(27) ||'[96m'|| substr(i.version,1,4) ||chr(27)||'[m' || '-'||
chr(27) ||'[91m'|| substr(i.host_name,1,11) ||chr(27)||'[m' unvh
from v$instance i
) SELECT trim(g2.unvh) p1 FROM g2;
set sqlp "&gp";
set termout on;
set heading on;

SQL> @../s/g6g.sql;
..

Elapsed: 00:00:00.13
string beginning “”xxx…” is too long. maximum size is 50 characters.
SQL>
--


SQL> @../s/g6g.sql;

P1
--------------------------------------------------------------------
XXXXXXXXXXXXXXXX-psup2-11.2-xxx-sdb-xxx = 39 characters
Connor McDonald
March 27, 2018 - 5:37 am UTC

SQL> WITH g2 AS
  2  ( select chr(27) ||'[93m'|| substr((select user from dual),1,16)||chr(27)||'[m' || '@'||
  3  chr(27) ||'[95m'|| substr(i.instance_name,1,6) ||chr(27)||'[m' || '-'||
  4  chr(27) ||'[96m'|| substr(i.version,1,4) ||chr(27)||'[m' || '-'||
  5  chr(27) ||'[91m'|| substr(i.host_name,1,11) ||chr(27)||'[m' unvh
  6  from v$instance i
  7  ) SELECT length(g2.unvh) p1 FROM g2;

        P1
----------
        56


When *finally* the characters get processed by the terminal, they are transposed into color commands etc, but *before* that, you have 56 characters of information. That is all that SQL Plus sees - it does not know that much of these "characters" are actually terminal control characters.

for Non sys user custom prompt

Alf, March 27, 2018 - 1:29 pm UTC

Correct and brilliant insight.

I've substr and removed color codes and chr(27) for some of the values to fit it 50 max chars.

Having said. Isn't "def p1=gp" suppose to bypass the 50 max characters limit? Can you spot a typo or something I keep missing. Thanks in advance.

....
def p1=gp
col "p1" new_value gp noprint
WITH g2 AS
( select substr((select user from dual),1,8)||'@'||
chr(27)||'[95m'||substr(i.instance_name,1,6)||chr(27)||'[m'||'-'||
substr(i.version,1,4)||'-'||
chr(27)||'[91m'||substr(i.host_name,1,9)||chr(27)||'[m' unvh
from v$instance i
) SELECT g2.unvh p1 FROM g2;
set sqlp "&gp SQL>"
set termout on
set heading on
Connor McDonald
March 28, 2018 - 1:42 am UTC

amper variables are simply text substitution.

So

set sqlprompt "&blah"

is expanded to:

set sqlprompt "my big piece of text which might be too long"

and you are in the same predicament.