Monthly Archives: June 2016

SQLcl – do REPEAT yourself

In previous blog posts I’ve covered some of the useful new commands available in SQLcl.

One extremely useful enhancement is the ability to rerun (repeat) the last command, which can be used to monitor changes in another window while you perform work in your main window.

For example, let’s say – as a DBA – I want to monitor how many users are logged into the database.

Firstly lets logging as SYS (yes this is a local database – obviously use a less privileged account in production).


bash-3.2$ ./sql sys@localhost:11521:xe as sysdba

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 17 18:42:12 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

 

SQL>

Now lets run our command –


SQL> select username,
2 count(*)
3 from v$session
4 group by username;

USERNAME COUNT(*)
------------------------------ ----------
                                       22
APEX_PUBLIC_USER                        1 
SYS                                     1

As you can tell, I’m running APEX in my DB (hence the single APEX_PUBLIC_USER session).

Now let’s say we want to continuously run that command, you just use the REPEAT command –


SQL> help repeat
repeat <iterations> <sleep>
Repeats the current sql in the buffer the specified times with sleep intervals
Maximum sleep is 120s

So, let’s say I want to run it 5 times, with an interval of 1 second –


SQL> repeat 5 1

This will run the previous command 5 times (with 1 second between each run), and you should see output similar to –


Running 5 of 5 @ 18:51:11.900 with a delay of 1s

USERNAME      COUNT(*)
                    22
APEX_PUBLIC_USER     1
SYS                  1

I’v e used this many times to monitor APEX instances which I suspect are getting a lot of web requests, it’s also useful to check things like V$SQL during tuning sessions. The ability to be able to re-run a command at a scheduled interval is such a simple feature and yet invaluable (I’ve lost count of the number of times I’ve manually done that with SQLPlus scripts)

SQLcl – Using SQLFORMAT

One of the new settings in SQLcl is the ability to set different output formats when running queries. You can access this via the set command –


SQL> help set
SET
---

Sets a system variable to alter the SQLcl environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

help set <setting> for more details

APPINFO
ARRAYSIZE
AUTOCOMMIT
AUTOPRINT
AUTORECOVERY
AUTOTRACE

BLOCKTERMINATOR
CLEAR
CMDSEP
COLSEP
CONCAT
COPYCOMMIT
COPYTYPECHECK
DEFINE
ECHO
EDITF[ILE]
EMBEDDED
ENCODING
ESCAPE
ESCCHAR
EXITCOMMIT
FEEDBACK
FLUSH
HEADING
HEADSEP
LDAPCON
LINESIZE
LONG

LONGCHUNKSIZE
NET
NEWPAGE
NOVERWRITE
NULL
NUMFORMAT
NUMWIDTH
PAGESIZE
PAUSE
RECSEPCHAR
SERVEROUTPUT
SHOWMODE
SQLBLANKLINES
SQLCASE
SQLCONTINUE
SQLFORMAT  <------------ Here it is!
SQLPLUSCOMPATIBILITY
SQLPREFIX
SQLPROMPT
SUFFIX
TAB
TERMOUT
TIME
TIMING
TRIMOUT
TRIMSPOOL
VERIFY
WRAP

Viewing the command specific help shows the available output formats


SQL> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

So, let’s try a couple of tests against the ubiquitous emp table, firstly lets query it without making any changes to the SQLFORMAT settings

 

We can view what the current setting is via the show command


SQL> show sqlformat
SQL Format : Default

Let’s try the JSON format

 

As you can imagine the CSV format does exactly what you’d expect

You can imagine the previous formats are very useful combined with a SPOOL command to save the data to a file on the filesystem in a particular format.

The most common format I use is ANSICONSOLE (which works very well with a large monitor), which does a great job at auto-sizing the column sizes and always displaying the full column name (instead of a shorten abbreviated name).

As you can see, using SQLFORMAT is an extremely useful way to format your SQL query output

SQLcl – Using Aliases

In the last post we covered the basics of connecting to a database with SQLcl.

If you’re familiar with Unix, you’ll be familiar with the history command. SQLcl also has a history command and once you start to use it you’ll wonder why sqlplus didn’t have one added years ago (how many times have you mistyped a select statement?).

Once you’ve connected to a database, typing ‘help’ will list the available commands, and you can see the alias command listed.

The general format of the command is


ALIAS
------

alias [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [<NAME>] |
DROP <name> | DESC <name> <Description String>]

Typing alias by itself will list any previously defined aliases


SQL> alias
locks
sessions
tables
tables2
SQL>

You can see I have some aliases already defined, but lets create a new one. One thing I commonly do on remote databases is check what the server time(zone) is. I frequently find myself typing the command –


select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual

so, let’s create an alias for that –


SQL> alias time=select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;
SQL>

Now we just need to type ‘time’ and it will run the full command –


SQL> time

TO_CHAR(SYSDATE,'DD
-------------------
16/06/2015 19:43:55

Such a simple thing, but so incredibly useful.

I also often find myself returning to a SQLPlus session and wondering which user I’m logged in as – now with SQLcl I can easily create an alias (called whoami to simulate the Unix command), like this –


SQL> alias whoami=select user from dual;
SQL> whoami

USER
------------------------------
JES

If you want these aliases to persist between database connections, then I can save my current aliases like this –

 


SQL> alias save aliases.sqlcl
ALIAS-007 - Aliases saved to aliases.sqlcl

and then reload them in a future session


SQL> alias load aliases.sqlcl
Aliases Loaded

it’s worth noticing that if you examine the file, it is stored in XML format –


<?xml version = '1.0' encoding = 'UTF-8'?>
<aliases>
<alias name="whoami">
<description/>
<queries>
<query>
<sql><![CDATA[select user from dual]]></sql>
</query>
</queries>
</alias>
<alias name="sel">
<description/>
<queries>
<query>
<sql><![CDATA[select :one]]></sql>
</query>
</queries>
</alias>
<alias name="time">
<description/>
<queries>
<query>
<sql><![CDATA[select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual]]></sql>
</query>
</queries>
</alias>
</aliases>

I can remove any aliases I want using the drop command –


SQL> alias drop whoami
Alias whoami dropped

The (very) cool thing is that you’re not just limited to SQL aliases, you can alias PL/SQL too.

Connecting to a database with SQLcl

In the last post I covered downloading and running SQLcl, now let’s try and connect to a database.

I have an Oracle XE database running on my local machine, so lets try and just enter the username and password of a valid schema to connect to that.


bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016</span>

Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?) jes
Password? (**********?) **********
USER = pre3_prod
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connection

ok, so that didn’t work…

The issue here is that it defaults to localhost and port 1521 (which is where my instance is running), but the service name it defaults to (orcl) isn’t valid.

Ok, so let’s try again this time passing the correct parameters


bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016
 
Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?) jes@localhost:1521/xe
Password? (**********?) **********
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

Great, success!

Actually SQLcl supports a number of different options when it comes to connection strings, namely –

  • jdbc:oracle:thin
  • jdbc:oracle:oci8
  • jdbc:oracle:kprb
  • jdbc:default:connection
  • jdbc:oracle:kprb:
  • jdbc:default:connection:

So now we’re connected let’s explore the available commands using the help command


SQL> help
For help on a topic type help <topic>
List of Help topics available:
/ @ @@ ACCEPT ALIAS APEX
ARCHIVE_LOG BREAK BRIDGE BTITLE CD CHANGE
COLUMN COMPUTE CONNECT COPY CTAS DDL
DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT
GET HISTORY HOST INFORMATION INPUT LIST
NET OERR PASSWORD PAUSE PRINT PROMPT
REMARK REPEAT RESERVED_WORDS REST RUN SAVE
SET SHOW SHUTDOWN SODA SPOOL SSHTUNNEL
STARTUP STORE TIMING TNSPING TTITLE UNDEFINE

this hasn’t formatted very well in this code editor, but here’s a screenshot

In the next post, we’ll explore the ALIAS command.

Getting started with SQLCL

I blogged briefly before here and here about SQLcl and thought I’d write a walkthrough of using it while I explored the various features.

Firstly how do you install it? Well head over to the download page here.

You should get a zipfile (I renamed the file for brevity) –

 
bash-3.2$ ls -al 
total 34120 
drwxr-xr-x 3 jes staff 102 14 Jun 19:09 . 
drwxr-xr-x+ 76 jes staff 2584 14 Jun 19:09 .. 
-rw-r--r--@ 1 jes staff 17466359 14 Jun 19:09 sqlcl.zip 

unzipping this extracts quite a few files

 
bash-3.2$ unzip sqlcl.zip
Archive: sqlcl.zip
 inflating: sqlcl/bin/sql
 inflating: sqlcl/bin/sql.bat
 inflating: sqlcl/bin/sql.exe
 inflating: sqlcl/lib/SQLinForm.jar
 inflating: sqlcl/lib/commons-codec.jar
 inflating: sqlcl/lib/commons-logging.jar
 inflating: sqlcl/lib/httpclient.jar
 inflating: sqlcl/lib/httpcore.jar
 inflating: sqlcl/lib/httpmime.jar
 inflating: sqlcl/lib/jackson-annotations.jar
 inflating: sqlcl/lib/jackson-core.jar
 inflating: sqlcl/lib/jackson-databind.jar
 inflating: sqlcl/lib/javax.json.jar
 inflating: sqlcl/lib/jline.jar
 inflating: sqlcl/lib/jsch.jar
 inflating: sqlcl/lib/ojdbc7.jar
 inflating: sqlcl/lib/oracle.dbtools-common.jar
 inflating: sqlcl/lib/oracle.dbtools.http.jar
 inflating: sqlcl/lib/oracle.dbtools.jdbcrest.jar
 inflating: sqlcl/lib/oracle.sqldeveloper.sqlcl.jar
 inflating: sqlcl/lib/oraclepki.jar
 inflating: sqlcl/lib/orai18n-mapping.jar
 inflating: sqlcl/lib/orai18n-utility.jar
 inflating: sqlcl/lib/orai18n.jar
 inflating: sqlcl/lib/orajsoda.jar
 inflating: sqlcl/lib/osdt_cert.jar
 inflating: sqlcl/lib/osdt_core.jar
 inflating: sqlcl/lib/xdb6.jar
 inflating: sqlcl/lib/xmlparserv2.jar

this should have created a sqlcl sub-folder in the directory you unzipped the file in

 
bash-3.2$ ls -al
total 34120
drwxr-xr-x 4 jes staff 136 14 Jun 19:37 .
drwxr-xr-x+ 76 jes staff 2584 14 Jun 19:09 ..
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 sqlcl
-rw-r--r--@ 1 jes staff 17466359 14 Jun 19:09 sqlcl.zip

inside the sqlcl folder you’ll find a bin and lib folder

 
bash-3.2$ cd sqlcl
bash-3.2$ ls -al
total 0
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 .
drwxr-xr-x 4 jes staff 136 14 Jun 19:37 ..
drwxr-xr-x@ 5 jes staff 170 14 Jun 19:37 bin
drwxr-xr-x@ 28 jes staff 952 14 Jun 19:37 lib

in the bin directory you will find the sql command

 
bash-3.2$ cd bin
bash-3.2$ ls -al
total 840
drwxr-xr-x@ 5 jes staff 170 14 Jun 19:37 .
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 ..
-rwxr-xr-x@ 1 jes staff 13781 14 Jun 19:37 sql
-rwxr-xr-x@ 1 jes staff 2585 14 Jun 19:37 sql.bat
-rwxr-xr-x@ 1 jes staff 406507 14 Jun 19:37 sql.exe

you’ll notice there’s a sql command, a sql.bat and a sql.exe file (since this is a single download regardless of operating system).

Since I’m on a Mac I can just invoke the sql command

 
bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016</span>

Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?)

and we’re good to go! in the next post I’ll connect to a database and explore some of the commands.

APEX Integration with SQLCl

Oracle SQLcl contains some very useful built in commands that APEX developers might find useful.

For example, lets say I’m connected to a schema and want to know what APEX applications are installed in the workspace linked to that schema, I can simply issue the APEX command and it will list the APEX applications, for example:

SQL> apex
WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON
JES_DEMO 105 LDAP Demo Run and Develop 12-JUN-16
JES_DEMO 280 Amazing jQuery Run and Develop 12-JUN-16

Let’s say you want to export an application from the command line, we can use the APEX EXPORT command like this:

SQL> spool f105.sql
SQL> apex export 105

...application spools out

Now I can do cool things like automate exports from my application using cron, how cool is that?

I see a lot of potential in SQLCl and it keeps improving with each release, go and check it out!

APEX 5.1 Early Adopter 1 Available

After a long (but I’m sure well worth it) wait, Oracle just announced APEX 5.1 Early Adopter is available.

Head over to https://apexea.oracle.com/ to sign up and request a Workspace.

The two big items everyone has been waiting for are

  • Interactive Grids
  • Jet Charts

but reading through the “Additional Features in Application Express 5.1” section on the EA homepage it looks like there are lots of other interesting improvements and enhancements.