This is my login.sql

SET sqlprompt "_user'@'_connect_identifier > "
SET sqlformat ansiconsole
SET serveroutput on
SET lines 3000

How to set the sqlprompt to color red for Prod (and green color for testing)?

  • 1
    set sqlprompt "@|red _USER|@@@|red _CONNECT_IDENTIFIER|@@|red >|@" – Kumar Nov 12 at 16:10
  • 2
    Check from ThatJeffSmith website,you can set color dynamically based on user or any other unique identifier(I didn't test though).Here is the link – Kumar Nov 12 at 16:41

From above link I created a Java Script in sqlcli bin directory(on windows 10)

C:\sqlcl-\sqlcl\bin>type PromptColor.js
//file name PromptColor.js save in sqlcli bin dir
//login sqlcli and run script PromptColor.js
var dbUser = util.executeReturnOneCol('select user from sys.dual');
if ( dbUser == 'HR' )
   sqlcl.setStmt('set sqlprompt "@|red _USER|@@@|red _CONNECT_IDENTIFIER|@@|red >|@"');
else if ( dbUser == 'SOE')
    sqlcl.setStmt('set sqlprompt "@|green _USER|@@@|green _CONNECT_IDENTIFIER|@@|green >|@"');

    sqlcl.setStmt('set sqlprompt "@|blue _USER|@@@|blue _CONNECT_IDENTIFIER|@@|blue >|@"');


login with default prompt

C:\sqlcl-\sqlcl\bin>sql soe/soe@localhost:1521/orclpdb.localdomain -- pdb database 

SQLcl: Release 19.2.1 Production on Tue Nov 12 12:17:13 2019

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

Connected to:
Oracle Database 19c Enterprise Edition Release - Production
SQL>script PromptColor.js

enter image description here

Next is to figure how to call javascript from login.sql

Edit:- create a sql file with single line script PromptColor.js place in SQLPATH directory and add that sqlfile name in login.sql it automatically changes color based on user logged in

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.