In SQL Developer we have color coded connections.
This was added, not because we like Christmas trees and all the pretty lights, but because sometimes you need a reminder of what database you’re on. So you don’t TRUNCATE stuff, accidentally. Visual cues won’t prevent all mistakes, but they sure help.
With SQLcl, you have the abilty to setup your SQL prompt with custom text and colors. This works better if you have a decent terminal. Windows 7? Not so much.
I thought I’d share an example or two here, and let others share theirs in the comments. Have fun!
>set sqlprompt "@|white _USER|@@@|green _CONNECT_IDENTIFIER|@@|white ? >|@"
Tip: On a Mac, you can invoke the Emoji keyboard by hitting ctrl+cmd+spacebar.
The @|white@ @|green@ – stuff – that invokes terminal text coloring. Examples for text coloring/decorating are specified here – thanks Kris!
How About A Dynamic Color/Emoji Base On User or DB?
Using the SCRIPT command to invoke some javascript at connect time, we can see who your user is and what your SERVICE/SID is and update your prompt dynamically.
Again, example here thanks to Kris.
/* execute a sql and get the first column of the first row as a return*/ var dbUser = util.executeReturnOneCol('select user from dual'); /* based on the connect user change my SQL prompt*/ if ( dbUser == 'KLRICE' ) { sqlcl.setStmt('set sqlprompt "@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue ?? >|@"'); } else { sqlcl.setStmt('set sqlprompt "@|blue _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue ? >|@"'); } sqlcl.run();
Speaking of Prompts and Logins, How About a Custom Sound/Song at Startup?
Again, with javascript and the SCRIPT command.
Where’s the code you say? Here.
Have a ‘Cool’ Prompt or Login Script? Share it!
Leave something in the comments – we’ll all be thankful!
8 Comments
Interesting but I would never use sounds 🙂
Instead, I would like to know how can I insert a newline character in the prompt or at the end of it?
In UNIX shel that is dead simple but here I could not find a solution!
Thanks
Hey mister Jeff, I’m starting exploring sqlcl and I like the possibilit of having a customized prompt. Is there a way to script it at logon time?
In SQLPlus I have a glogin file like this:
column sqlprompt_col new_value sqlprompt_value
set termout off
define sqlprompt_value=’NOT CONNECTED’
select lower(user)||’@’||instance_name
as sqlprompt_col
from v$instance;
set termout on
set sqlprompt ‘&sqlprompt_value> ‘
This whole block works perfectly when manually issued in a sqlcl prompt, but I can’t find where to put it to be launched automatically on each logon. Is there a way to have a glogin file fired automatically like with SQLPlus this is not possible yet?
run ‘show login’ and you can see where to put your login script
OK, did some research and found this blog entry:
https://www.talkapex.com/2015/05/sqlcl-and-loginsql/
So, the login file must be present in the directory where sqlcl is launched? Is there no other mean?
I could replicate the workaround offered by the post, setting the SQLPATH with path to my login file, but still, isn’t there a native config for this?
Ooops, sorry, did not see you answer before writing mine.
I just checked and found that by default (after unseting the SQLPATH) show login points to C:\oracle\ora11Gr203x64_cli\dbs
I’m not familiarized with Oracle’s folder structure, but the dbs directory seems to be the server parameter file folder. Since I’m running no server there’s no such directory on my client’s folder. Is my client wrongly configured or maybe sqlcl?
Anyway I just created the dbs folder and put the login file and now it works correctly. I would still suggest to make it easier, like having the login file in the same folder as the sqlcl executable – maybe even having an empty example one.
Thank you very much for the help!
So, my prompt now has colours to highlight SYS or production database logons. Also displays Session ID and Serial number in case you want to trace or monitor the session.
Include “SCRIPT prompt.js” at the end of LOGIN.SQL, but be aware of security issues doing this. I adapted Kris’s script above.
/* execute a sql and get the first column of the first row as a return*/
var dbUser = util.executeReturnOneCol(‘select user from sys.dual’);
var dbName = util.executeReturnOneCol(‘select name from v$DATABASE’);
var dbSid = util.executeReturnOneCol(‘SELECT LTRIM(sid) sid FROM v$session, v$instance i WHERE sid in (SELECT sid FROM v$mystat WHERE rownum < 2)');
var dbSerial = util.executeReturnOneCol('SELECT LTRIM(serial#) serial# FROM v$session, v$instance i WHERE sid in (SELECT sid FROM v$mystat WHERE rownum < 2)');
var dbHostname = util.executeReturnOneCol('SELECT i.host_name FROM v$session, v$instance i WHERE sid in (SELECT sid FROM v$mystat WHERE rownum |@”‘);
} else {
sqlcl.setStmt(‘set sqlprompt “@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@’ + dbHostname + ‘:’ + dbSid + ‘.’ + dbSerial + ‘@|white > |@”‘);
}
} else {
sqlcl.setStmt(‘set sqlprompt “@|green _USER|@@@|blue _CONNECT_IDENTIFIER|@@|green > |@”‘);
}
sqlcl.run();
Realised after all this time that my post was really mangled by cut and paste. Apologies to anyone who scratched their heads over it.
I’ve stored a tested version of the script in a git repository. If you add SCRIPT prompt.js to login.sql and set your SQLPATH to point to the scripts, you’ll get the results promised.
See the website for how to download the scripts.
https://oracleexpert.net/login-sql/
Here’s my SQLCL prompt, with text and colors:
set sqlprompt “@|white ‘I am ‘|@@|bold _USER|@@|white ‘ on ‘|@@|blue,bg_white _CONNECT_IDENTIFIER|@@|bg_cyan ‘ > ‘|@”