Thursday, December 11, 2014

What is SDSQL ?

  SQL Developer is now up to version 4.1 and has had many additions over the years to beef up the sqlplus compatibility.  This is used today by millions of users adding up to millions if not billions of hours in the tool doing their work.  That means our support of core sqlplus has to be very full featured.  One idea we kicked around for a while but never had the time to do was to make our sqlplus engine available on a command line.  The huge benefit of this is that we could then run the actual sqlplus regression tests against our code line.

Enter SDSQL

  We did exactly that.  The guts of SQL Developer's sqlplus support married with a command line interface.  The result is a 10mb download soon with native installers.  That 10mb includes everything you need including the jdbc driver.  Now while replicating sqlplus is interesting and will greatly improve our support for it, it's well boring.  Writing code that does nothing more than mimic other code isn't fun.  So we are adding lots and lots of new things.   The first things you'll notice using sdsql is the arrow keys work, there's history, there's color these and more are just core things we added.  There's also entirely new commands being added and the easiest way to see some of these new things is to use the help command.  The non-sqlplus command are highlighted.  Give them a try.

Help
Here's something added for Bryn which is pretty much obvious yet never been in sqlplus.  I'm in sqlplus and want to change directory to a new path and run some scripts.  In sqlplus, you either exit, cd, and start back up or path all the scripts like @my/other/path/script.sql

Now there's a built in "cd" command.



Here's a quick example.  I start the tool in one path , raptor_common,  then need to run a script in the sub directory.  A quick cd examples then @color.sql.  All host operations are now in that path.  That means running a script, spooling a file, host commands, ...

KLRICE@ORCL>┌─[14:29:56]─[klrice]─[kriss-MacBook-Pro-2]:~/workspace/raptor_common$
└─>./sdsql klrice/klrice@localhost/orcl

sdsql: Release 4.1.0 Beta on Thu Dec 11 14:30:04 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 

KLRICE@ORCL>host pwd
/Users/klrice/workspace/raptor_common
KLRICE@ORCL>host ls
color  examples login.sql sdsql  test.sql
KLRICE@ORCL>cd examples    <<<<<<< I can just cd to the new path
KLRICE@ORCL>host pwd       <<<<<<< I can do hosty things here
/Users/klrice/workspace/raptor_common/examples
KLRICE@ORCL>host ls        <<<<<<< Then run host command in that path
color.sql ctas.sql ddl_emp.sql
KLRICE@ORCL>@color         <<<<<<< Then run sql from it 
Colors                                     
This is red,bold,underline             
 This is negative                           
 This is faint                              
 This is my bold                            
 This is italic                             
 This is underline                          
 This is blink_slow                         
 This is blink_fast                         
 This is conceal                            
 This is black                             
 This is green                             
This is yellow                             
 This is blue                              
 This is magenta                           
  This is cyan                             
 This is white                             
 This is bg_red                            
 This is bg_black                          
 This is bg_green                          
This is bg_yellow                          
 This is bg_blue                           
 This is bg_magenta                        
  This is bg_cyan                          
 This is bg_white                          
 This is bg_red                            


KLRICE@ORCL>host cat color.sql

select '@|red,bold,underline This is red,bold,underline|@' "@|red Colors|@"  from dual
union all
select '@|NEGATIVE_ON  This is negative|@'   from dual
union all
select '@|INTENSITY_FAINT  This is faint|@'   from dual
union all
select '@|INTENSITY_BOLD  This is my bold|@'   from dual
union all
select '@|ITALIC  This is italic|@'   from dual
union all
select '@|UNDERLINE  This is underline|@'   from dual
union all
select '@|BLINK_SLOW  This is blink_slow|@'   from dual
union all
select '@|BLINK_FAST  This is blink_fast|@'   from dual
union all
select '@|CONCEAL_ON  This is conceal|@'   from dual
union all
select '@|black  This is black|@'   from dual
union all
select '@|green  This is green|@'   from dual
union all
select '@|yellow This is yellow|@'   from dual
union all
select '@|blue  This is blue|@'   from dual
union all
select '@|magenta  This is magenta|@'   from dual
union all
select '@|cyan   This is cyan|@'   from dual
union all
select '@|white  This is white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual
union all
select '@|bg_black  This is bg_black|@'   from dual
union all
select '@|bg_green  This is bg_green|@'   from dual
union all
select '@|bg_yellow This is bg_yellow|@'   from dual
union all
select '@|bg_blue  This is bg_blue|@'   from dual
union all
select '@|bg_magenta  This is bg_magenta|@'   from dual
union all
select '@|bg_cyan   This is bg_cyan|@'   from dual
union all
select '@|bg_white  This is bg_white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual



If you want better output try the script and see why it's named color.sql