1

For a long time now, I've had my login.sql file configured to set the prompt in SQLPLUS to two lines, so that it looks like so:

my_username@my_db_instance
SQL>

The code within login.sql that accomplishes this is below (the chr(10) is what creates the 2nd line):

define gname = 'SQL'
column global_name new_value gname

select user||'@'||instance_name||chr(10)||'SQL' as global_name 
from v$instance;

set sqlprompt '&&gname> '

However, the same does not work for SQLcl, and the same code results in the SQLcl prompt appearing all one line, like so

my_username@my_db_instance SQL>

I tried replacing the chr(10) with chr(13), chr(13)||chr(10), and u'\000A', but the SQLcl prompt displays on one line regardless. I've confirmed that SQLcl is actually reading the login.sql in question by making other changes to the prompt (e.g. replacing SQL with SQL1), and those changes worked.

Does anyone know whether it's possible to separate the SQLcl prompt into 2 lines? And if so, how?

(ps, I realize this is a pretty petty annoyance, but though I like having the connection info in the prompt, having the 1st line of all my sql starting way off to the right is just...yuck :)

Phil Goldenberg
  • 78
  • 7
  • It seems to be possible, if painful, with escape codes; but it looks like the prompt length is taken as the whole string length, not just the length of the second line - which messes up history and editing. It might be worth asking on [the SQLcl forum](https://community.oracle.com/tech/developers/categories/sqlcl). – Alex Poole Jun 30 '21 at 12:01

1 Answers1

2

This is just a workaround.

I use a window "title" for this. I think it's more convenient: enter image description here

You can create a script like this: https://github.com/xtender/xt_scripts/blob/master/inc/title.sql

and add it into your on_login.sql, for example from my own on_login.sql:

@inc/title "&db_name / &my_user / &db_host_name   SID=&my_sid    SERIAL#=&my_serial     SPID=&my_spid     IS_DBA=&my_is_dba / INST_ID = &DB_INST_ID / DB_VERSION = &DB_VERSION"

Other my sql*plus tips: http://orasql.org/tag/sqlplus-2/

Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27