Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. This was developed by the sql developer Team. It contains very useful features. We can say it is the advanced version of SQL* utility,

 

 

Installation:

Download the sqlcl tool from oracle website.

Download linkhttp://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

 

Copy that tool to the database server and unzip there.

To connect to database with sqlcl tool, go to sqlcl/bin location

We can create one alias also:

If JAVA_HOME is not set properly, then you can hit unsupported minor version error , while connecting with sqlcl.

export JAVA_HOME= /jdk/jdk1.8.0_102         ( for solaris) 

USEFUL FEATURES OF SQLCL:

1. HELP:

This will display all the available commands and their usage:

HELP
HELP < COMMAND >

 

2. SHOW :

This Shows the value of a SQLcl system variable, or the current SQLcl environment.

Examples:

3. HISTORY:

Setting this parameter to ON, will report all the sql commands in history, same as that of history command works at os level.

SET history ON — ( to enable history option)
SET history OFF — ( to disable history option)
SET history CLEAR — ( To clear the existing history )

4. REPEAT :

REPEAT command can be used to execute particular SQL query in the buffer for n number of times at particular interval.

example:

 

5.DDL:

DDL command is used to get the DDL of an object in databases, no need to use dbms.metadata.get_ddl utility.

DDL < OBJECT_NAME> < OBJECT_TYPE>

 

If you wish to save this DDL to a file, then SAVE keyword can be used.

 DDL <OBJECT_NAME> SAVE < FILE_NAME>

6.PASSWORD:

In the database, generally, we change the password of a user with alter user username identified password. The problem with this one is the password is displayed on the screen.

With PASSWORD command of sqlcl, we can alter password without displaying it. it will be marked as **.

 

7.INFORMATION:

INFORMATION command when used with an object_name, give details about the table structure and index, constraint information also.

INFORMATION <SCHEMA_NAME>.<OBJECT_NAME>

 

 

8. BRIDGE:

BRIDGE command works like a database link. i.e we can move data between databases without creating the database link. And also it supports LONG columns.

Creating a table NEW_TAB in local database from remote database (172.30.224.176:1540/D2RRESO)

For insert also:

 

9. ERRORLOGGING:

If errorlogging is set ON, incase we are hitting any error, It will display the explanation of that error with cause and action like oerr tool.

10. CTAS:

If we want to create a table from another table from using CTAS( with all columns or few columns,), then CTAS will come in handy.

CTAS <EXISTING_TABLE>  <NEW_TABLE> , will generate the CTAS DDL.

And when we type EDIT, the DDL will open in an editor, where we can make changes to the table structure and save it. It can be executed to create the new table.

 

The more we explore this SQLCL utility, more we get its usefulness. So connect to sqlcl prompt and type HELP and try new commands.

I will update this article after going through other commands.

 

JeffSmith really worked a lot on this tool, He publishes regularly about the updates on this sqlcl utility.  http://www.thatjeffsmith.com/