view counter


Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

My iPhone is back …

Life’s interesting, and thankfully AT&T is no longer the sole provider of the iPhone. On December 21st, 2010, I replaced my iPhone 3G with a LG Optimus S using Sprint. It was adequate, and far cheaper than what AT&T was charging me. It also had 3G service because AT&T didn’t provide coverage in Rexburg, Idaho.

How to use object types?

A tale of Oracle SQL object types, their constructors, and how you use them. This demonstrates what you can and can’t do and gives brief explanations about why.

The following creates a base SAMPLE_OBJECT data type and a sample_table
collection of the base SAMPLE_OBJECT data type.

Analytic sales forecast

Analytic functions have been very helpful at my work. One of the good examples is trying to forecast next years sales for each item taking into consideration seasonal variations and whether the item has been going up in sales the last years or declining. In this blog post I demonstrate how to use REGR_SLOPE for that purpose.We have our sales statistics by month (though we're thinking about going to weekly data.)So for the simple demo we create this very simple table:


Recently on the ODTUG e-mail list ODTUG-SQLPLUS-L a user highcharge asked how to take daylight savings time into consideration when calculating number of hours between two DATE values. If you merely subtract the dates, twice a year you will be one hour wrong.Ideally you might switch to datatype TIMESTAMP WITH TIMEZONE, but sometimes that is not an option (or sometimes perhaps "overkill" for the situation :-) Fortunately there is another way.

Optimization of Cursor FOR Loops and Implicit Queries (11588)

The 8 February 2011 quiz tested your knowledge of the performance implications of different ways of fetching a single row of data, ranging from the use of OPEN FOR with a cursor variable to a cursor FOR loop.

The key objective of the quiz was to make sure you were aware that when you open a cursor variable, Oracle always performs a parse.

But we also scored as incorrect the choice that stated:

Winner Selected for Hierarchical Queries Challenge (8471)

In October 2011, we posted a competition regarding hierarchical queries, with this introduction:

Some words on result cache dependencies tracking in 11.2.

First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third,  the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.
This has several consequences.

APEX: Show Actions menu with Authorization

With an Interactive Report you can choose whether you want the Actions Menu to be shown or not. This has to be done by the developer. Of course the customer wanted to have this based upon the existing authorization scheme. How can you solve this?

Discovering the Author within me: My upcoming book

Happily I would announce the listing of my upcoming book at PacktPub publishers website. The book is expected to be released by May, 2012. The book is for the Oracle Associate professionals who are aspiring for Professional level exam (1Z0-146 certification). “The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts.

Function or Procedure?

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

view counter