The Snowflake Data Sharehouse. Wow!

With Snowflake Data Sharing, you can now easily transform your data into a valuable, strategic business asset.

Removing Outliers using stddev()

A colleague asked me about removing outliers from reports and I knew just the function to give a go.

Some time ago I had to prove that some data we had conformed to a normal distribution. Remember those from high school?
A normal distrubition (bell curve)
That request was about 2007 and I ended up using stddev() and lpad() to produce a vertical shaped bell curve in SQL*Plus. That was pretty cool.

As for removing (more...)

More on Optimistic Locking with ORA_ROWSCN

Thanks to comments by Tony Hasler and pingbacks from Jeff Kemp, here's more detail on how optimistic locking works with SCNs, especially with respect to "restarts" during update.

Annual Oracle release cycle coming?

Those of you on Twitter at the end of May, and certainly those at DOAG might have seen this announcement.

I'm no DBA, but that first sentence in the picture has the potential to be rather game changing.
Annual Feature Release of (more...)

Optimistic Locking: One SCN to rule them all

Previously I showed how to avoid lost updates with ORA_ROWSCN. Now let’s create an API that avoids lost updates with just one SCN. What kind of API? A transaction consists of one or more changes to data that should happen together: either all should happen or none. When the transaction commits, all the changes happen; […]

Snowflake at Stoweflake

Every year the World Wide Data Vault Consortium (WWDVC) gets better and better! This year’s event was the 4th Annual and was again held at the lovely Stoweflake Mountain Lodge in Stowe, Vermont. And once again this year, my employer, Snowflake Computing, was a proud sponsor of the event. This year I even got to […]

Oracle SQL Strip Quotes

Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:

SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message"
FROM   dual;

It will print:

Hello World!

As always, I hope this helps those looking for a solution.

Write (Even) Less with More – VALIDATE_CONVERSION

I wrote the post Write Less with More – Part 8 – PL/SQL in the WITH Clause in November 2015, when the latest released Oracle version was 12.1.
In that post I explained about PL/SQL in the WITH Clause – a new 12.1 feature – and demonstrated it using the following example:


Since then Oracle 12.2 was released, and introduced a new feature that enables solving this task in a simpler way – the VALIDATE_CONVERSION function. This function gets an expression and a data type, and returns 1 if the expression can be converted to the data type and 0 if not.
Using the same setup from the original post, the requested query becomes as simple as:

> select *
  from   people
  where  general_info is not null
  and    validate_conversion(general_info as date, 'dd/mm/yyyy') = 1;

---------- ---------- --------------- --------------------
       102 Paul       McCartney       18/6/1942
       202 Ella       Fitzgerald      15/6/1996
       203 Etta       James           20/1/2012

In addition to introducing the new VALIDATE_CONVERSION function, the older CAST and some of the TO_* conversion functions have been enhanced in Oracle 12.2 and include a DEFAULT ON CONVERSION ERROR clause, so when data type conversion fails we can get some default value instead of an error.

> select p.person_id,
         to_date(p.general_info default null on conversion error, 'dd/mm/yyyy') my_date
  from   people p;

---------- ---------- --------------- ----------
       101 John       Lennon
       102 Paul       McCartney       18/06/1942
       103 Ringo      Starr
       104 George     Harisson
       201 Louis      Armstrong
       202 Ella       Fitzgerald      15/06/1996
       203 Etta       James           20/01/2012
       317 Julie      Andrews

8 rows selected.

The post Write (Even) Less with More – VALIDATE_CONVERSION appeared first on @DBoriented.

Avoiding Lost Updates with ORA_ROWSCN

Applications today use "optimistic locking" to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn't work (see ORA_ROWSCN for optimistic locking). I now think it does!

JDBC, Linux and Entropy

Some troubles — especially those happening only sporadically — are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system, particularly random number generation. This case was suited well to demonstrate an approach to trouble-shoot […]

PL/SQL in SQL in View in SQL in PL/SQL

I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017.
One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL (see the section PL/SQL in SQL in PL/SQL in this post).
I was asked, regarding this restriction, if it’s (more...)

Presentation: Dynamic Actions, Javascript, & CSS for APEX Beginners

My first webinar didn't do so well, perhaps my new home will have suitable internet capability, in this "innovate nation" of ours...

My second one on my Boggex game went ok, though the room the locals were in was like an sauna.

My third attempt had it's own set of issues.
1) Ubuntu as an OS doesn't seem to enable broadcasting via GoTo Meeting.
2) The wireless policy in the Oracle building is effective, but (more...)

I’m still here.

It has been some time since my last confession.
It's been a while, but sometimes you get busy, ya know?

And a different sort of busy, the sort that left me wanting to escape to a different place. I spent a bit of time playing an old favourite, Civilisation.

I really like to write, consolidate ideas, and share insights in this forum, but it's rare that I have such a gap.

I also miss it.


Änderungen im SQL-Plan-Management mit 12.2

Wieder ein neuer Artikel von Jonathan Lewis: diesmal weist er auf einige interessante Änderungen hin, die sich beim SQL Plan Management mit 12.2 ergeben haben:
  • SQL Patches sind jetzt ein offizielles Feature: ihre Verwaltungsroutinen sind vom Package dbms_sqldiag_internal ins Package dbms_sqldiag gewandert.
  • der Hint-Text für SQL Patches ist nicht mehr auf 500 Zeichen begrenzt, sondern als CLOB definiert.
  • der zugehörige Funktionsname lautet nun create_patch (bisher i_create_patch).
  • neben der älteren Variante, bei der der (more...)

New Snowflake features released in Q1’17

This post provides an overview of the major new Snowflake features we released during Q1 of this year, and highlights the main value they provide.

Partitionierungs-Optionen in 12.2

Jonathan Lewis zeigt in seinem jüngsten Artikel die große Flexibilität, die die Definition von partitionierten Tabellen in Oracle 12.2 errreicht hat. Dabei liefert er größeres Code-Beispiel für ein ALTER TABLE ... MOVE, in dem folgende Punkte aufgeführt sind:
  • List-Partitionierung über mehrere Spalten.
  • automatic: das Schlüsselwort, das die Generierung neuer Partitionen für neu ankommende Daten erlaubt - das entspricht damit dem Interval-Partitioning für Ranges, das man schon aus älteren Releases kannte.
  • indexing off: erlaubt die Beschränkung (more...)

JavaScript code linting using ESLint on Oracle Developer Cloud Service

This blog will help you understand how to execute ESLint tool for linting JavaScript code. This blog will cover the configuration required for the ESLint tool as well as for Oracle Developer Cloud Service to execute the configured rules on the code pushed to the Git repository on Developer Cloud Service.


What is code linting?

Linting is the process of checking the source code for programmatic as well as stylistic errors.


Why use (more...)

Mocha.js for Test Automation of Node.js REST API on Oracle Developer Cloud Service

This blog covers the testing automation of Node.js based REST service application using a popular testing framework called Mocha.js on Oracle Developer Cloud Service. The Node.js application will be deployed on Oracle Application Container Cloud Service.



Note: For details on how to develop and deploy NodeJS REST application on Application Container Cloud using Developer Cloud service, please refer the blog link here.


Tech Stack Used

Eclipse: IDE for Nodejs (more...)

Read list of a dictionaries

My students wanted a quick example of how to read a list of a dictionaries in Python. So, here it is:


# Declare list of dictionaries.
cakes = [{'cake':"vanilla",'frosting':"chocolate"}

# Read the list of dictionaries.
for lkey, lvalue in enumerate(cakes):
  print lvalue['cake'] + " with " + lvalue['frosting'] + " frosting."

Naturally, a list can contain many things and you should ensure each value you read is a dictionary before trying (more...)

Oracle REST Data Services and Docker

TL;DR 1) check out 2) Download ORDS ; optionally SQLcl,APEX 3) Build w/DB connection details docker build -t krisrice/ords:3.0.10 --build-arg DBHOST= --build-arg DBSERVICE=orcl --build-arg DBPORT=1521 --build-arg DBPASSWD=oracle . 4) Run the image docker run -d -p 8888:8888 -p 8443:8443 --name=ords krisrice/ords:3.0.10 5) Access