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


Posted by at
Labels: ,