Wednesday, December 14, 2016

sqlcl improves the way we work with oracle databases from terminal

sqlcl is a command line tool for connecting and work with an oracle database from terminal. Having autocomplete, easy formatting for terminal or for export in CSV, HTML and other formats, history of commands, edit of SQL commands in external editor, edit or correct multi line commands and many many more features.

You can download it from Oracle website:

http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index-097090.html

For run it you need to extract it and put the sqlcl/bin in your system/user PATH for convenience.

the command to run it on linux is sql inside sqlcl/bin (You can use sql.bat or sql.exe on windows)

To connect with the database there are several ways. Personally i prefer to connect like this:

$ sql sys@//dbhostname:port/servicename as sysdba

The first thing to do is change the sqlformatl. You can do it using
SQL> set sqlformat ansiconsole

This will change the output of queries to be more easy to read from terminal. Try it with or without this setting and the improvements are obvious.

Using tab you can use the autocomplete for all kind of objects and commands. I found this very useful.

You can write multi line commands using the enter to separate the rows.
The nice thing is that after you wrote the command and execute it, you can edit it in vi (or the editor or your choice) from terminal without exit sqlcl and execute it again. To do this just do

SQL> edit
SQL>/

The / will execute the command you just edited with vi.

History is available using the history command
SQL> history

We can execute operating system commands using !
SQL>!ls

This will list the files from working directory

I am not a DBA and i occasionally connect and work with Databases but i loved this tool already because i enjoy working from terminal and sqlcl improves the way i work with databases from terminal. Give it a go i am sure you will enjoy with it.

You can find more information in this excellent blog:

http://krisrice.blogspot.com

And a getting started video here:

https://www.youtube.com/watch?v=HApdy-o525A





No comments:

Post a Comment