Thursday, October 15, 2015

The github repo for all the things the DB Tools team does will be posted here https://github.com/oracle/Oracle_DB_Tools 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 handsontable, or sqlcl scripting.

There's a new function in there to exec host commands and capture the stdout, stderr, and return code of the command. Now coupling that with the BLOB loading and you can load an entire directory of files into a table with a small amount of javascript.

Wednesday, October 14, 2015

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.

Running it is as expected. One new row and showing the blob size. The only difference here is that you can avoid typing s-c-r-i-p-t and save 2 letters with just scri.

Method #2 shared library

I'll be making a library intended to make some things easier such as file loading and creating the hashmap. So taking out this blob loading as an example there first 2 things to put into such a library in helpers.js

Tuesday, October 13, 2015

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://en.wikipedia.org/wiki/List_of_JVM_languages So you want to program sql scripting in groovy,perl,javascript,pascal,.... All doable now.

Javascript is the only one built into Java by default so most example will be focused on that although I do have some Jython ones also to post.

To run a script simple type: script <script name> If there is no file extension passed along it assume a .js The language is loaded based on this extension. If the language can't be loaded you get a message saying as much "Could not load Scripting Engine for :py" More on how to add the other languages later.

This example while simple show you can run commands from inside the script that affects sqlcl itself like changing the prompt. The simple example is that I connected to production so a baby bottle it swapped out for beer

There's a few globals available for use. There'll be more formal doc to follow

sqlcl

Methods:

setStmt(<String of stuff to run>)

This can be a single statement, an entire script of stuff, or any sqlcl command such as "@numbers.sql"

run()

Runs whatever is in the stmts

ctx ( this has tons of methods but this is the single most important )

Methods:

write(<String>)

util ( again tons of methods )

Methods:

execute(<string>,binds)

executes whatever is passed in with a boolean return for success/failure

executeReturnOneCol(<string>,binds)

executes and returns the first row , first column

executeReturnListofList(<string>,binds)

executes and returns an array(rows) of arrays(row).

executeReturnList(<string>,binds)

execute and returns and array ( rows ) of objects ( row )

Here is an example using some of these methods with the expectable output shown below.

Tuesday, October 06, 2015

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 of options on how to connect. This is list of valid jdbc prefixes. While I've not yet tested it as noted in this list we will be testing loading sqlcl into the DB and trying it out in the JVM there.

jdbc:oracle:thin

jdbc:oracle:oci8

jdbc:oracle:kprb

jdbc:default:connection

jdbc:oracle:kprb:

jdbc:default:connection:

Next is the ansiconsole formatting of numbers was changed up a little. The new syntax as 3 options. One piece of feedback was that the numbers auto formatting with separators made some things difficult. Now just setting the console formatter will NOT format numbers. Instead the default option needs to be specified. Also is the option to specify any number format which has to be in the form of a java format. The syntax can be read here.

set sqlformat ansiconsole

set sqlformat ansiconsole default

set sqlformat ansiconsole <number format>

This script show an example of all the new options.

The only caution when using the @|... |@ syntax is that only one thing can be used at a time as the, used to separate the formatting options gets read by the java formatter as a number format. If this is something that folks end up using a lot, I'll come up with something to let it blink and be red at the same time!