17 October 2012

OOW 2012: Little things make me happy

Yesterday evening we had the annual "We went to OOW and this is what's new in the world we call Oracle" at AMIS. Starting around 5pm and ending around 10pm a lot of information was presented by several AMIS colleagues.
My contribution to the evening was (amongst others) a little bit of new features in SQL and PL/SQL, only a little bit as there is too much to cover in just 20 minutes.
It's the little things that make me happy. What is the little thing that I like?
When you define a table you can assign a default value to a column (here it comes) based on a sequence. Take a look at the following (taken from the presentation Tom Kyte did during OOW):
First a sequence is created, with the resounding name S. Next the table (named T) is created where the column X has a default based on the NEXTVAL of the sequence S.
Just to repeat the line of interest:
x int default s.nextval primary key
How cool is that?
No longer will you need to create a trigger to fill the column with the nextvalue from a sequence, you just declare it this way and you're done.
Maybe it's just syntactic sugar, I don't know (yet) and frankly I don't care. I like the fact that this feature makes it more declarative to populate the primary key (or any other column of course).
I'm happy.

02 October 2012

SQL Developer tip: Don't open the table

One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.
There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings:
Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click"
And that's it.

OOW 2012: Is PL/SQL still alive?

Yesterday Bryn Llewellyn, Distinguished Product Manager, did a session called "PL/SQL Enhancements brought by the latest generation of database technology". The room was packed. For the upcoming release of the Oracle 12c database the enhancements were broken into three areas:
  • Improved PL/SQL and SQL interoperability

    In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.
    The types that can be used as bind variables has been extended: Boolean, Nested Tables can be used as bindvariable in Dynamic SQL.
    Subquery Factoring (the With clause if you will) can hold private functions, the use case presented for this: pretty print an integer in K, M, G, T.
    Using userdefined PL/SQL functions in a SQL statement is not advisable, but if you really need to there is a new pragma: PRAGMA UDF. With this pragma the statement is almost as fast as pure SQL.

  • Security

    A new step has been taken to continue on "least privileges", meaning that the user only gets the privileges that are absolutely necessary. A Role can be granted to a PL/SQL unit.

  • Improved programmer usability

    The ability to inspecting the callstack has been improved by the introduction of a new package: UTL_CALL_STACK. The package contains numerous functions to help in this area, such as current_edition and lexical_depth. White listing of program units: restrict which program unit can be called by other program units.

So, is PL/SQL still alive? YES! ALIVE AND KICKING!