I have two last debuts from the Scotland OUG meeting – the presentations.
First, the SQLcl presentation and the demo.sql file to run the things I showed there.

I thank everybody who found the time to come hear me talk and I hope you enjoyed the presentation.

Direct access on SlideShare: http://www.slideshare.net/zohare/sqlcl-the-next-generation-of-sqlplus

Embedded:

alias cdcl='cd /u01/app/oracle/sqlcl/bin/'
cdcl

-- ## connection
sql /nolog
connect hr/hr
connect hr/hr@[tab]
connect hr/hr@192.168.56.102:1521/pdb1
show jdbc

-- ## object completion and easy edit
select * from [tab]
select * from j[tab] --> job --> [s;]
>> return to the star --> [tab] --> get column list
select job_id, job_title from jobs;
break line after from
[ctrl+s] to jump to the end of the script
run it

-- ## history
[up arrow key] scroll back in history commands
history
history [n] choose a number to load
run the command
history usage
run the command
history usage
history full -> shows multi-line commands
history clear session (doesnt work yet) but history clear

-- ## describe, info, info+
desc JOBS -> regular
info JOBS => table
info EMP_DETAILS_VIEW => show table info
info HR_SEQ => show sequence info
info ADD_JOB_HISTORY => code (procedure)

info+ JOBS => shows column statistics (if available)

-- ## repeat
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select sysdate from dual;
repeat 10 0.5

-- Jeff's example with tail -f
SELECT To_Char(Originating_Timestamp, 'DD-MON-YYYY HH24:MI:SSxFF') Time_Entry, substr(trim(message_text), 0, 75) || '...' ABBR_MESSAGE_TEXT
FROM X$dbgalertext
ORDER BY Originating_Timestamp DESC, indx desc
fetch FIRST 15 ROWS ONLY;

repeat 20 0.5

--> second window:
alter system switch logfile

--> first window changes with switch log

-- ## ddl and dbms_metadata
ddl JOBS => table
problem: no terminate at the end of command:
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
ddl JOBS => table
ddl employees => tables, indexes, triggers etc.

ddl EMP_DETAILS_VIEW => show table info
ddl HR_SEQ => show sequence info - same as info
ddl ADD_JOB_HISTORY => shows actual data types
ddl UPDATE_JOB_HISTORY => trigger

-- ## alias
alias sqlterm=exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE);
alias fix_output=begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
end;
/

alias to show aliases.

-- run aliases:
tables   => list of tables
tables2  => list of tables and size
locks 275  => locks for session 275

-- ## ctas
ctas jobs jobs_new
-- show problem with terminator at the middle of command

-- ## sqlpath
show sqlpath
@demo -> error
cd /u01/app/oracle/scripts
show sqlpath
@demo -> okay

[oracle@lnx-oracle66-db12c bin]$ cat /u01/app/oracle/scripts/demo.sql
select * from jobs where job_id = 'AC_ACCOUNT';

-- ## bridge
select * from user_db_links; --> empty, no dblinks
bridge b_dwh_11g as "jdbc:oracle:thin:zohar/zohar@192.168.56.101:1521/orcldg"(select * from zohar.dwh);
select * from b_dwh_11g; -- rows from 11g db
drop table b_dwh_11g; -- remove bridged data

-- ## output
select * from employees;
set sqlformat ansiconsole --> better formatting 

set sqlformat html
spool /tmp/emps.html 
select * from employees;
spool off
-- use firefox to open /tmp/emps.html
-- show responsiveness, show search bar (not working? **need to check it with dev team**)
set sqlformat --> clear format
select /*html*/ * from employees;

-- get data to applications:
select * from regions;
set sqlformat json 
select * from regions;
set sqlformat xml
select * from regions;
set sqlformat fixed
select * from regions;
set sqlformat loader
select * from regions;

set sqlformat insert
select * from regions;

--
-- export data as csv
set sqlformat csv
spool /tmp/jobs.csv
select * from jobs;
spool off
set sqlformat

--> edit the file and remove first lines

-- ## load
load jobs_load /tmp/jobs.csv

-- last thing if we have time:
-- color table:
CREATE TABLE "COLORS"
(    "LOOK_AT_THE_PRETTY_COLORS" VARCHAR2(100)
) ;

CREATE OR REPLACE EDITIONABLE SYNONYM "COLOURS" FOR "COLORS";

REM INSERTING into COLOURS
SET DEFINE OFF;
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|red,bold,underline This is red,bold,underline|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|NEGATIVE_ON This is negative|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|INTENSITY_FAINT This is faint|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|INTENSITY_BOLD This is my bold|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|ITALIC This is italic|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|UNDERLINE This is underline|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|BLINK_SLOW This is blink_slow|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|BLINK_FAST This is blink_fast|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|CONCEAL_ON  This is conceal|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|black This is black|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|green This is green|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|yellow This is yellow|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|blue This is blue|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|magenta This is magenta|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|cyan This is cyan|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|white This is white|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_red This is bg_red|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_black This is bg_black|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_green This is bg_green|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_yellow This is bg_yellow|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_blue This is bg_blue|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_magenta This is bg_magenta|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_cyan This is bg_cyan|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_white This is bg_white|@');
Insert into COLOURS (LOOK_AT_THE_PRETTY_COLORS) values ('@|bg_red This is bg_red|@');

set sqlformat 
select * from colours;
set ansiconsole 
select * from colours;

-- don't gorget to say it is also works with emoji in osx