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.

3 comments:

Turloch O'Tierney said...

Display the text of an alias:

SQL> alias fred=select 1 from dual;
SQL> alias list
fred
locks
tables
SQL> alias list fred
fred
----
select 1 from dual

Turloch O'Tierney said...

Notes on aliases:

-Please pass on your experience in this new feature - it can be a pretty useful swiss army knife - but there are some caveats - do not cut yourself.
-Some command (create user for example) do not like binds as arguments (in or outside aliases).
-You can convert binds to substitution variables using
column xx new_value xx noprint
select :thebind xx from dual;
-If you are using substitution (&) variables inside aliases set scan off before and set scan off when defining.
-You can use
set verify on
and
set echo on
as normal.
-If you supply the right number of binds - no error or warning
-If you supply too many binds - warning but runs
-If you supply too few binds - errors out.
-If you define binds within an alias could be problems as the search for unassigned binds does not currently take into account the variable statement - you would need to 'pretend' this bind is passed in and your bind passed in would be ignored.
-aliases do not take binds as input i.e. myalias :somebind is the same as myalias ':somebind' i.e. passes a string bind name not the contents of the bind.

Turloch O'Tierney said...

May 4th release:
Besides echoing the alias name the alias respects set term off, and other features that would apply if running a file by @thefile.sql .