Misc
alias
sqlcl can create aliases:
rene@ORA19> alias usr=select username, account_status, default_tablespace from dba_users;
The command that such aliases is set to are then executed by simply entering the name of the alias:
rene@ORA19> usr
ddl / ctas
Use
dbms_metadata to get a table's create statement or to produce a create table as select… statement. info / info+
info and info+ describe tables and views colored output
SQL Select hints
SQLcl and SQL Developer allow to specify SQL select hints which cause a result set to be returned in a specific format:
select /*ansiconsole*/ * from …; -- Best appearance for ANSI terminal display select /*csv*/ * from …; -- Comma-separated values select /*delimited*/ * from …; -- same as csv select /*fixed*/ * from …; -- Fixed-width fields with trailing blanks select /*html*/ * from …; -- Marked-up HTML table select /*insert*/ * from …; -- Return INSERT statements select /*json*/ * from …; -- Returns JSON object format select /*loader*/ * from …; -- Pipe-delimited format suitable for SQL*Loader select /*text*/ * from …; -- Plain text select /*xml*/ * from …; -- Tagged XML
Note: these hints are not to be confused with Oracle Optimizer hints.
In SQL Developer, such a select statement needs to be executed with
F5 for the «hint» to have an effect.
See also
set sqlformat …
TODO: Compare with SQL Extensions for Oracle SQL Developer and SQLcl.
Long input lines longer than 5000 characters
The maximum line length limit of SQL*Plus of 5000 characters before and 3000 characters after subustitution (error messages SP2-0027: Input is too long (> 4999 characters) - line ignored and SP2-0341: line overflow during variable substitution) does not apply with sqlcl:
$ printf 'set verify off\ndefine num=42\n%-10000s;\nexit\n' 'select &num as num from dual' > /tmp/long-line.sql $ ~/bin/sqlcl/bin/sql rene/rene@localhost/freepdb1 @/tmp/long-line.sql
SQLcl not showing error messages
SQLcl does not automatically show error messages. (As per Ottmar Gobrecht's Blog, this problem only occurs in versions prior to 19.4 or on Windows).
rene@SYS$USERS> create or replace procedure drop_me as 2 begin 3 no_such_procedure; 4 end; 5 /
No error message shown, although
no_such_procedure does not exist. rene@SYS$USERS> show errors Errors for PROCEDURE RENE.DROP_ME: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/4 PL/SQL: Statement ignored 3/4 PLS-00201: identifier 'NO_SUCH_PROCEDURE' must be declared
Ottmar Gobrecht proposes to use the following setings until a fix is released
set define off verify off feedback on
With these settings, the errors are now displayed.