Tuesday 6 January 2015

Alias command - a little like shell alias

SQL> help alias
ALIAS
------

Not a sqlplus command.

alias
alias name=select :one from dual;
SQL> alias fred=select :one from dual;
SQL> fred 1
Command=fred
:ONE
--------------------------------
1

SQL> alias db= begin dbms_output.put_line('hi'); end;
2  /
SQL> db
Command=db
anonymous block completed
hi

alias is single line terminated by newline
alias ..=.. is terminated by ';'
alias ..=begin or ..=declare is terminated by newline/
alias on its own gives a list of existing aliases.

SQL>REM loaded on demand and saved on exit (sdsql only). WARNING - two sessions - last to exit will overwrite.
SQL>REM show current aliases
SQL> alias
locks
tables
SQL> alias mysql=select 'mysql' from dual;
SQL> mysql
Command=mysql
'MYSQL'
-------
mysql

SQL> set serveroutput on
SQL> alias myplsql=begin
  2  dbms_output.put_line('some output');
  3  end;
  4  /
SQL> myplsql
Command=myplsql
anonymous block completed
some output
SQL>REM plsql (given above - note abusing / a little - call it 'executing a plsql alias assignment command'), q string or bind
SQL>REM q string:(Complex option 1: for use by the 1% of use cases with multiple commands)
SQL> alias mysql=q'[select 'mysql' from
  2  dual]';
SQL> mysql
Command=mysql
'MYSQL'
-------
mysql
SQL>REM bind: (Complex option 2: for use by the 0.1% of use cases that require preprocessing of alias text or other 'maximum' flexibility)
SQL> variable bind clob;
SQL> begin :bind:='select ''frombind'' from dual'; end;
  2  /
anonymous block completed
SQL> alias mysql=:bind;
SQL> mysql
Command=mysql
'FROMBIND'
----------
frombind
SQL>REM using bind: 
SQL>  alias mysql2=select :vv from dual;
SQL> mysql2 'hello vv'
Command=mysql2
:VV
--------------------------------
hello vv
SQL> REM You can use existing or prompted for substitution variables - arguments are all (new) binds
SQL> REM obvious usecase capture sql from a live job, run unchanged as an alias using the argument to bind facility. 
SQL> REM running from a clob bind variable opens up lots of possibilities 1/ download a script from a database and run it 2/ run without accessing the file system 3/dynamically change (preprocess) the bind value (equivalent of #ifdef, or changing strings) before creating the alias.

EDIT1: Warning around infrequent higher flexibility use cases.
EDIT2: The bind arguments are assumed to be varchar2 (as opposed to numeric or date for example)
- automatic type conversion done by the sql layer
- use TO_DATE(:thevarchar,'the_date_mask') if you prefer explicit conversion.