KScope14

ODTUG_Kscope14

This year, from June 22 - June 26, the best convention in the world, ODTUG’s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world.

Some of the presentations I am looking forward to:

In the Developer’s Toolkit track:

Schnelle Datensatz-Generierung

Im OTN Forum hat sich dieser Tage ein Thread mit einer recht interessanten Diskussion zum Thema der schnellsten Möglichkeit zur Erzeugung einer großen Mengen von Test-Datensätzen ergeben. Ausgangspunkt war, dass der Fragesteller des Threads mit der Performance eines INSERT mit einer connect by level Generierung von 10 Millionen Datensätzen nicht zufrieden war und nach schnelleren Optionen suchte. David Berger schlug eine PL/SQL-Lösung mit Collections und Bulk Insert vor, die tatsächlich im System des OP zu (more...)

Language and UX: CRUD can also be a Fancy Name for Crap

If you can guess the PL/SQL statements used by transactions or the names of your database’s columns by looking at the labels on input fields or action buttons in an application’s UI, then consider yourself enlightened. You’re ready to improve the user experience.

Take a look at the UI (more...)

Non-intrusive SQL Trace instrumentation on legacy PL/SQL code

Problem

Legacy PL/SQL code with intermittent performance degradation.

To improve the performance of this code, the first step is to diagnose it. But this code has no instrumentation whatsoever, it is in Production, and rolling any code into Production usually requires rigorous testing. So, whatever method we implement has to (more...)

AJAX in APEX

AJAX is becoming important in the world of web applications. APEX has provided us a very easy way to create an AJAX process, by using dynamic actions. Using PL/SQL Actions in Dynamic Actions to communicate with the database without submitting the page will suffice in most cases, but the downside (more...)

Readable Code for Modify_Snapshot_Settings

It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road (more...)

Yet another one on ‘Oracle Type inheritance and advanced type casting’

Triggered by a comment on an older article I wrote a little article yesterday on determining the instance of an object. As far as known by me at least and seemingly by Google there is no java instanceOf counterpart in Oracle Pl/Sql. In SQL Where clauses you can use the (more...)

Another one on "Oracle Type inheritance and advanced type casting"

Already four and a half year ago I wrote an article on Oracle Type inheritance and type casting.  See here. But although Object types are supported by Oracle since Oracle 8, it seems to me that there is still little familiarity on the subject.
I'm not really a Pl/Sql (more...)

Running PL/SQL Procedures in Parallel

As your data volumes increase, particularly as you evolve into the big data world, you will be start to see that your Oracle Data Mining scoring functions will start to take longer and longer.  To apply an Oracle Data Mining model to new data is a very quick process. (more...)

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he (more...)

APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve (more...)

Oracle Database 12c PRAGMA UDF and WITH clause enhancements

Here are two interesting enhancements in Oracle database 12c PL/SQL. PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can (more...)

SQL_ID aus SQL_TEXT erzeugen

Dass die SQL_ID als Hash für einen gegebenen SQL_TEXT erzeugt wird, konnte man vor längerer Zeit bei Tanel Poder erfahren. Jetzt hat Carlos Sierra eine handliche PL/SQL-Funktion veröffentlicht, mit der man die SQL_ID zu einer Query vor ihrer Ausführung berechnen kann (und im Artikel auch die Links auf den Artikel (more...)

New York Oracle User Group Fall Conference Materials

Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time - with me. I sincerely hope you found both the presentations enlightening as well as entertaining.

Please see the details of the sessions below along with the (more...)

Oracle 12c OFFSET…FETCH.. bug?

Working on the chapter that covers best ways of manipulating with object collection. Of course, if you talk about pagination of the rowset, it is very tempting to test Oracle 12c new feature - OFFSET…FETCH.

Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH (more...)

JSON parser for PL/SQL in Apex 5 ?

I was looking at the Apex 4.2.2 installation script (coreins.sql), and noticed the mention of a new package, wwv_flow_json, which is apparently being worked on but was removed at the last minute "as no longer required for 4.2.2".

Even though the package is not installed (more...)

Logger: a PL/SQL Logging and Debugging Framework

Martin D’Souza:

Logger is a PL/SQL logging and debugging framework. It’s used in many organizations to instrument code in their Oracle applications. Tyler Muth created Logger a few years ago and has since released several upgrades, the last being 1.4.0. After some great feedback, I’m pleased to announce (more...)

An Oracle Designer Feature: Generating Table APIs with Oracle SQL Developer

Have you heard of Oracle Designer? Perhaps you even still use it?

Designer incorporates support for business process modeling, systems analysis, software design and system generation.

I don’t want to get you too excited, because this technology is being phased out. You can read the official statement of direction here (more...)

Dynamic SQL and RESULT_CACHE

Over the weekend one of my friends asked me for a clarification on RESULT_CACHE - a really interesting functionality that allows Oracle to cache in SGA results of PL/SQL function calls.

The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature (more...)

Performance tuning. Spending time is NOT OK (if you do not know exactly why)

Yet another performance tuning story, similar to one that happened about a month ago. Just to keep it short:

  • once upon a time there was a very time-consuming module.
  • eventually we were forced to take much closer look - WHY is it so time-consuming
  • we found in our own (more...)