Having Fun With Prompts – and Saving Yourself from Mistakes

thatjeffsmith SQL Developer 2 Comments

Tell Others About This Story:

In SQL Developer we have color coded connections.

Feeling blue?

Feeling blue?

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 ? >|@"
GREEN = 'safe place' AWESOME emoji, just b/c

GREEN = ‘safe place’ AWESOME emoji, just b/c

Tip: On a Mac, you can invoke the Emoji keyboard by hitting ctrl+cmd+spacebar.

The @|[email protected] @|[email protected] – 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.

Remind me not to upset these folks.

Remind me not to upset these folks.

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!

Tell Others About This Story:

Comments 2

  1. 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();

Leave a Reply

Your email address will not be published. Required fields are marked *