Top 60 Oracle Blogs

Recent comments

Oracle Development

Oracle 12c SQL – Using JSON

JSON Is Part of Oracle 12c

One of the more-exciting features (at least to me) of Oracle 12c is the support for JSON now built into the database.

What is JSON?
JSON (JavaScript Object Notation) is a language-independent open data format (see for more). JSON stores text in name-value pairs. Originally usedin JavaScript, JSON is now also in: Java, R, .NET, PHP, Python, Node.js, and Oracle. JSON is most-often used for data interchange but is frequently used to pass data to and from REST-style web services. Increasingly JSON is becoming favored for big-data persistence.

OTN APAC Tour 2015


Some of you might know I was asked to fill in on short notice for a colleague on the OTN APAC (Oracle Technology Network Asia-Pacific) tour — what a lucky break for me! The OTN tours are an ongoing effort of Oracle’s to support local user groups with well-known speakers (mostly Oracle Ace Directors and Oracle Aces). Local user groups get in touch with OTN, an Ace Director (Francisco Alvarez from Australia in this case) organizes things, and OTN helps defer the costs. Thanks Oracle; user groups need all the help they can get.

Shanghai, China

Cloud Perspective: Cool New Stuff or Same Old Stuff ?

Is “The Cloud” Cool New Stuff or is it the Same Old Stuff ?

Cloud is upon us! Everywhere you turn vendors are offering cloud solutions promising (once-again) a single solution to solve the ills of our IT organizations.

Bah! Some naysayers say “this is just the same-old, same-old, we’ve been doing the cloud for years” – hmmmm is this really true?

Another Pretty UI – Poor UX Story

Last week my wife and I experienced yet another case of someone creating a really nice UI but designing a lousy User Experience.


Oracle 12c updates the TRUNCATE TABLE statement allowing the use of CASCADE to eliminate values in tables that are referentially connected.

This option is something that some shops were looking for from Oracle; it is possible that your shop will not find the need for this option.


Here is the syntax:


(other TRUNCATE TABLE options such as those for MATERIALIZED VIEWs and/or STORAGE may still be specified)



  • Child table referential security must specify “ON DELETE CASCADE” or the statement will fail
  • If layers of tables are referentially connected and all specify “ON DELETE CASCADE” the system will CASCADE as directed


Here is some example code:


*** Setting up sample tables ***

Oracle Database Developer Choice Awards

Hey, do you know a developer who should be recognized as one of the worlds best at: SQL, PL/SQL, Oracle REST Data Services, Oracle Application Express, or Database Design?

Oracle Technology Network (OTN) is sponsoring the Oracle Database Developer Choice Awards to help recognize your superstars as they deserve.

Please go to to learn more about the awards and how to nominate people.

Open Source is Free – Free like a puppy!

I’m at the ODTUG Kscope conference in Hollywood Florida and was just talking with some Oracle folks about Open Source (yes, Oracle has people devoted to working with Open Source tools) and I shared with them my general comments to students and colleagues about Open Source.

There are two kinds of free:

1. Here’s a free cup of coffee (or beer or soda…)

2. Here’s a free puppy

When accepting option 1 (free coffee) you take it, consume it, and enjoy.

When accepting option 2 (free puppy) you take it, you find a place for it to sleep, you take it to the vet, you walk the puppy, you feed the puppy, and oh-yeah — every once in a while the puppy might make a mess on your floor! You’ve made a commitment.
(I cannot take credit for the metaphor; I first heard it used by friend and colleague Jim Cody of Cardinal Directions – thanks Jim!)

Clearly, “free” is not always “completely free”

Oracle 12c – PL/SQL Implicit Result Sets

Implicit Result Sets

Beginning with Oracle 12c it is possible for SELECT statements to send results back to a client from PL/SQL without need for REF CURSOR definitions. Previously, PL/SQL required definition of a REF CURSOR as an OUT parameter adding complexity (and precision) not found in other products (argh… the older I get the more I believe that a module should do exactly one thing (and do it well)). However, it is common in some database products to use a Stored Procedure to return multiple result sets.

Using Oracle 12c’s newly implemented implicit result sets will allow reuse of SQL Server (and other database) stored procedures that return implicit result sets. Implicit result sets were originally targeted at JDBC clients but will probably work well in other environments like C#.NET or SQL Server TSQL.

Oracle 12c SQL – Expanded VARCHAR2 (32k)

Extended VARCHAR2

Oracle 12c now allows an expanded maximum size for VARCHAR2, NVARCHAR2, and RAW columns; CHAR and NCHAR are unchanged.
Three varying character datatypes may now grow to a maximum size of 32,767 bytes (4,000 was the previous limit):

  • VARCHAR2       32767
  • NVARCHAR2    16383
  • RAW                    32767

Expanded VARCHAR is not the default; it must be enabled by DBAs (see following).

Extended VARCHAR Setup

DBA activities to setup extended VARCHAR include:

  • Set init parameter:  max_sql_string_size = EXTENDED
  • May be set when creating database
  • To change an existing database
  • Bring database to UPGRADE mode first
  • Execute upgrade script (utl32k.sql)

Once made, the change to extended is irreversible!

Using Extended VARCHAR

Extending VARCHAR maximum size should be researched carefully:

Oracle 12c SQL – Session-Specific SEQUENCE

Session-Specific Sequence

CREATE SEQUENCE now offers a SESSION parameter allowing a sequence to be reset in each session where it is used.

create sequence session_sample_seq
start with 1 increment by 1

When using session-specific sequences; values will reset each time a new session begins. This works the same way that a Global Temporary Table is reinitialized (default is GLOBAL). Rows in Global Temporary Tables exist either for the life of the session or transaction; so resetting sequences makes sense in many cases.

While particularly useful for GTTs; session-specific sequences are NOT limited to GTTs.

It is possible to use ALTER SEQUENCE to convert a SESSION sequence to a GLOBAL (normal type of sequence) and back (be sure to test carefully if you decide to do this you might have unintended consequences!)