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.
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.
While the alias command works, they’re not stored in SQLDev for use in the next application startup…
26 Comments
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
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.
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.
No, it would need to be in a SQL statement as a :bind or as a static pl/sql block
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.
Well, does select * from a.am; work?
Yes it does.
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”
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.
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.
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
Thank you very much!
😀
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
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
Not today but we’re considering it.
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
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).
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
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