Wednesday, December 17, 2014

SDSQL - Output Formats

   In SQL Developer, Jeff has shown in many tips & tricks talks and here on his blog how to get data from any select into a few formats.  The most common I would say is csv.   Nothing here has changed. Well, almost.  What will be new is the html format is getting a facelift and a json format is being added neither are in the version of sdsql or sql developer that are on otn yet

    As expected all those "hint" work in sdsql as well.  What's new is there is a "set sqlformat" command so that all results come out in that format. So, all the same formats work : xml, html, delimited, insert, loader, fixed, text. Also there's a new one named ansiconsole which is described here.

  Now with the new things being added it could be easy to create a "build" script of sorts.  Combine this with the new ddl command into something like this

SQL>set sqlformat insert
SQL>spool my_build_script.sql
SQL>ddl emp
SQL>select * from emp;
SQL>apex export 101
SQL>spool off

What you would end up with out of this is a sql script with the ddl and data for emp and the Apex App export.  This script could then be checked in, given to a DBA, or even just for a nightly backup of sorts.

Tuesday, December 16, 2014

SDSQL - Pre and Post Commands

  This is in the process of evolving but should be enough for anyone to give it a whirl.  The idea is that for any given statement you may want to run something before and possible after.

  Here's a trivial example.  I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command.  Then after I'm doing sql that shows me the changes from that point to now.

SQL> select count(1) from emp;

Table MY_STATS dropped.

Table MY_STATS created.


NAME                                                                  DELTA
---------------------------------------------------------------- ----------
logical read bytes from cache                                       1859584
session pga memory                                                  1507328
session uga memory                                                  1035504
KTFB alloc space (block)                                              65536
cell physical IO interconnect bytes                                   32768
physical write total bytes                                            24576
physical write bytes                                                  24576
redo size                                                             16200

This is on the same connection you are executing on so whatever is being done will show in the stats as well. One thing still being worked on is the ability to clone and pass some context to another connect and have these pre and post things done on that connection keeping the main connection free of that noise.

The way to do this is the introduction of 2 new set commands. The names should be a clue as to which is which : set precommand and set postcommand. They can hold 1 or N commands to be run.

set precommand drop table my_stats; create table my_stats as select * from v$mystat;
set postcommand select, ( m2.value - m.value ) delta from my_stats m, v$mystat m2, V$STATNAME s where m.STATISTIC#   = m2.STATISTIC# and m.STATISTIC#    = s.STATISTIC# and m.value != m2.value order  by 2 desc;

select count(1) from user_objects where object_name like 'E%';

The current downside of this method is that it needs to be all on one line.  So, if there's multiple commands to be run it's kinda ugly.  Like in this precommand where I drop and create a table.  The great thing about this is that the command to be run could be @mypre.sql

This allows me to better format the sql and make it much much easier to read and maintain.

└─>cat pre.sql 
drop table my_stats;
create table my_stats as select * from v$mystat;

└─>cat post.sql 
select, ( m2.value - m.value ) delta 
  from my_stats m, v$mystat m2, V$STATNAME s 
  where m.STATISTIC#   = m2.STATISTIC# 
    and m.STATISTIC#    = s.STATISTIC# 
    and m.value != m2.value order  by 2 desc;

Then the sql set command look just like expected.

set precommand @pre.sql
set postcommand @post.sql

Now if you want to mess with people throw something like this in their login.sql when they aren't looking

SQL> set postcommand select 'Better go tune that before Joel sees it' MOTD  from dual;

Then on every command they will get that message.

SQL> select 1 from dual;


Better go tune that before Joel sees it


Friday, December 12, 2014

Aliases with sdsql and simpler CTAS

First, we just put up a new build of sdsql.  Go get it or the last thing here will not work.

  SQL is a great and verbose language so there's many ways to shorten what we have to type.  As simple as a view or saving a script to call later with @/path/to/sessions.sql  SDSQL is taking it a step further and we added aliases to the tool.  Almost as if right on queue, John asked if we could add them.

Here's a simple example where I setup an alias for "sessions" in my login.sql.  Once defined  you simply type that alias name hit enter and go.  Now do not go and be trying to alias "select" or other base things.  Aliases are evaluated after everything we normally recognize , although it would be an awesome April fool thing to edit a friend's file and do that. hmm maybe a new feature....

Setting up these aliases are very simple yet quite flexible.  In the simplest form,

KLRICE@ORCL>alias ls=select object_name,object_type,last_ddl_time 
  2  from user_objects
  3   order by 2,3;
OBJECT_NAME                     OBJECT_TYPE        LAST_DDL_TIME  
REMOTEDB                        DATABASE LINK                     
DEMO_ERROR_HANDLING             FUNCTION           25-SEP-14      
DO_WORK                         FUNCTION           25-SEP-14      
EXECSQL                         FUNCTION           25-SEP-14      
FIB                             FUNCTION           25-SEP-14      

Now add in binds and it become very flexible.  This is just one bind but you can probably guess the names of binds 2,3,N

KLRICE@ORCL>alias ll=select object_name,object_type,last_ddl_time 
  2  from user_objects
  3  where object_name like :1||'%';
EXECSQL        FUNCTION     25-SEP-14      
EMP_TRG1       TRIGGER      25-SEP-14      
EMP_SEQ        SEQUENCE     25-SEP-14      
EMP_NEW        TABLE        11-DEC-14      
EMPLOYEES_PK   INDEX        25-SEP-14      
EMPLOYEES22    TABLE        25-SEP-14      
EMP            TABLE        25-SEP-14      
E1             TABLE        11-DEC-14      

It works for aliasing off plsql calls also.

If you need to see the aliases defined, just type alias or alias list.

If you need to see what the alias is, just type alias list <alias name>

There's lot you can do with this to save lots of typing and personally there'll be lots less typo for my presentations!

CTAS Easier than ever

Lastly, for the one that went and downloaded the build we just posted today there is a new CTAS <TABLE> <NEW_TABLE> command added just yesterday.  The idea here is yet again to save on typing.  This is just getting dbms_metadata information so what is used can be adjusted as I explained here  Then strip off the things not needed ( i.e. datatypes ) swap out the names and you get a new table with very very little typing.  Note the index on empno carries over.

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.


  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.

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, ...

└─>./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 - 64bit Production 

KLRICE@ORCL>host pwd
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
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 
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

Wednesday, December 10, 2014

SDSQL's flux capacitor

  Writing sql or any code is an iterative process.  Most of the time that means to see what you have done say 5 minutes ago means how big is your undo buffer or better is if you are in SQL Developer there's a full blown history.  If you are in sqlplus, you are basically out of luck.


  SDSQL has built in history and between sessions.  We are still jiggling where it stores the history so from EA1 to next it's most likely going to change to be configurable and default to your PWD.  That means a possible shared history OR a per folder history.  If you type as little as possible like me, simply typing h will print the history upto the last 100 commands.
  But wait there's more.  Try "history full" and you get the not condensed sql for example from the previous screenshot item #36 is ugly and not what I typed in.  I use typed very loosely since I pasted it.  Typing history full shows the real sql

  Curious how many time a statement user executed? Try typing "history usage" and now it shows how many times with a number in parentheses.  For example here, I forget a lot what emp looks like so I've done "info emp" 11 times.

If you read my history, you probably noticed the alias command in here.  Check out the help for it and give it a try.