SQLcl Aliases With Variables

Aliases in SQLcl allow you to create quick shortcuts for frequently used queries.

SQL: > alias emps=select empno,ename
  2    from emp
  3   order by empno
  4* /
SQL: > emps

   EMPNO ENAME
________ _________
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7934 MILLER

11 rows selected

To add parameters, use bind variables;

SQL: > alias emps=select empno,ename
  2    from emp
  3   where ename = :ENAME
  4   order by empno
  5* /
SQL: > emps KING

   EMPNO ENAME
________ ________
    7839 KING

Use wildcards with parameters:

SQL: > alias emps=select empno,ename
  2    from emp
  3   where ename like '%'||upper(:ENAME)||'%'
  4   order by empno
  5* /
SQL: > emps ki

   EMPNO ENAME
________ ________
    7839 KING

SQL: > emps %

   EMPNO ENAME
________ _________
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7934 MILLER

11 rows selected.

Use multiple parameters:

SQL: > alias emps=select empno,ename
  2    from emp
  3   where job like '%'||upper(:JOB)||'%'
  4     and ename like '%'||upper(:ENAME)||'%'
  5   order by empno
  6* /
SQL: > emps clerk a

   EMPNO ENAME
________ ________
    7876 ADAMS
    7900 JAMES

To preserve your aliases, place them into your login.sql file, located in a directory specified in SQLPATH. If you don’t have SQLPATH specified in your environment, add it to you environment profile:

export SQLPATH=~/sql