I’m always looking for things. I love the ALT+G trick in SQL Developer.

But what about the command-line?

One of the really nice things about SQLcl is that if you can’t do what you want with the available commands, you can just build your own. That’s especially true now that we have JavaScript support.

But this is just plain, straight up SQL.

To build your own command, use ‘ALIAS’.

SQL> alias fuzzy=SELECT owner, object_name, object_type FROM dba_objects
WHERE object_name LIKE :name
ORDER BY 1,2,3
fetch FIRST 10 ROWS ONLY;

You can probably guess what this does.

You pass the binds in in the same order they appear in your query.
You pass the binds in in the same order they appear in your query.

So, pretty easy, yeah?

And if you forget what your ALIAS is, just do, ALIAS LIST ALIAS_NAME.

Or do ALIAS LIST to see them all.

SQL> alias list
 
fuzzy
locks
SEARCH
sessions
TABLES
tables2

Or do HELP ALIAS to get a refresher.

SQL> help alias
 
ALIAS
------
 
alias [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [NAME] | DROP ]
 
Alias IS a command which allows you TO save a SQL, plsql OR sqlplus script AND assign it a shortcut command.
        "alias" - print a list OF aliases
        "alias list <aliasName>" - list the contents OF the alias
        "alias <aliasName>=select :one from dual;" - Simple alias command
        Define an alias simply BY USING the alias keyword followed BY a single identifier
        name followed BY an '='. Anything after the '=' will be used AS the alias contents.
        FOR example, IF it IS SQL, it will be TERMINATED BY a ';'. IF it IS PLSQL, it will
        be TERMINATED BY a '/'
 
Examples:
--------
1. SQL Example WITH Binds
 
        SQL> alias fred=SELECT :one FROM dual;
        IN this example, a bind variable can be SET up IN the alias.
        Running the alias IS done LIKE this below WITH any parameters
        TO the alias being bound TO a bind variable BY SQLcl
 
        SQL> fred 1
        Command=fred
        :ONE
        ----
        >
 
2. PL/SQL example
        SQL> alias db= BEGIN dbms_output.put_line('hi'); END;
        >  /
        Here the block OF PLSQL IS TERMINATED BY the '/' ON a separate
        line at which point it IS accepted AS a NEW alias.
 
        SQL> db
        Command=db
        PL/SQL PROCEDURE successfully completed.
        hi
 
Summary
-------
        alias ..=.. IS TERMINATED BY ';'
        alias ..=BEGIN OR ..=DECLARE IS TERMINATED BY a / ON a newline
        alias ON its own gives a list OF existing aliases.
SQL>

We ship with a very few simple ones out of the box, but I’m hoping the clever folks out there will come up with some real humdingers.

And about SQL Developer…

Our script engine in SQLcl and SQL Developer are essentially the same. And actually, they are exactly the same. It’s the same code. Now, while it might not make sense to do things in SQL Developer that you do in SQLcl, you theoretically CAN.

an alternative to sql code templates perhaps
an alternative to sql code templates perhaps

While the alias command works, they’re not stored in SQLDev for use in the next application startup…

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

26 Comments

  1. hi Jeff,
    I’m trying to create an alias for some sql that has pl/sql in the with clause. The alias terminates on the first semicolon – do you have any trick for this, without creating a view?

    super simple example:

    alias jeff=with
    function funcy return number is
    begin
    return 123;
    end;
    select funcy from dual;
    /

    alias list jeff

    jeff
    —-

    with
    function funcy return number is
    begin
    return 123

  2. Rajeshwaran Jeyabal Reply

    Is that the “alias list” got broken in the latest version (22.2.1) of SQLCL connecting to Oracle database 21c (21.3)
    getting this below error.


    demo@PDB1> alias list
    2022-09-22 17:56:44.602 SEVERE oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run java.base/java.util.Formatter$FormatSpecifier.index(Formatter.java:2888)
    java.util.IllegalFormatArgumentIndexException: Illegal format argument index = 0
    at java.base/java.util.Formatter$FormatSpecifier.index(Formatter.java:2888)
    at java.base/java.util.Formatter$FormatSpecifier.(Formatter.java:2959)
    at java.base/java.util.Formatter.parse(Formatter.java:2827)
    at java.base/java.util.Formatter.format(Formatter.java:2751)
    at java.base/java.util.Formatter.format(Formatter.java:2705)
    at java.base/java.lang.String.format(String.java:4138)
    at oracle.dbtools.raptor.newscriptrunner.commands.alias.AliasCommand.searchForToken1Group(AliasCommand.java:366)
    at oracle.dbtools.raptor.newscriptrunner.commands.alias.AliasCommand.handleEvent(AliasCommand.java:198)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:334)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:227)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:355)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:238)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:989)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1173)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:1349)

    demo@PDB1> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.1.0 build: 22.2.1.201.1456
    demo@PDB1>
    demo@PDB1>

    • It’s indeed a bug – apparently particular to Java 17. So if you switch down to 11, it should be just fine as a workaround.

  3. Does alias work where parameters are to be without quotes ?
    SQL>alter session set container=contname ;
    or
    SQL> connect user/pass@string

    For me it doesn’t work and I noticed two more asked Qs in this thread on similar lines…without solution.

  4. Hi,
    What am I doing wrong ?
    alias sel_am=select * from a.am;
    SQL> sel_am
    Error at line : 1 column:21 File @\ALIAS\sel_am
    Error report :
    SQL error : ORA-00942-“Table or view does not exist”

    Thanks.

  5. Aamir Haroon Reply

    What am I doing wrong?

    SYS@CDB$ROOT>show pdbs
    CON_ID CON_NAME OPEN MODE RESTRICTED
    _________ ___________ ____________ _____________
    2 PDB$SEED READ ONLY NO
    3 PDB1 MOUNTED

    SYS@CDB$ROOT>alias list setc
    setc
    —-

    alter session set container = :one

    SYS@CDB$ROOT>setc pdb1
    SP2-0158: unknown SET option “pdb1”

  6. Ron Harvey Reply

    Thanks for the ALT-G tip. I’m always looking for cool tips like that.
    You mentioned that the alias command works in SQL Developer but are not stored in SQLDev for use in the next application startup. I didn’t see mentioned though, the cool fact that you can create an alias in SQLcl and then use that alias inside of SQL Developer. You might have to restart SQL Developer for it to recognize any new alias, but still way cool that when working in SQL Developer, you don’t have to switch back to SQLcl to run a quick alias that was defined and stored there.

  7. Enrico Corini Reply

    I try your example… but my output is:
    [email protected]:1521/SPECTRA > alias db= BEGIN dbms_output.put_line(‘hi’); END;
    2 /
    [email protected]:1521/SPECTRA > db
    Procedura PL/SQL completata correttamente.
    [email protected]:1521/SPECTRA > show serveroutput
    serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

    Why i don’t prompt “hi”?
    What am I doing wrong?
    Thanks,
    Enrico

    • Doesn’t look like you’re doing anything wrong…I’ll be back on the 2nd to take a look.

  8. Enrico Corini Reply

    hi jeff,

    If i execute:

    > alias list tables
    tables – tables – show tables from schema
    ————————————————–

    select table_name “TABLES” from user_tables

    How can I enter a description even in the aliases entered by me?

    Thanks,
    Enrico

    • I am HR on orcl > alias example=select 'example' from dual;
       
      I am HR on orcl > alias desc example 'this is an example'
      I am HR on orcl > alias list example
      example - desc example 'this is an example'
       
      --------------------------------------------
       
      select 'example' from dual
  9. Hi Jeff,
    The custom alias tables2 at times is throwing errors and exiting from sqlcl.
    The headings in the definition also do not work.

    SQLcl: Release 4.2.0 Production on Wed Mar 08 04:29:52 2017

    set sqlformat
    SQL Format Cleared
    SQL> tables2
    SP2-0158: unknown COLUMN option “,999,999,999”
    SP2-0158: unknown COLUMN option “,999,999”
    SP2-0158: unknown COLUMN option “,990.999”

    Tables
    ======
    Exception in thread “main” java.lang.NoSuchMethodError: oracle.sql.Datum.isNull()Z
    at oracle.dbtools.db.SQLPLUSCmdFormatter.executeComputeSQL(SQLPLUSCmdFormatter.java:5964)
    at oracle.dbtools.db.SQLPLUSCmdFormatter.computedData(SQLPLUSCmdFormatter.java:5587)
    at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:841)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:262)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:236)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:137)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:65)
    at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:734)
    at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:645)
    at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:71)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:364)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:331)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:221)
    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runAliases(SQLPLUS.java:4308)
    at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:138)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:380)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:244)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:331)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:221)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:351)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:359)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1069)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:431)

    regards

  10. SQL Developer can connect to mssql & mysql using custom jars, and i was wondering if sqlcl has the same flexibility. Can you please provide input?

    Thanks,
    Durga

  11. Tommy Wareing Reply

    SQL Developer (4.1.1.19) appears to support the ALIAS command in the same way, including the same help text.
    But including a placeholder in the alias definition (eg the select :one from dual; example) immediately produces the Enter Binds dialogue, rather than waiting until the alias is invoked (or accepting them from the invocation line, as in your fuzzy example).
    I’ll come back once I’ve got 4.1.2 installed ๐Ÿ™‚

    • i don’t think the alias command is really setup to be exploited in SQL Developer itself, so stay tuned

    • Tommy Wareing

      I can understand that ๐Ÿ™‚ 4.2 behaves the same as 4.1.19, as far as I can see.
      But since SQL Developer has the help text which says these things work, and then these things don’t work, there’s a bug, even if it’s just a documentation problem.

      However, even without the bind variables, I’m already liking it, to replace the static code templates I was using (which replaced the snippets).

  12. Hi Jeff, Just want to tell you how I appriciate your blog. So much information that one could put to good use . It’s just amazing , this post is just another example ! I see a good application for it in my “impact surface module” that I want to create. Which basically tells someone if you change this you need to take care of that table, that report that view ….

    I just one crazy wish is that you make sqlcl portable to the other rdbms big players like mssql and mysql. Just the export capabilities to json , xml and so forth are so nice and easy to use that it’s no brainer for me If you do it this will replace all my command command line tools for sure.

    Thank you !!

    Ricardo

    • Ah thanks Ricardo, appreciate that!

      Your wish is a little bit crazy…making MSFT easier to use isn’t really our core mission, and getting this just right for Oracle users is tough enough!

    • I know I know ๐Ÿ™‚ that’s why I called it my “crazy” wish.

      But with SQL Developer you do it already and at my work I can enjoy one IDE to handle my dba tasks for the enterprise mysql , mssql and oracle databases ! and for more on that I have created connections template so that our dev / qa users have all they need in a glance. ( mac and pc users )

      Like I said I’m a fan of what you do …..

      Thanks again

  13. Is there something like a common Loginscript (login.sql) for both sqlcl + sqldeveloper?

    Cheers, Markus

    • not in the sense you mean but you can in affect have them both use the same file, just use the SQLDev preferences to point to the login you have for SQLcl

Write A Comment