SDSQL - Output Formats

   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 all those "hint" work in sdsql as well.  What's new is there is a "set sqlformat" command so that all results come out in that format. So, all the same formats work : xml, html, delimited, insert, loader, fixed, text. Also there's a new one named ansiconsole which is described here.

  Now with the new things being added it could be easy to create a "build" script of sorts.  Combine this with the new ddl command into something like this

SQL>set sqlformat insert
SQL>spool my_build_script.sql
SQL>ddl emp
SQL>select * from emp;
SQL>apex export 101
SQL>spool off

What you would end up with out of this is a sql script with the ddl and data for emp and the Apex App export.  This script could then be checked in, given to a DBA, or even just for a nightly backup of sorts.

SDSQL - Pre and Post Commands

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

SQL> select count(1) from emp;

Table MY_STATS dropped.


Table MY_STATS created.

  COUNT(1)
----------
        14

NAME                                                                  DELTA
---------------------------------------------------------------- ----------
logical read bytes from cache                                       1859584
session pga memory                                                  1507328
session uga memory                                                  1035504
KTFB alloc space (block)                                              65536
cell physical IO interconnect bytes                                   32768
physical write total bytes                                            24576
physical write bytes                                                  24576
redo size                                                             16200
.....

This is on the same connection you are executing on so whatever is being done will show in the stats as well. One thing still being worked on is the ability to clone and pass some context to another connect and have these pre and post things done on that connection keeping the main connection free of that noise.

The way to do this is the introduction of 2 new set commands. The names should be a clue as to which is which : set precommand and set postcommand. They can hold 1 or N commands to be run.

set precommand drop table my_stats; create table my_stats as select * from v$mystat;
set postcommand select s.name, ( m2.value - m.value ) delta from my_stats m, v$mystat m2, V$STATNAME s where m.STATISTIC#   = m2.STATISTIC# and m.STATISTIC#    = s.STATISTIC# and m.value != m2.value order  by 2 desc;

select count(1) from user_objects where object_name like 'E%';
~                                                                 

The current downside of this method is that it needs to be all on one line.  So, if there's multiple commands to be run it's kinda ugly.  Like in this precommand where I drop and create a table.  The great thing about this is that the command to be run could be @mypre.sql

This allows me to better format the sql and make it much much easier to read and maintain.

└─>cat pre.sql 
drop table my_stats;
create table my_stats as select * from v$mystat;

└─>cat post.sql 
select s.name, ( m2.value - m.value ) delta 
  from my_stats m, v$mystat m2, V$STATNAME s 
  where m.STATISTIC#   = m2.STATISTIC# 
    and m.STATISTIC#    = s.STATISTIC# 
    and m.value != m2.value order  by 2 desc;

Then the sql set command look just like expected.

set precommand @pre.sql
set postcommand @post.sql

Now if you want to mess with people throw something like this in their login.sql when they aren't looking

SQL> set postcommand select 'Better go tune that before Joel sees it' MOTD  from dual;


Then on every command they will get that message.

SQL> select 1 from dual;


         1
----------
         1

MOTD                                 
---------------------------------------
Better go tune that before Joel sees it

SQL> 

Aliases with sdsql and simpler CTAS

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.

@krisrice Any chance of an ‘alias’ command while you’re at it? ;)

— John Scott (@aejes) December 12, 2014

Here's a simple example where I setup an alias for "sessions" in my login.sql.  Once defined  you simply type that alias name hit enter and go.  Now do not go and be trying to alias "select" or other base things.  Aliases are evaluated after everything we normally recognize , although it would be an awesome April fool thing to edit a friend's file and do that. hmm maybe a new feature....

Setting up these aliases are very simple yet quite flexible.  In the simplest form,

KLRICE@ORCL>alias ls=select object_name,object_type,last_ddl_time 
  2  from user_objects
  3   order by 2,3;
KLRICE@ORCL>ls
Command=ls
OBJECT_NAME                     OBJECT_TYPE        LAST_DDL_TIME  
REMOTEDB                        DATABASE LINK                     
DEMO_ERROR_HANDLING             FUNCTION           25-SEP-14      
DO_WORK                         FUNCTION           25-SEP-14      
EXECSQL                         FUNCTION           25-SEP-14      
FIB                             FUNCTION           25-SEP-14      

Now add in binds and it become very flexible.  This is just one bind but you can probably guess the names of binds 2,3,N

KLRICE@ORCL>alias ll=select object_name,object_type,last_ddl_time 
  2  from user_objects
  3  where object_name like :1||'%';
KLRICE@ORCL>ll E
Command=ll
OBJECT_NAME    OBJECT_TYPE  LAST_DDL_TIME  
EXECSQL        FUNCTION     25-SEP-14      
EMP_TRG1       TRIGGER      25-SEP-14      
EMP_SEQ        SEQUENCE     25-SEP-14      
EMP_NEW        TABLE        11-DEC-14      
EMPLOYEES_SEQ  SEQUENCE     25-SEP-14      
EMPLOYEES_PK   INDEX        25-SEP-14      
EMPLOYEES22    TABLE        25-SEP-14      
EMP            TABLE        25-SEP-14      
E1             TABLE        11-DEC-14      

It works for aliasing off plsql calls also.

If you need to see the aliases defined, just type alias or alias list.

If you need to see what the alias is, just type alias list <alias name>

There's lot you can do with this to save lots of typing and personally there'll be lots less typo for my presentations!

CTAS Easier than ever

Lastly, for the one that went and downloaded the build we just posted today there is a new CTAS <TABLE> <NEW_TABLE> command added just yesterday.  The idea here is yet again to save on typing.  This is just getting dbms_metadata information so what is used can be adjusted as I explained here  Then strip off the things not needed ( i.e. datatypes ) swap out the names and you get a new table with very very little typing.  Note the index on empno carries over.

What is SDSQL ?

  SQL Developer is now up to version 4.1 and has had many additions over the years to beef up the sqlplus compatibility.  This is used today by millions of users adding up to millions if not billions of hours in the tool doing their work.  That means our support of core sqlplus has to be very full featured.  One idea we kicked around for a while but never had the time to do was to make our sqlplus engine available on a command line.  The huge benefit of this is that we could then run the actual sqlplus regression tests against our code line.

Enter SDSQL

  We did exactly that.  The guts of SQL Developer's sqlplus support married with a command line interface.  The result is a 10mb download soon with native installers.  That 10mb includes everything you need including the jdbc driver.  Now while replicating sqlplus is interesting and will greatly improve our support for it, it's well boring.  Writing code that does nothing more than mimic other code isn't fun.  So we are adding lots and lots of new things.   The first things you'll notice using sdsql is the arrow keys work, there's history, there's color these and more are just core things we added.  There's also entirely new commands being added and the easiest way to see some of these new things is to use the help command.  The non-sqlplus command are highlighted.  Give them a try.

Help

Here's something added for Bryn which is pretty much obvious yet never been in sqlplus.  I'm in sqlplus and want to change directory to a new path and run some scripts.  In sqlplus, you either exit, cd, and start back up or path all the scripts like @my/other/path/script.sql

Now there's a built in "cd" command.

@krisrice @thatjeffsmith More generally on SQL*Plus ++ a "cd" command (implies "pwd" too) to allow ad hoc script running with less typing.
— Bryn Llewellyn (@BrynLite) November 29, 2014

Here's a quick example.  I start the tool in one path , raptor_common,  then need to run a script in the sub directory.  A quick cd examples then @color.sql.  All host operations are now in that path.  That means running a script, spooling a file, host commands, ...

KLRICE@ORCL>┌─[14:29:56]─[klrice]─[kriss-MacBook-Pro-2]:~/workspace/raptor_common$
└─>./sdsql klrice/klrice@localhost/orcl

sdsql: Release 4.1.0 Beta on Thu Dec 11 14:30:04 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 

KLRICE@ORCL>host pwd
/Users/klrice/workspace/raptor_common
KLRICE@ORCL>host ls
color  examples login.sql sdsql  test.sql
KLRICE@ORCL>cd examples    <<<<<<< I can just cd to the new path
KLRICE@ORCL>host pwd       <<<<<<< I can do hosty things here
/Users/klrice/workspace/raptor_common/examples
KLRICE@ORCL>host ls        <<<<<<< Then run host command in that path
color.sql ctas.sql ddl_emp.sql
KLRICE@ORCL>@color         <<<<<<< Then run sql from it 
Colors                                     
This is red,bold,underline             
 This is negative                           
 This is faint                              
 This is my bold                            
 This is italic                             
 This is underline                          
 This is blink_slow                         
 This is blink_fast                         
 This is conceal                            
 This is black                             
 This is green                             
This is yellow                             
 This is blue                              
 This is magenta                           
  This is cyan                             
 This is white                             
 This is bg_red                            
 This is bg_black                          
 This is bg_green                          
This is bg_yellow                          
 This is bg_blue                           
 This is bg_magenta                        
  This is bg_cyan                          
 This is bg_white                          
 This is bg_red                            


KLRICE@ORCL>host cat color.sql

select '@|red,bold,underline This is red,bold,underline|@' "@|red Colors|@"  from dual
union all
select '@|NEGATIVE_ON  This is negative|@'   from dual
union all
select '@|INTENSITY_FAINT  This is faint|@'   from dual
union all
select '@|INTENSITY_BOLD  This is my bold|@'   from dual
union all
select '@|ITALIC  This is italic|@'   from dual
union all
select '@|UNDERLINE  This is underline|@'   from dual
union all
select '@|BLINK_SLOW  This is blink_slow|@'   from dual
union all
select '@|BLINK_FAST  This is blink_fast|@'   from dual
union all
select '@|CONCEAL_ON  This is conceal|@'   from dual
union all
select '@|black  This is black|@'   from dual
union all
select '@|green  This is green|@'   from dual
union all
select '@|yellow This is yellow|@'   from dual
union all
select '@|blue  This is blue|@'   from dual
union all
select '@|magenta  This is magenta|@'   from dual
union all
select '@|cyan   This is cyan|@'   from dual
union all
select '@|white  This is white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual
union all
select '@|bg_black  This is bg_black|@'   from dual
union all
select '@|bg_green  This is bg_green|@'   from dual
union all
select '@|bg_yellow This is bg_yellow|@'   from dual
union all
select '@|bg_blue  This is bg_blue|@'   from dual
union all
select '@|bg_magenta  This is bg_magenta|@'   from dual
union all
select '@|bg_cyan   This is bg_cyan|@'   from dual
union all
select '@|bg_white  This is bg_white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual

If you want better output try the script and see why it's named color.sql

SDSQL's flux capacitor

  Writing sql or any code is an iterative process.  Most of the time that means to see what you have done say 5 minutes ago means how big is your undo buffer or better is if you are in SQL Developer there's a full blown history.  If you are in sqlplus, you are basically out of luck.

History

  SDSQL has built in history and between sessions.  We are still jiggling where it stores the history so from EA1 to next it's most likely going to change to be configurable and default to your PWD.  That means a possible shared history OR a per folder history.  If you type as little as possible like me, simply typing h will print the history upto the last 100 commands.

  But wait there's more.  Try "history full" and you get the not condensed sql for example from the previous screenshot item #36 is ugly and not what I typed in.  I use typed very loosely since I pasted it.  Typing history full shows the real sql

  Curious how many time a statement user executed? Try typing "history usage" and now it shows how many times with a number in parentheses.  For example here, I forget a lot what emp looks like so I've done "info emp" 11 times.

If you read my history, you probably noticed the alias command in here.  Check out the help for it and give it a try.

Getting DDL for objects with sdsql

Getting ddl out for any object is quite simple.  You can just call dbms_metadata with something nice and easy like select dbms_metata.get_ddl('TABLE','EMP') from dual;

SQL>  select dbms_metadata.get_ddl('TABLE','EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "KLRICE"."EMP"
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
 "ENA

SQL>

Now you are all set and go create a table right ? Not really. The easiest part is setting pages or set long to get the full output.  The hard part is getting the dependent objects like triggers.


Now introducing the new DDL command

KLRICE@ORCL>ddl emp

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB, 
  PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
  FOREIGN KEY ("MGR")
   REFERENCES "KLRICE"."EMP" ("EMPNO") ENABLE, 
  FOREIGN KEY ("DEPTNO")
   REFERENCES "KLRICE"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" 
 LOB ("MYCLOB") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE
KLRICE@ORCL>

Too much details?

This is still using dbms_metadata underneath it so you can adjust the output by setting some transformation parameters on metadata.  For example:

KLRICE@ORCL>host cat examples/ddl_emp.sql
begin
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

  end;
/

ddl emp
KLRICE@ORCL>@examples/ddl_emp.sql
anonymous block completed

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0), 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB
   ) ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


/
ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE;
KLRICE@ORCL>

But wait I have an Application Express Application I need the 'ddl' for that too. Give this a try.

OBE@ORCL>apex export 100
<<< LOTS AND LOTS OF TEXT WILL PRINT >>

Now couple these with spool and you have a sql script to get objects with dependencies and the apex application that goes with it all.

OBE@ORCL>spool mybuild.sql
OBE@ORCL>ddl emp
OBE@ORCL>ddl dept
OBE@ORCL>apex export 100
OBE@ORCL>spool off

SQL Developer 4.1 EA1 is out

SQL Developer 4.1 is out for tire kicking.  There's a lot of new things in there as well as some great enhancements.  There's a nice shiny new Instance Monitor to get an overview of what's going on.   Keep in mind this is in flux from a UI stance and the final may be quite different that what you see today.  There's tons of data here and mostly all things have drill downs.





Also, we took the sqlplus engine we have and built a command line for it and named it SDSQL.  Someone will say this stands for sql developer sql but let's not dwell on names.  This new command line has TONS of things in it.  Please keep in mind this is an EA.  The new command line is very very compatible with sqlplus in fact I alias sqlplus to my sdsql and that's all I use now.  However, this is an EA and it is not 100% sqlplus and may never be there's some very odd old things that sqlplus does like did you know "SQL> #create table klr_1 ( id number );" works?  Yeah , we're not doing that.

Anyway, grab it, try it, see what you think.  On the 4.1 download page there's a section for SDSQL.  Just grab that and as usual just unzip and ./sdsql.   This screenshot shows color coding and a nice compact printing of the results of a query.  This results is buffered and the col headers are set to the max of the datasize completely ignoring whatever your linesize is set to.  The catch is only 5k rows is supported since it's buffering.  Query over 5k and you'll get a nice warm message that says unsupported.

Here's my Login.sql if you want to try it out.

set sqlformat ansiconsole
set sqlprompt '"@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue,bg_green,bold >|@"

If you want different colors, here's a script that shows a lot of the possibilities.

select '@|red,bold,underline This is red,bold,underline|@' "@|red Colors|@"  from dual
union all
select '@|NEGATIVE_ON  This is negative|@'   from dual
union all
select '@|INTENSITY_FAINT  This is faint|@'   from dual
union all
select '@|INTENSITY_BOLD  This is my bold|@'   from dual
union all
select '@|ITALIC  This is italic|@'   from dual
union all
select '@|UNDERLINE  This is underline|@'   from dual
union all
select '@|BLINK_SLOW  This is blink_slow|@'   from dual
union all
select '@|BLINK_FAST  This is blink_fast|@'   from dual
union all
select '@|CONCEAL_ON  This is conceal|@'   from dual
union all
select '@|black  This is black|@'   from dual
union all
select '@|green  This is green|@'   from dual
union all
select '@|yellow This is yellow|@'   from dual
union all
select '@|blue  This is blue|@'   from dual
union all
select '@|magenta  This is magenta|@'   from dual
union all
select '@|cyan   This is cyan|@'   from dual
union all
select '@|white  This is white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual
union all
select '@|bg_black  This is bg_black|@'   from dual
union all
select '@|bg_green  This is bg_green|@'   from dual
union all
select '@|bg_yellow This is bg_yellow|@'   from dual
union all
select '@|bg_blue  This is bg_blue|@'   from dual
union all
select '@|bg_magenta  This is bg_magenta|@'   from dual
union all
select '@|bg_cyan   This is bg_cyan|@'   from dual
union all
select '@|bg_white  This is bg_white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual

And here's what you'll see from it.

Oracle XE 11.2 for Windows 64 bit is here

Title says it all.

Get it here:
  http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Node Road show recap. When to use and not use node.

I was recently at a Node road show.  This was very much like the OTN tour just obvious focused on Node.  They had multiple big customers there to share how they used node, what worked, and what doesn't.  There were 3 things that stood out for for me about when when to use Node.

First, that node is designed for high concurrency low latency.  In case you didn't know node has 1 execution thread.  Here's a blog that explains it very well, http://www.aaronstannard.com/post/2011/12/14/Intro-to-NodeJS-for-NET-Developers.aspx.  That means a simple loop like while(true){} will lock up the entire node server.  Now, you may think oracle's OCI is not async so it's all lost.  Node takes care of when it's waiting on I/O.  While waiting on I/O from the database, node.js will push this work to the queue until there's a response then it comes back to the main thread for more execution.

Second, Node as the Front End's Backend.  Yahoo and Rakuten was there and explained how they use node to be the buffer from the front end to the "real" backend.  This makes a place where the front end developers can use what they know , javascript, and perform server side operations.  They can use the same REST based access the browser would use to also access the backend.

Third, A lot of people probably know this but is not meant for thing like finance.  Here's a simple example that math isn't a main point when it matters.  There are plans for Javascript to add a bigdecimal eventually which would remedy this.

Publish data over REST with Node.js

Of course the best way to expose database data over REST is with Oracle REST Data Services.  If you haven't read over the Statement of Direction, it's worth the couple minutes it takes.  The auto table enablement and filtering is quite nice.

For anyone interested in node.js and oracle, this is a very quick example of publishing the emp table over REST for use by anyone that would prefer REST over sql*net.  For example from javascript,php,perl,...



The current best driver for node.js to access oracle is node-oracle which is found here: https://github.com/joeferner/node-oracle


Here's a short set of steps to get going.

1. Install node, http://nodejs.org/download/

2. Setup the environment's oracle variables

export PATH=$PATH:.  
export ORACLE_HOME=/path/to/my/oracle/product/11.2.0/dbhome_1
export OCI_LIB_DIR=$ORACLE_HOME/lib
export OCI_INCLUDE_DIR=$ORACLE_HOME/rdbms/public
export OCI_VERSION=11 #Integer. Optional, defaults to '11'
export NLS_LANG=.UTF8 # Optional, but required to support international characters
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3. run npm install oracle

That's all there is to getting then env setup for oracle.  The other node package I use in this example is express.

4. To install that just type:  npm install express To learn more about express, it's home is here http://expressjs.com/

var express = require('express');
var app = express();
// This make a uri end point for the REST call
// http://myhost/emp will return the data
app.get('/emp', function(req, res){
  empList(req,res)
  }
);

var oracle = require('oracle');
var conn;

var connectData = {
    hostname: "127.0.0.1",
    port: 1521,
    database: "XE", 
    user: "klrice",
    password: "fortim"
}

// connect
//var conn="";
oracle.connect(connectData, function(err, connection) {
    if (err) { console.log("Error connecting to db:", err); return; }
    console.log('connected');
    conn =connection;
});

// function registered in the /emp declaration
function empList(req,res){
     var empList = "select * from emp ";
    conn.execute(empList, [], function(err, results) {
            if (err) { console.log("Error executing query:", err); return; }

            // use the JSON function to format the results and print
            res.write(JSON.stringify(results));
            res.end();
    });
}

// listen on port 8000
app.listen(8000);

The result is that the node script is listening on port 8000 for /emp and will return this JSON.

KScope14's Sunday Database Symposium

    This is the 3rd year Chet aka oraclenerd  and I have organized the Sunday Symposium.  This year has a bunch of great speakers with great content.  So, if you are debating at all getting to KScope a day early to make the symposium, here's the agenda for the DB Symposium and debate no more get there early !

Starting off the APEX and DB tracks will be combined.  

Oracle Database Tools  -  Mike Hichwa 

Mike Hichwa, Vice President of Database Tools, will provide an insight into the latest development of database tools and the Oracle Database Cloud Service. Everybody who is currently utilizing Oracle database tools should attend this session. 

Restful Access to Your Data  -  Kris Rice 

Representational State Transfer (REST) enables data to be shared over HTTP(s) allowing data access from virtually any platform or language. Oracle REST Data Services ( ORDS ) make this easier than ever to RESTfully enable your database. This session will start with an overview of what REST is and why it is growing in popularity as well as cover all the upcoming features in ORDS that includes: Auto enablement of relational data, Oracle Database 12 JSON support, and Oracle NoSQL support.  

Split to separate DB track

Best PL/SQL Features You Never Heard Of (or certainly don’t use)  - Steven Feuerstein

Hop out of the deep ruts of your programming habit and learn how to take advantage of some under-utilized but very powerful features of the finest database programming language in Planet Earth.

Storing and Querying JSON Data in Oracle Database 12c  -  Mark Drake 

JSON has been widely adopted as a mechanism for persisting structured and semi-structured data in recent years. Application developers are particularly attracted to the schema later / schema never nature of JSON, which allows them to evolve their application data model at will. The rising popularity of JSON has been accompanied by wider adoption of 'document stores' such as MongoDB and CouchDB.

    This session will show how new features of Oracle Database 12c allow the database to be used as a JSON document store. It will show how the database can deliver JSON storage, indexing and queryability, combined with performance, and full life-cycle support for JSON data, while still providing developers with all the flexibility that attracted them to JSON in the first place. The session will also touch on new API’s that make it very easy to use the Oracle Database as a JSON Document store. These new APIs are designed to support the programming paradigms and frameworks used by today’s cutting-edge developer They enable the use of popular document centric approaches, such as REST,  to be used to perform Create, Retrieve, Update and Delete operations on JSON documents managed by an Oracle database, without having to make use of traditional relational technology such as SQL, OCI, or JDBC.

Oracle SQL Developer and Oracle Database 12c  - Jeff Smith

Discover and exploit new Oracle Database 12c features such as multitenant container databases and data redaction with Oracle SQL Developer. This session also covers other Oracle Database 12c SQL and PL/SQL language enhancements that are available to all Oracle SQL Developer users. 

Applications HA Solutions for Planned and Unplanned Database Outages - Kuassi Mensah 

Upon database instance outage, applications are confronted with four issues: (1) hang and control loss, (2) error handling, (3) reliable determination of the outcome of in-flight work, and (4) resubmission of uncommitted transactions. This session introduces Oracle Database 12cApplication Continuity, which addresses all four issues. Fast Application Notification addresses the hang, Recoverable Error classification enables applications to interpret SQL exceptions without maintaining a list of error codes, Transaction Guard enables reliable determination of the outcome of in-flight work, and Application Continuity solves the resubmission of the uncommitted transaction. The session also discusses configuration and design considerations.

Hat Trick Tuesday - 3 new releases

  There's 3 new releases today from the database tools teams.  For non-hockey folks, a hat trick is when you get 3 goals in 1 game.  In our world of software, 3 releases at once is the closest thing ( I think ).

First up is SQL Developer, with over 3.5m users now it is by far the database IDE for Oracle databases.

SQL Developer 4.0.1

  The Oracle IDE for the database has come a long ways from when we started.  Everyone should upgrade to this latest patch as there are lots of fixes in there, the full list is here.  The tool continues to grow from where we started with over 3.5m users now.  Looking over the documentation from previous release really shows how much it has grown.

SQL Developer 1.1 Worksheet

SQL Developer Datamodeler 4.0.1

   The same applies to the modeler, there are a bunch of fixes in here.  However, there are also a bunch of new features included in this patch. Such as the mouse wheel now works to zoom in/ out and a dialog to make it easier to add/remove objects from subviews.  The other great thing about the modeler is it's already inside SQL Developer.

Oracle REST Data Services 2.0.6 ( formerly Oracle APEX Listener )

    The biggest change is the Oracle Apex Listener is now named Oracle REST Data Services.  

   What does this name change mean to you?

• Forum Name changed
• OTN URLs, Links changed
• The file name changed apex.war is now ords.war
• Default URL is now /ords

   The biggest reason for this change is to put an emphasis on where the majority of development work is going.  There's a lot of new features coming for REST to easier enable your relational, non-relational, and nosql data in the next release.

   The new name has no impact on the support of Application Express.  The support for OWA toolkit which Application Express relies on will continue to be enhanced.

   We will be building a Statement of Direction soon to let everyone know where this is headed.  In the mean time, there's a lot of example on this blog and on Colm's blog