Is SQLcl the Next Generation of SQL*Plus?

Session from ILOUG I presented in May, 2016

Introducing the new tool from the developers of SQL Developer: SQLcl – a new command line tool from the SQL Developer team that might replace SQL*Plus and all of its functions which has been around for over 30 years!
In this session, we will explore the new functionality of the SQLcl, and use a live demonstration to show what SQLcl has to offer over the old SQL*Plus. We will use real life example to see what makes this tool such a time saver in day-to-day tasks for DBAs and developers who prefer using the command line interface.

1. Zohar Elkayam www.realdbamagic.com Twitter: @realmgic Is SQLcl the Next Generation of SQL*Plus?

2. Who am I? Zohar Elkayam, CTO at Brillix DBA, team leader, database trainer, public speaker, and a senior consultant for over 18 years Oracle ACE Associate Involved with Big Data projects since 2011 Blogger – www.realdbamagic.com and www.ilDBA.co.il http://brillix.co.il 2

3. About Brillix Brillix is a leading company that specialized in Data Management We provide professional services and consulting for Databases, Security, NoSQL, and Big Data solutions Providing the Brillix Big Data Experience Center 3

4. Agenda SQL*Plus and what is it good for Introducing SQLcl Installing and Using SQLcl SQLcl cool features and a demo Q&A http://brillix.co.il 4

5. SQL*Plus Introduced in Oracle 5 (1985) Looks very simple but has tight integration with other Oracle infrastructure and tools Very good for reporting, scripting, and automation Replaced old CLI tool called … UFI (“User Friendly Interface”) http://brillix.co.il 5

6. What’s Wrong With SQL*Plus? Nothing really wrong with SQL*Plus – it is being updated constantly but it is missing a lot of trivial functionality SQL*Plus forces us to use GUI tools to complete some basic tasks Easy to understand, a bit hard to use Not easy for new users or developers http://brillix.co.il 6

7. Introducing: SQLcl SQLcl is a new command line interface (CLI) for SQL developers, report users, and DBAs It is part of the SQL Developer suite – developed by the same team: Oracle Database Development Tools Team Does (or will do) most of what SQL*Plus can do, and much more Main focus: making life easier for CLI users Minimal installation, minimal requirements http://brillix.co.il 7

8. Current Status (May 2016) Still in Early Adopter version current version: 4.2.0.16.131.1023 RC, May 13, 2016 QA is still logging bugs from SQL*Plus regression tests Version comes out every couple of months Adding support for existing SQL*Plus commands/syntax Adding new commands and functionality The team is accepting bug reports and enhancement requests from the public http://brillix.co.il 8

9. Prerequisites Very small footprint: 12MB Tool is Java based so it can run on Windows, Linux, and OS/X Java 7/8 JRE (runtime environment - no need for JDK) No need for installer or setup No need for any other additional software or special license No need for Oracle Client http://brillix.co.il 9

10. Installing Download from: SQLcl Developer tool OTN Page Unzip the file Verify has +x if in Linux/OSX Run it http://brillix.co.il 10

11. Running SQLcl http://brillix.co.il 11

12. Connecting to the Database When no Oracle Client: Using thin connection: EZConnect connect style out of the box connect host:port /service Support TNS, Thick and LDAP connection when Oracle home detected Auto-complete connection strings from last connections AND tnsnames.ora http://brillix.co.il 12

13. Object Completion and Easy Edit Use the tab key to complete commands Can be used to list tables, views or other queriable objects Can be used to replace the * with actual column names Use the arrow keys to move around the command Use CTRL+W and CTRL+S to jump to the beginning/end of commands http://brillix.co.il 13

14. Command History 100 command history buffer Commands are persistent between sessions (watch out for security!) User SET NOHISTORY to blacklist commands to not go into history Use UP and DOWN arrow keys to access the old commands Usage: history history usage h istory time history script history full history clear [session?] Load from history into command buffer: history <number> http://brillix.co.il 14

15. Describe, Information and Info+ Describe lists the column of the tables just like SQL*Plus Information shows column names, default values, indexes and constraints. In 12c database information shows table statistics and In memory status Works for table, views, sequences, and code objects Info+ shows additional information regarding column statistics and column histograms http://brillix.co.il 15

16. SHOW ALL and SHOW ALL+ The show all command is familiar from SQL*Plus – it will show all the parameters for the SQL*Plus settings The show all+ command will show the show all command and some perks: available tns entries, list of pdbs , connection settings, instance settings, nls settings, and more! http://brillix.co.il 16

17. Repeat Repeats the current SQL or PL/SQL in the buffer the specified number of times with specified sleep intervals Looks like the “watch” command in Linux Usage: repeat <iterations> <sleep> http://brillix.co.il 17

18. Repeat as “tail -f alert.log” Jeff Smith (@ thatjeffsmith ) had a cool implementation for the repeat command on Oracle 12c: tail –f on the alert log http://brillix.co.il 18 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;

19. DDL and DBMS_METADATA Extract DDL of objects using a single command DDL <object name > Uses DBMS_METADATA.GET_DDL to extract the object We can modify the output using SET DDL: http://brillix.co.il 19 SET DDL SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS | CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING | SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION | BODY | FORCE | INSERT | |INHERIT | RESET] { on|off } ]| OFF ]

20. OERR OERR shows an error message just like the oerr in the command line (in *nix systems) http://brillix.co.il 20 SQL> oerr ora -0 00000. 00000 - "normal, successful completion" *Cause: Normal exit. *Action: None. SQL> oerr ora -1 00001. 00000 - "unique constraint (% s.%s ) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

21. Alias Alias is a command which allows us to save a SQL, PL/SQL or SQL*Plus scripts, and assign it a shortcut command . Command can receive parameters using bind variables Aliases are session persistent and being saved Usage: a lias - for list of aliases a lias list < aliasName > - for definition Setting an alias: a lias my_command = sql ; (terminate with ;) a lias my_code =begin command; end; (terminate with /) http://brillix.co.il 21

22. Alias Example We’re tired of running the dbms_xplan after we explain a query So we set this alias: http://brillix.co.il 22 alias plan=select * from table( dbms_xplan.display ); SQL> explain plan for select * From dual; Explained. SQL> plan PLAN_TABLE_OUTPUT ---------------------------------------------------------------Plan hash value: 272002086

23. CTAS Extract DDL for a table and use it to recreate another table using select * from Useful when we want to copy a table with partitions or a primary key Might not stay in the final version – have a lot of small and annoying issues Usage: ctas table new_table http://brillix.co.il 23

24. SQLPATH SQLPATH is the order in which the CLI is looking for sql scripts (both in SQL*Plus and SQLcl) Running a script not from the path requires full path location Default search order is: Current cd directory Current running directory The rest of the sqlpath Show SQLPATH will show that current search path http://brillix.co.il 24

25. CD command When we want to change the path in SQL*Plus , we usually can’t . SQLcl comes with CD command to change that path and make it easier to run scripts: Usage : cd /u01/app/oracle/scripts Show SQLPATH http://brillix.co.il 25

26. Bridge Used mainly to script data move between two connections from the client (no server direct server connections) The following functionality is available: Query tables in other connections Query tables in multiple connections in the same statement I nsert data from one connection into another Create a table and insert data into it from another connection http://brillix.co.il 26

27. Bridge (cont.) Uses JDBC connection string to connect our client to the remote connection. Creates a table with the results in the database Usage: BRIDGE < targetTableName > as "< jdbcURL >"(< sqlQuery >); Example: http://brillix.co.il 27 BRIDGE dept_remote as “ jdbc:oracle:thin:scott / tiger@localhost:1521 / orcl "(select * from dept );

28. Pretty Input Using the SQL Developer formatting rules, it will change our input into well formatted commands. Use the SQLFORMATPATH to point to the SQL Developer rule file (XML) http://brillix.co.il 28 SQL> select * from dual; D - X SQL> format buffer; 1 SELECT 2 * 3 FROM 4* dual

29. SQL*Plus Output SQL*Plus output is generated as text tables We can output the data as HTML but the will take over everything we do in SQL*Plus (i.e. describe command) We can’t use colors in our output We can’t generate other types of useful outputs (CSV is really hard for example) http://brillix.co.il 29

30. Generating Pretty Output Outputting query results becomes easier with the “set sqlformat ” command (also available in SQL Developer) We can create a query in the “regular” way and then switch between the different output styles: ANSIConsole Fixed column size output XML or JSON output HTML output generates a built in search field and a responsive html output for the result only http://brillix.co.il 30

31. Generating Other Useful Outputs We can generate loader ready output (with “|” as a delimiter) We can generate insert commands We can easily generate CSV output Usage: set sqlformat { csv,html,xml,json,ansiconsole,insert , loader,fixed,default } http://brillix.co.il 31

32. Load Data From CSV File Loads a comma separated value (csv) file into a table The first row of the file must be a header row and the file must be encoded UTF8 The load is processed with 50 rows per batch Usage: LOAD [schema.] table_name [@ db_link ] file_name http://brillix.co.il 32

33. SCRIPT SQLcl exposes javascript scripting with nashorn to make things very scriptable This means we can create our own commands inside SQLcl using JavaScript For more information (and DEMO), check out Kris Rice’s blog (product manger for SQLcl): http:// krisrice.blogspot.com/ http://brillix.co.il 33

34. Q&A http://brillix.co.il 34

35. Conclusion We talked about SQL*Plus and SQLcl differences We saw how to start working with SQLcl We tried some of its new features There are more new features coming and since it's and EA version, more bug fixes to come I’ve been using this tool for over a year now – it’s just getting better all the time! http://brillix.co.il 35

36. What Did We Not Talk About? Official release date (but rumer says it will be shipped out with 12cR2 The SQLcl is very open to ideas – check them out Bug reporting can be done on the community page: https:// community.oracle.com/community/database/developer-tools/sql_developer/sqlcl http://brillix.co.il 36

37. Useful Resources SQLcl OTN page: http:// www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html SQLcl Community page: https ://community.oracle.com/community/database/developer-tools/sql_developer/sqlcl Kris Rice ( @ krisrice ) manage Oracle SQL Developer team Blog: http:// krisrice.blogspot.com/ Jeff Smith ( @ thatjeffsmith ) Oracle SQL Developer Product Manager at Oracle Blog: http://www.thatjeffsmith.com / http://brillix.co.il 37

38. Thank You Zohar Elkayam twitter: @ realmgic [email protected] www.realdbamagic.com http://brillix.co.il 38