Tuesday, October 06, 2015

SQLcl - Oct 5th Edition

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!

Friday, September 11, 2015

SQLCL - More secure, now with REST !

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 anything you passed into the api and quite easily.  Enter SQLCL new thing #1 starting with this build now changes all "exec my_api(...)" into an anonymous block with binds so those literals do not show up.

Procedure SECURE_API compiled

KLRICE@orcl >exec secure_api('my password');

PL/SQL procedure successfully completed.

KLRICE@orcl >select sql_text from v$sql where sql_text like '%secure_api%';

BEGIN secure_api(:ZSqlDevUnIq1); END;                          
select sql_text from v$sql where sql_text like '%secure_api%'  

KLRICE@orcl >

REST Support

Always check the help, the tool highlights what is new to make it easy.  It only took Alex 8 minutes from the download being available to seeing the new features !

 This build adds the ability to extract ORDS services.  Once a rest service is defined and it's time to move it from one system to another this will make it easier than ever.  Simple spool to a file, export the definition and instant install script.

KLRICE@orcl >help
KLRICE@orcl >rest


REST allows to export ORDS 3.X services.

      REST export                     -  All modules
      REST export >module_name>    -  Export a specific module
      REST export >module_prefix>  -  Export a specific module related to the given prefix
      REST modules                    -  List the available modules
      REST privileges                 -  List the existing privileges
      REST schemas                    -  List the available schemas
KLRICE@orcl >rest modules

demos  demos/  PUBLISHED  25              
hr     hr      PUBLISHED  25              

KLRICE@orcl >rest export hr

  l_module_id number;
  l_template_id number;
  l_handler_id number;

  l_module_id := ORDS_METADATA.ORDS_SERVICES.create_module( p_name => 'hr' , p_uri_prefix => 'hr' , p_items_per_page => 25 , p_status => 'PUBLISHED' );
  l_template_id := ORDS_METADATA.ORDS_SERVICES.add_template( p_module_id => l_module_id, p_uri_template => 'everything' , p_priority => 0 , p_etag_type => 'HASH' );
  l_handler_id := ORDS_METADATA.ORDS_SERVICES.add_handler( p_template_id => l_template_id, p_source_type => 'json/query' , p_method => 'GET' , p_items_per_page => 0 , p_source => 'select d.*,' || unistr('\000a')
   || '' || unistr('\000a')
   || '   cursor( select e.*' || unistr('\000a')
   || '           from hr.employees e where e.department_id = d.department_id) emps           ' || unistr('\000a')
   || '   from hr.departments d');

KLRICE@orcl >

Tuesday, August 25, 2015

Yet another CSV -> Table but with pipleline function

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 another Chris where he create a csv parser in plsql here.

The changes I made which is probably obvious to use a plsql object and table of said object to put into a form that can be use in sql.

I'm sure there's optimizations that could be added in here since I almost never use plsql object. But here's the code that renders the above results.

drop function parse_csv;
drop type t_csv_col;
drop type t_csv_table;

    col1   varchar2(4000),
    col2   varchar2(4000),
    col3   varchar2(4000),
    col4   varchar2(4000),
    col5   varchar2(4000),
    col6   varchar2(4000),
    col7   varchar2(4000),
    col8   varchar2(4000),
    col9   varchar2(4000),
    col10   varchar2(4000)

show errors

CREATE or replace TYPE t_csv_COL IS TABLE OF t_csv_table

show errors

create or replace function parse_csv(
  p_clob clob,
  p_delim varchar2 default ',',
  p_optionally_enclosed varchar2 default '"' ) 
  return t_csv_COL
  CARRIAGE_RETURN constant char(1) := chr(13);
  LINE_FEED constant char(1) := chr(10);
  l_char char(1);
  l_lookahead char(1);
  l_pos number := 0;
  l_token varchar2(32767) := null;
  l_token_complete boolean := false;
  l_line_complete boolean := false;
  l_new_token boolean := true;
  l_enclosed boolean := false;
  l_lineno number := 1;
  l_columnno number := 1;
  -- additions from Kris
  l_ret t_csv_COL;
  l_ret_row t_csv_table;
  l_ret_index number;
  -- initialize things
  l_ret := t_csv_COL();
  l_ret_row := t_csv_table(null,null,null,null,null,null,null,null,null,null);
    -- increment position index
    l_pos := l_pos + 1;
    -- get next character from clob
    l_char := dbms_lob.substr( p_clob, 1, l_pos);
    -- exit when no more characters to process
    exit when l_char is null or l_pos > dbms_lob.getLength( p_clob );
    -- if first character of new token is optionally enclosed character
    -- note that and skip it and get next character
    if l_new_token and l_char = p_optionally_enclosed then
      l_enclosed := true;
      l_pos := l_pos + 1;
      l_char := dbms_lob.substr( p_clob, 1, l_pos);
    end if;
    l_new_token := false;
    -- get look ahead character
    l_lookahead := dbms_lob.substr( p_clob, 1, l_pos+1 );
    -- inspect character (and lookahead) to determine what to do
    if l_char = p_optionally_enclosed and l_enclosed then
      if l_lookahead = p_optionally_enclosed then
        l_pos := l_pos + 1;
        l_token := l_token || l_lookahead;
      elsif l_lookahead = p_delim then
        l_pos := l_pos + 1;
        l_token_complete := true;
        l_enclosed := false;
      end if;
    elsif l_char in ( CARRIAGE_RETURN, LINE_FEED ) and NOT l_enclosed then
      l_token_complete := true;
      l_line_complete := true;
      if l_lookahead in ( CARRIAGE_RETURN, LINE_FEED ) then
        l_pos := l_pos + 1;
      end if;
    elsif l_char = p_delim and not l_enclosed then
      l_token_complete := true;
    elsif l_pos = dbms_lob.getLength( p_clob ) then
      l_token := l_token || l_char;
      l_token_complete := true;
      l_line_complete := true;
      l_token := l_token || l_char;
    end if;
    -- process a new token
    if l_token_complete then
      -- dbms_output.put_line( 'R' || l_lineno || 'C' || l_columnno || ': ' || nvl(l_token,'**null**') );
       -- assign the column value
      case  l_columnno 
          when 1 then  l_ret_row.col1 := l_token;
          when 2 then  l_ret_row.col2 := l_token;
          when 3 then  l_ret_row.col3 := l_token;
          when 4 then  l_ret_row.col4 := l_token;          
          when 5 then  l_ret_row.col5 := l_token;
          when 6 then  l_ret_row.col6 := l_token;
          when 7 then  l_ret_row.col7 := l_token;          
          when 8 then  l_ret_row.col8 := l_token;
          when 9 then  l_ret_row.col9 := l_token;
          when 10 then l_ret_row.col10 := l_token;
          else dbms_output.put_line('Only supports up to 10:'||l_columnno);
      end case;
      l_columnno := l_columnno + 1;
      l_token := null;
      l_enclosed := false;
      l_new_token := true;
      l_token_complete := false;
    end if;
    -- process end-of-line here
    if l_line_complete then
      l_ret(l_lineno):= l_ret_row;
      l_lineno := l_lineno + 1;
      l_columnno := 1;
      l_line_complete := false;
      l_ret_row := t_csv_table(null,null,null,null,null,null,null,null,null,null);

    end if;
  end loop;
  return l_ret;
end parse_csv;
show errors

Wednesday, July 15, 2015

Connecting to DBaaS, did you know this trick?

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 you ( like me ) forgot which port is forwarded since it's all scripted so as not to be thought about again.  There's a "show sshtunnel" command built in also

Now I can take this over to any tool, which for me is sqldev, and use that port to make a connection

Yes, a NEW demo on EMP 

If you thought you'd seen ever demo possible off the data in emp.  Here's one more. Salary represented as beer.

Tuesday, June 30, 2015

New Tools releases , now with Java

What's New

  For the 90%+ of people using sqldev/modeler on windows, the JDK bundled versions are back.  So no more debating what to install or worrying about conflicting Java versions.

  Lots of bug fixes.

   My favorite bug is now fixed so you can use emojis in your sql> prompt.

RESTful CSV Loading

  We wrapped he same CSV import code in SQL Developer into the REST Auto-Enablement feature. Here's a very short example of javascript batch inserting 4 records into the EMP table.  I'm not sure it can get much easier.

// CSV (POST http://localhost:8888/ords/klrice/emp/batchload)

    url: "http://localhost:8888/ords/klrice/emp/batchload?" + jQuery.param({
        "responseFormat": "RAW",
    type: "POST",
    headers: {
        "Content-Type": "text/csv",
    processData: false,
    data: "empno,ename,job,mgr,hiredate,sal,comm,deptno
5001,M,SPY MAST,,2005-05-01 11:00:01,4000,,11
5002,J.BOND,SPY,0,2005-05-01 11:00:01,2000,,11
5003,R.Cooper,SOFTWARE,0,2005-05-01 11:00:01,10000,,11
5004,Max,DENTIST,0,2005-05-01 11:00:01,5000,,11
.done(function(data, textStatus, jqXHR) {
    console.log("HTTP Request Succeeded: " + jqXHR.status);
.fail(function(jqXHR, textStatus, errorThrown) {
    console.log("HTTP Request Failed");
.always(function() {
    /* ... */

Here's the same example in Paw which is a great client if are on OSX

While this is a very simple case, there are a lot of switches to customize this importing.  Those options are all in the doc found here:  http://docs.oracle.com/cd/E56351_01/doc.30/e56293/develop.htm#BABCACEC

Go Forth and Download

SQL Developer 4.1.1

Data Modeler 4.1.1

REST Data Services 3.0.1

SQLCL - Main Line Build
( Last Link on the bottom of the sqldev download page )

Disqus for Kris' Blog