Thursday, 16 June 2016

Developer Days VM Startup and Shutdown, SQLcl

If you're developing on a remote platform, chances are that you are using a Virtual Machine. (VM)  In Oracle, we release a virtual machine called the  "Oracle Developer Days".    This is available on the Oracle Technology Network and ala google.  Todays hack is setting up headless vm's, ports and aliases to speed up your day. (This post took a lot longer to write that the aliases we set up!)

For this virtual machine on Virtual Box, we have a bunch of cool stuff to get your teeth into, prebuilt and configured, ready to go.  Most of the time, we all start our VM from the virtual box front end like this from the application and use the guy front end into the VM.

You can click on the network tab and click port forwarding to see how you are allowing access to this virtual machine.

Clicking on your VM in the user interface, you can start it up and get the full GUI to work from.

Normal VM startup within the GUI
This takes a while and has to be done every time you are starting up which is too many times.  Like any engineer, we are all trying to reduce the number of times we have to do the same thing so I like to make these into command line calls and make the GUI disappear so we can ssh into the virtual machine.

From above, we can see that ssh is mapped to port 2222 on the host for this guest virtual machine so there should be no issues in working this way.   To startup your VM from the command line like above, run this below (obviously changing the name of your VM before you do!)  This will open a window and startup your VM.

vboxmanage startvm "Oracle DB Developer VM" --type=gui

You can also start up your virtual machine in a headless session, you can run this command, very similar to the one above to do that.

(~) $vboxmanage startvm "Oracle DB Developer VM" --type headless
Waiting for VM "Oracle DB Developer VM" to power on...
VM "Oracle DB Developer VM" has been successfully started.
(bamcgill@ daedalus)–(0|ttys001|-bash)
(~) $

and thats it. the machine will boot, start the database and start the listeners.  you can then ssh into your machine like this.

(bamcgill@ daedalus)–(0|ttys001|-bash)
(~) $ssh -p 2222 oracle@localhost
oracle@localhost's password: 
Last login: Thu Jun 16 07:35:21 2016
[oracle@vbgeneric ~]$ sqlplus barry/oracle

SQL*Plus: Release Production on Thu Jun 16 07:38:20 2016

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

Last Successful login time: Thu Jun 16 2016 06:30:17 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ~]$ logout
Connection to localhost closed.
(~) $

All very cool and means I can have aliases setup to do this too so I don't have to remember all the long bits of a vboxmanage statement!

(~) $alias | grep vbox
alias headless='vboxmanage startvm "Oracle DB Developer VM" --type headless'
alias poweroff='vboxmanage controlvm "Oracle DB Developer VM" poweroff'
alias startvm='vboxmanage startvm "Oracle DB Developer VM" --type=gui'
alias vboxsave='vboxmanage controlvm "Oracle DB Developer VM" savestate'
(~) $

poweroff  rips the rug from under the VM and it is reset
savestate saves the VM like a most recent snapshot and when started again, the VM is refreshed with this state.

When all this is setup, you can connect with SQLcl.  By default, sqlcl will look at a few connect strings when you connect, so you can try sql user/password in your host machine.

(bamcgill@ daedalus)–(0|ttys001|-bash)
(~) $sql barry/oracle

SQLcl: Release RC on Thu Jun 16 13:01:44 2016

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

Last Successful login time: Thu Jun 16 2016 13:01:44 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

BARRY@orcl🍻🍺 >

Fantastic.  Look out for more great features on Oracle SQLcl and SQLDeveloper  with Kris, Jeff , Dermot and  Turloch 

Thursday, 15 October 2015

Wheres my TNS connection? SHOW TNS

Lots of users have been head scratching as to which tnsnames.ora is being found and used when  connecting to the database with SQLDeveloper and with SQLcl.

In the latest release we've added another new command.


What this will do is walk the locations where we look for tnsnames.ora and list these out in order.  Then it will tell you which one the tool will actually use and list the entries for you.

So, with nothing set, no ORACLE_HOME, no TNS_ADMIN, here's what you get.  TNS is going to look in your home directory for a file called tnsnames.ora or .tnsnames.

Now, if we have an ORACLE_HOME set, we'll look for $ORACLE_HOME/network/admin/tnsnames.ora

Further, if we set TNS_ADMIN, it will override ORACLE_HOME and go to that location as shown here.

Lastly, we'll come back to the User directory. If you have a tnsnames.ora there or a .tnsnames, it will override everything and this is what will be used.

Now, go ahead and make a connection.  You can then do another new command called


which will show you how you are connected and what you are connected to.

Finally, you can get a look at which driver you are using for a connection using


which will show something like this, detailing types, versions and the URL of the connection you have.

Sunday, 3 May 2015

SQLcl - Cloud connections via Secure Shell tunnels

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

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]


-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. 


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 -i ./id_rsa -L

Password for ********
ssh tunnel connected

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

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

test me


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

Friday, 1 May 2015

SQLcl - Code editing on the console

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.

Thursday, 30 April 2015

SQLcl connections - Lazy mans SQL*Net completion

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.

Friday, 20 February 2015

Connections Types in SQLcl

We support many ways to connect in SQLcl, including lots from SQL*Plus which we need to support to make sure all your SQL*Plus scripts work exactly the same way using SQLcl as with SQL*Plus.

I've added several ways to show how to connect to SQLcl.  If there is one you want to see added that is not here, let me know and I'll add it to the list.  So far, We have below:
  • EZConnect
  • LDAP
At any time when connected you can use the command 'SHOW JDBC'  to display what the connection is and how we are connected.  Here's some details of the types above.


The easy connect naming method eliminates the need for service name lookup in the tnsnames.ora files for TCP/IP environments.  It extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database:

 $sql barry/oracle@localhost:1521/orcl  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:15:12 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 12c Enterprise Edition Release - 64bit Production   


The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection  is made in SQL*Plus or SQL*Plus Instant Client. 

In SQLcl, we can set this up as a jdbc style connection like this

$export TWO_TASK=localhost:1521/orcl  


Local Naming resolves a net service name stored in a tnsnames.ora file stored on a client.  We can set the location of that in the TNS_ADMIN variable.

 $export TNS_ADMIN=~/admin  

An example tons entry is shown here below.

 $cat tnsnames.ora   
 BLOG =  
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )  
 (SERVICE_NAME=orcl) ) )  

we can then use the entry to connect to the database.

 $sql barry/oracle@BLOG  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:29:14 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 12c Enterprise Edition Release - 64bit Production   


We've already written about LDAP connections here.  Here's a quick review.

  set LDAPCON jdbc:oracle:thin:@ldap://,cn=OracleContext,dc=ldapcdc,dc=lcom   

 $export LDAPCON=jdbc:oracle:thin:@ldap://,cn=OracleContext,dc=ldapcdc,dc=lcom   
 $sql /nolog  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:37:02 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 SQL> connect barry/oracle@orclservice_test(Emily's Desktop)   

If we have more types to add, then they will appear here.  Let us know what you want to see.

Thursday, 19 February 2015

Code Insight on SQLcl

Here's a little preview of the code insight we have in SQLcl.  These changes are part of EA2 which are coming out very soon.  This also shows the buffer and cursor management which was introduced in SQLcl

This allows you to move around the buffer easily and add and change text as you would in a normal text editor, not a console window like this.

We're also adding hotkeys to run the buffer from anywhere or to jump out of the buffer to do something else without losing the contents of the buffer.

Stayed tuned for this soon.