Skip navigation.

Barry McGillin

Syndicate content
Oracle SQL Developer and Database Migration, with lots of core Oracle tech as well!Barry McGillinhttps://plus.google.com/103359237556179403995noreply@blogger.comBlogger79125
Updated: 7 hours 14 min ago

SQLcl - Cloud connections via Secure Shell tunnels

Sun, 2015-05-03 17:39
We're always trying to make SQLcl easier to connect to your database, whether its at your place or in the cloud.  So, one other thing we have added to enable you to drill into your cloud databases is an SSHTUNNEL command.  Lets take a look at the help for it, which you can get as follows.

SQL> help sshtunnel
SSHTUNNEL
---------

Creates a tunnel using standard ssh options
such as port forwarding like option -L of the given port on the local host
will be forwarded to the given remote host and port on the remote side. It also supports
identity files, using the ssh -i option
If passwords are required, they will be prompted for.

SSHTUNNEL <username>@<hostname> -i <identity_file> [-L localPort:Remotehost:RemotePort]

Options

-L localPort:Remotehost:Remoteport

Specifies that the given port (localhost) on the local (client) host is to be forwarded to
the given remote host (Remotehost) and port (Remoteport) on the remote side. This works by
allocating a socket to listen to port on the local side.
Whenever a connection is made to this port, the connection is forwarded over
the secure channel, and a connection is made to remote host & remoteport from
the remote machine.

-i identity_file
Selects a file from which the identity (private key) for public key authentication is read.


SQL>


So for this to work we need to decide which ports locally we are going to use and which remote machine and port we want to use to map our ports from local to remote.  We also need a RSA file from the target host.  In this example, we have created one with the default name of id_rsa.  

The format of the flags follow the standard ssh rules and options, so -i for identity files and -L for port forwarding.  Heres an example connecting to a remote host via a tunnel.

(bamcgill@daedalus.local)–(0|ttys000|-bash)–(Mon May 04|12:16:46)
(~/.ssh) $sql /nolog

SQLcl: Release 4.1.0 Release Candidate on Mon May 04 00:16:58 2015

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


SQL> sshtunnel bamcgill@gbr30060.uk.oracle.com -i ./id_rsa -L 8888:gbr30060.uk.oracle.com:1521

Password for bamcgill@gbr30060.uk.oracle.com ********
ssh tunnel connected

SQL> connect barry/oracle@localhost:8888/DB11GR24
Connected

SQL> select 'test me' as BLRK from dual weirdtable

BLRK
-------
test me


SQL>


You can download SQLcl from OTN here and give this a try when the next EA is released.

SQLcl - Code editing on the console

Fri, 2015-05-01 12:08
We've been playing with our console drawing in SQLcl for a while now and this week, we hooked up some keys to make editing and running much easier.  The video will show the following keys for managing your buffer in the console.  This will make it into the next Early Access candidate soon.

  • up arrow - previous history (this will continue to show you the next history unless you move into the text to edit it.
  • down arrow - next history which is the same as above.
If we are editing and not showing history, then the up and down arrow will move up and down the buffer. 
  • ctrl-W will take you to the top left of the buffer and ctrl-S will take you to the bottom of the buffer.
  • left arrow moves right, with ctrl-A taking you to extreme left of that line
  • right arrow moves right and ctrl-E takes you to the extreme right of that line
  • ESC takes you out of edit mode, back to the SQL> prompt
  • ctrl-R will execute your buffer if you are editing it.

Editing SQL in SQLcl
At the start of the video, we paste in a large piece of SQL from Kris' blog and all NBSP get stripped out so you get the full SQL and none of the dross. 
If you are at the end of the buffer and terminate your statement correctly, the next CR will run the contents of your buffer.  If you are anywhere else in the buffer, ctrl-R will run the buffer for you.
Check out the latest one on OTN and come back for these features when we drop the new version of SQLcl on OTN.

SQLcl connections - Lazy mans SQL*Net completion

Thu, 2015-04-30 10:30
Turloch posted this today, which is like aliases for SQL*Net connection URL's which are used to connections like this:

connect <USERNAME>/<Password>@URL


This works great and you can simplify your connection strings that you use.  Vadim wired this into the code completion and we can now code complete via key, a connection string that you have used before or you can set up a new now using the net command.