Kris Rice

Subscribe to Kris Rice feed
This blog has moved to
Updated: 9 hours 4 min ago

Carbonated SQLCL - aka SODA support

Fri, 2016-06-10 15:03
     Oracle Database introduced JSON support.  This is supported in REST Data Services already and now in the latest SQLCL.  This means ORDS can serve up the access to client applications and developers can have a command line way to interact and test features/queries/... in a nut shell a shell for JSON document storage. To use this feature the database will be to have patch #20885778

SQLCL - Blob loading ALL the files

Fri, 2016-06-10 15:03
The github repo for all the things the DB Tools team does will be posted here This will include sqlcl , sqldev, modeler, ords , and who knows what other things we have up our sleeves to post.  This repo is going to be made up of  examples and getting started things like a new extention for sqldev, custom transformation for modeler, ords integration with

AskTom's print_table - SQLCL version

Fri, 2016-06-10 15:03
There was an idea logged over on to add AskTom style print_table to sqlcl. Here's how easy it is now that there's scripting in sqlcl.  This is a simple js file that loops over the rows and columns and prints them.  When using the executeReturnListofList the first row is the name of the columns for reference.  With that data there it's quite simple to customize any output.

SQLCL - Blob loading from a file

Fri, 2016-06-10 15:03
There is a ton of ways having new scripting in sqlcl can help.  One example is a simpler way to load a local file into a BLOB in a column. Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this. Enter SQLCL and javascript method #1. Here's a simple script which loads a local file

SQLcl - Oct 13th Edition

Fri, 2016-06-10 15:03
Another build of SQLCL is out.  This one has lots of great things in it.  There's fix in there to work with Cygwin for the unlucky people that are still on windows. Now the new stuff:  SCRIPT This new command can radically change things you can do in the tool.  The script command runs a script that can be any language that is JSR-223 enabled.  The best list I can find is here: https://

SQLcl - Oct 5th Edition

Fri, 2016-06-10 15:03
It was time for another push of the new sql command line , sqlcl.  Here's the changes in the latest build.  There are a number of bugs fixed relating to connecting to idle databases, alias feedback, and picking up the $ORACLE_HOME's jdbc driver when available.  There is only a couple functional changes. First is that now the jdbc connect string itself can be specified.  This opens up a number

SQLCL - More secure, now with REST !

Fri, 2016-06-10 15:03
A new SQLCL build was just posted go grab it and kick the tires.  There are well over 100 bug fixes in there so it's better than ever.  Also there's some new things. More Secure    Imagine you have an api that is called and a password or something is used in the parameters.  We use exec MY_API(...)  and it works just fine.  However consider if someone with access to v$sql they just got

SQLcl , yet again

Fri, 2016-06-10 15:03
By the Numbers There's a new SQLcl out for download.  In case, there are too many to keep track of the build numbers are quite easy to tell if you have the latest.  The build posted today is Here's what we are doing 4.2.0 <-- doesn't matter at all 15     <- year 167   <- day in julian 0827 <- time the build was done So yes, this build was done today at 8am

Repeating another SQLCL EA release

Fri, 2016-06-10 15:03
    It's been a month and a lot of bugs have been fixed in that time and new features added. That adds up to time for another EA for our new sqlcl.  The third sqlcl EA is out now and can be found here on the SQL Developer 4.1 download page.     Not sure what this sqlcl thing is ? Check here. LOAD:  Making CSV loading to the db very easy Here's a very quick example of the possibilities.

3 new things about sdsql

Fri, 2016-06-10 15:03
New Name ! The first is a new name this EA it's named sqlcl for sql command line.  However, the binary to start it up is simply sql.  Nothing is easier when you need to run some sql than typing 'sql' and hitting enter. #./sql klrice/klrice@//localhost/orcl SQLcl: Release 4.1.0 Beta on Fri Jan 30 12:53:05 2015 Copyright (c) 1982, 2015, Oracle. All rights reserved. Connected to: Oracle

SDSQL - Output Formats

Fri, 2016-06-10 15:03
   In SQL Developer, Jeff has shown in many tips & tricks talks and here on his blog how to get data from any select into a few formats.  The most common I would say is csv.   Nothing here has changed. Well, almost.  What will be new is the html format is getting a facelift and a json format is being added neither are in the version of sdsql or sql developer that are on otn yet     As expected

SDSQL - Pre and Post Commands

Fri, 2016-06-10 15:03
  This is in the process of evolving but should be enough for anyone to give it a whirl.  The idea is that for any given statement you may want to run something before and possible after.   Here's a trivial example.  I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command.  Then after I'm doing sql that shows me the changes from that point

Aliases with sdsql and simpler CTAS

Fri, 2016-06-10 15:03
First, we just put up a new build of sdsql.  Go get it or the last thing here will not work.   SQL is a great and verbose language so there's many ways to shorten what we have to type.  As simple as a view or saving a script to call later with @/path/to/sessions.sql  SDSQL is taking it a step further and we added aliases to the tool.  Almost as if right on queue, John asked if we could add them.

DIY SQCL Commands

Fri, 2016-06-10 15:03
As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable.  To learn more on Nashorn itself there's a lot of great write ups such as  So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands. Here's an

SQLCL Monitoring itself with Longops

Fri, 2016-06-10 15:03
Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl. The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at anytime

Logstash and Oracle Database

Wed, 2016-03-30 07:48
    For anyone that doesn't know what Logstash is head over to and watch the video on the home page.  Robin Moffatt has done a great blog post on the full ELK stack with OBIEE.     This blog post is a first cut at an input filter for Logstash to gather metrics, logs, or anything that can be expressed in sql.  A huge caution that this is a 0.01 attempt and will get better as


Mon, 2016-03-14 07:56
Seems I've never posted about PL/SQL based REST endpoints other than using the OWA toolkit.  Doing the htp.p manually can give the control over every aspect of the results however there is an easier way. With PL/SQL based source types, the ins and outs can be used directly without any additional programming.  Here's a simple example of an anonymous block doing about as little as possible but

Yet another CSV -> Table but with pipleline function

Tue, 2015-08-25 09:11
Here's just one more variation on how to get a CSV into a table format.  It could have been done before but my google-fu couldn't find it anywhere. First to get some sample data using the /*csv*/ hint in sqldev. Then the results of putting it back to a table. The inline plsql is just to convert the text into a CLOB. Now the details. The csv parsing is completely borrowed(stolen) from

Connecting to DBaaS, did you know this trick?

Wed, 2015-07-15 16:10
SSHTunneling Trick The new command line is a must try, says 10 out of 10 people that built it.  The tool has sshtunneling of ports built in as described by Barry. This means you can script opening your sshtunnel from the command line and run sql very quickly.  Here's the one I used recently at Kscope15. Now the trick? is that once this port is forwarded, any tool can now use it.  In case

ORDS - Auto REST table feature

Tue, 2015-07-14 10:47
Got a question on how easy it is to use ORDS to perform insert | update | delete on a table.  Here's the steps. 1) Install ORDS ( cmd line or there's a new wizard in sqldev ) 2) Enable the schema and table in this case klrice.emp; ( again there's a wizard in sqldev ) BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'KLRICE',