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.

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.

  COUNT(1)
----------
        14

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 s.name, ( 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 s.name, ( 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;


         1
----------
         1

MOTD                                 
---------------------------------------
Better go tune that before Joel sees it

SQL> 

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.

@krisrice Any chance of an ‘alias’ command while you’re at it? ;)

— John Scott (@aejes) December 12, 2014

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;
KLRICE@ORCL>ls
Command=ls
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||'%';
KLRICE@ORCL>ll E
Command=ll
OBJECT_NAME    OBJECT_TYPE  LAST_DDL_TIME  
EXECSQL        FUNCTION     25-SEP-14      
EMP_TRG1       TRIGGER      25-SEP-14      
EMP_SEQ        SEQUENCE     25-SEP-14      
EMP_NEW        TABLE        11-DEC-14      
EMPLOYEES_SEQ  SEQUENCE     25-SEP-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.

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.

@krisrice @thatjeffsmith More generally on SQL*Plus ++ a "cd" command (implies "pwd" too) to allow ad hoc script running with less typing.
— Bryn Llewellyn (@BrynLite) November 29, 2014

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

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.

History

  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.

Getting DDL for objects with sdsql

Getting ddl out for any object is quite simple.  You can just call dbms_metadata with something nice and easy like select dbms_metata.get_ddl('TABLE','EMP') from dual;

SQL>  select dbms_metadata.get_ddl('TABLE','EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "KLRICE"."EMP"
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
 "ENA

SQL>

Now you are all set and go create a table right ? Not really. The easiest part is setting pages or set long to get the full output.  The hard part is getting the dependent objects like triggers.


Now introducing the new DDL command

KLRICE@ORCL>ddl emp

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB, 
  PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
  FOREIGN KEY ("MGR")
   REFERENCES "KLRICE"."EMP" ("EMPNO") ENABLE, 
  FOREIGN KEY ("DEPTNO")
   REFERENCES "KLRICE"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" 
 LOB ("MYCLOB") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE
KLRICE@ORCL>

Too much details?

This is still using dbms_metadata underneath it so you can adjust the output by setting some transformation parameters on metadata.  For example:

KLRICE@ORCL>host cat examples/ddl_emp.sql
begin
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

  end;
/

ddl emp
KLRICE@ORCL>@examples/ddl_emp.sql
anonymous block completed

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0), 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB
   ) ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


/
ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE;
KLRICE@ORCL>

But wait I have an Application Express Application I need the 'ddl' for that too. Give this a try.

OBE@ORCL>apex export 100
<<< LOTS AND LOTS OF TEXT WILL PRINT >>

Now couple these with spool and you have a sql script to get objects with dependencies and the apex application that goes with it all.

OBE@ORCL>spool mybuild.sql
OBE@ORCL>ddl emp
OBE@ORCL>ddl dept
OBE@ORCL>apex export 100
OBE@ORCL>spool off

SQL Developer 4.1 EA1 is out

SQL Developer 4.1 is out for tire kicking.  There's a lot of new things in there as well as some great enhancements.  There's a nice shiny new Instance Monitor to get an overview of what's going on.   Keep in mind this is in flux from a UI stance and the final may be quite different that what you see today.  There's tons of data here and mostly all things have drill downs.





Also, we took the sqlplus engine we have and built a command line for it and named it SDSQL.  Someone will say this stands for sql developer sql but let's not dwell on names.  This new command line has TONS of things in it.  Please keep in mind this is an EA.  The new command line is very very compatible with sqlplus in fact I alias sqlplus to my sdsql and that's all I use now.  However, this is an EA and it is not 100% sqlplus and may never be there's some very odd old things that sqlplus does like did you know "SQL> #create table klr_1 ( id number );" works?  Yeah , we're not doing that.

Anyway, grab it, try it, see what you think.  On the 4.1 download page there's a section for SDSQL.  Just grab that and as usual just unzip and ./sdsql.   This screenshot shows color coding and a nice compact printing of the results of a query.  This results is buffered and the col headers are set to the max of the datasize completely ignoring whatever your linesize is set to.  The catch is only 5k rows is supported since it's buffering.  Query over 5k and you'll get a nice warm message that says unsupported.

Here's my Login.sql if you want to try it out.

set sqlformat ansiconsole
set sqlprompt '"@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue,bg_green,bold >|@"

If you want different colors, here's a script that shows a lot of the possibilities.

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

And here's what you'll see from it.