X

Katsumi's Blog

  • Java
    March 10, 2016

SQLcl can run Ruby script!!.. JRuby to be precise.

SQLcl is the java version of sqlplus with lots of new features.
One of them is the ability to run JVM based languages and I tried python before .

I finally ran Ruby this time. I used the 2nd sample from the blog by our product manager below.

Kris' blog: SQLcl - Oct 13th Edition

More on how to add the other languages later.

These sample Javascript files are already uploaded to GitHub. So, I downloaded like:

$ https_proxy=your-proxy:80 c-u-r-l  -O https://raw.githubusercontent.com/oracle/Oracle_DB_Tools/master/sqlcl/examples/sql.js

Then I copied this *.js file to sql.rb and modified to Ruby constructs and syntaxes.

Finally, I modified all the lines and the top lines of 'diff' looks like below. I think the most prominent difference is that "global" variables like 'util','sqlcl,'ctx' needs to be prefixed with '$'.

$ diff /c/c/kinoue/bin/sql.{js,rb} | head -14
1,2c1,2
< /* Look up a single value to use in a bind later */
< var user = util.executeReturnOneCol('select user from dual');
---
> # Look up a single value to use in a bind later
> user = $util.executeReturnOneCol('select user from dual')
4,7c4,5
< /* simple string or number binds can be a js object */
< var binds = {};
< binds.name = 'EMP';
< binds.who = user;
---
> # simple string or number binds can be a Ruby object
> binds = {'name' => 'EMP', 'who' => user}

Next , I downloaded JRuby library files. I wanted to do minimal setup so I just downloaded 1 *.gem file.

Downloads — JRuby.org

JRuby-jars 9.0.5.0.gem

Then I extracted 2 *.jar files under lib/ directory.

$ tar xvf /c/c/tmp/jruby-jars-9.0.5.0.gem data.tar.gz -O \
| tar xvz lib/jruby-core-9.0.5.0-complete.jar lib/jruby-stdlib-9.0.5.0.jar

This is the whole sql.rb file.

# Look up a single value to use in a bind later
user = $util.executeReturnOneCol('select user from dual')
# simple string or number binds can be a Ruby object
binds = {'name' => 'EMP', 'who' => user}
$ctx.write('Using Binds:'+ binds['name'] + ',' + binds['who'] + "\n");
$ctx.write("***************************************************************\n");
$ctx.write("***** SIMPLE LOOP OF LIST OF LIST ***********\n");
$ctx.write("***************************************************************\n");
ret = $util.executeReturnListofList('select object_name,object_type from all_objects where object_name = :name and owner = :who ',binds);
(0..ret.size-1).each {|i|$ctx.write( ret[i][1] + "\t" + ret[i][0] + "\n");
}
$ctx.write("\n\n");
$ctx.write("***************************************************************\n");
$ctx.write("***** SIMPLE LOOP OF LIST OF NAMES WITH BINDS **********\n");
$ctx.write("***************************************************************\n");
ret = $util.executeReturnList('select object_name,object_type from all_objects where object_name = :name and owner = :who ',binds);
(0..ret.size-1).each {|i|$ctx.write( ret[i]['OBJECT_TYPE'] + "\t" + ret[i]['OBJECT_NAME'] + "\n");
}

Finally ,  I ran this *.rb file in sql.exe(SQLcl).

$ CLASSPATH=lib/* sqlcl/bin/sql scott/tiger@myserver/orcl
SQLcl: Release 4.2.0.15.349.0706 RC on Thu 3 10 18:44:34 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> script c:/kinoue/bin/sql.rb
Using Binds:EMP,SCOTT
***************************************************************
***** SIMPLE LOOP OF LIST OF LIST ***********
***************************************************************
OBJECT_TYPE OBJECT_NAME
TABLE EMP
INDEX EMP
***************************************************************
***** SIMPLE LOOP OF LIST OF NAMES WITH BINDS **********
***************************************************************
TABLE EMP
INDEX EMP
SQL>

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.