Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Category Archives: Oracle

Commenting your code

It’s been a while since I blogged. I was actively following lot of topics, but mostly held my thoughts to myself from writing.

One such article I read was: Who needs comments? My code is self-documenting! aka: Comment tersely with value-added information.

The author makes the following point:

/* If the first field of the properties record is N... */
IF master_list(l_curr_index).properties_flag.field1 = 'N' THEN
a better way of writing it would be
/* If the customer is not eligible for a discount... */
IF customer_not_eligibility (l_curr_index) = 'N' THEN
As part of the suggestion, he suggests it is better to write a function, and code the business validation. Once that is done, there is no need for the preceding comment. The function is self explanatory.
I know coding style differs. But I am particularly edgy about a function returning TRUE for a negative check (I know, I know, it is just me, but still saying). I would rather rewrite the following:
IF customer_not_eligible (l_curr_index) THEN

into

IF NOT customer_eligible (l_curr_index) THEN

this way, I can use the same function to see if the customer is eligible (otherwise, you would be doing, “IF NOT customer_not_eligible (l_curr_index) ” which is double negation and could be confusing.

How would you do?

Oracle Application Express (APEX) Interview Questions

Oracle Application Express (APEX) is a rapid application development tool developed by Oracle for developing rich database centric applications. It first started as HTMLDB and later renamed as Application Express, though it is often shorten as APEX. APEX is a free feature that can be enabled from Oracle database v 9.2 onwards. It means, Oracle Application Express can be installed with the free version of Oracle 10g Express Edition. It is easy to create and run web applications which look professional and scalable. Of recent there is increased interest on APEX skills.

I had an opportunity to interview few people for evaluating their APEX skills. Following is the selected set of questions I used in that interview. The difficulty level is basic to intermediate level. The main idea is to identify the promising people who had real experience on APEX. I have added links to the concepts, documentation. Read more of this post

Compound Trigger – A Performance Improvement for Trigger Executions

In Oracle database 11g release version 1, among the enhancements in trigger execution an important and efficient feature known as ‘Compound Trigger’ is rolled out. This feature is particularly exciting because of the performance improvements it may bring in along with it.

Auditing Bulk INSERT/UPDATE of a Table Efficiently

Assume you have a transactional table, this table is highly accessed by a front end application which does a lot of inserts and updates every minute. You also have a functional requirement of keeping track of the changes against the values inserted / updated. The traditional setup one would have for such a scenario is to write a separate trigger each for AFTER INSERT and AFTER UPDATE to fetch the values and insert them into an auditing table as well. Read more of this post

Trigger Enhancements in Oracle 11g Release 1

Prior to Oracle 11g Release 1, if you have two (or more) triggers on a table for same action – event (BEFORE, AFTER – INSERT, DELETE, UPDATE), there is no guarantee in which order there will be fired. Oracle database 11g Release 1 has introduced improvements in triggers.

On Order of Execution of Multiple Triggers

Prior to Oracle database 11g, developers can never assure you the order of execution of triggers. If you have more than one trigger set on the same triggering conditions, Oracle documentation says that, the order of execution will be random.

Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

This was a hindrance in many scenarios. Most of the time PL/SQL developers would do away with this in simple manner. They will write one trigger and let it to call all other similar triggering actions. Although this doesn’t look like a good practice, it was at least better than to leave it for Oracle to decide at random. Read more of this post

Oracle: On Functions Returning Two or Multiple Values

Mathematically, a deterministic function always returns same value no matter how many times you call. An exception is random function. Any function which involves a random functions will return different values at different times. Here, we are going to discuss about a deterministic Oracle function returning multiple values. To put it simple, In Oracle, can a function have two OUT parameters? In short, yes.

In Oracle, we always know that a function can and should return a value. Is it possible to send two return values?

Checkout.

Oracle Database and Partial Differential Equations (PDE)

I was going through this website and found this interesting offer on Oracle Database 10g: Complete Reference. This wonderful book is sold for INR 63 (+100 for handling and postage), and in total INR 163 with companion CD. It still worked out to a good deal. Until I found the related titles, which the site suggested, “If you like the above item, you might also like the following items:” and listed down books from Partial Differential Equations (PDE).

Now, please don’t get angry. Either there has to be hidden connection between Oracle and PDE or the site some how read my mind. You can also try and it might still show up to you. Even I would like to know where there is any relation between Oracle Database and Partial Differential Equations.

Native Webservice Inside Oracle Database

Lot of times, developers configure and build web service using Java, ASP or some other technology.

In most of the times, the web services called from Java program (or some thing of same sort) connects to database, pulls out chunk of data, processes them and returns them in nice XML structure. The program which called the web service gets the XML, and processes it and presents it to the viewer.

In Oracle database release 10g, Oracle supports native web services. i.e., you can setup and configure web services right inside database. So, how to do that? Let’s start with creating a user and a DAD for out testing.

CREATE USER test
IDENTIFIED BY test
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
/

Read more of this post

Virtual Columns in Oracle 11g R1

I just found out this good feature in Oracle 11g release 1. How many times you felt tired because you were coding again and again for a particular function while selecting data from table?

Consider the following table. The table contains employee name, date of joining. Year of joining will not be stored in database as it can be always derived from one data. An dependable data! Assume, you need it desperately for some sake. One way is use views to achieve the purpose.

With Oracle 11gR1 virtual columns, you can add an virtual column in table for easy selection.

SQL> create table empTable
 2         ( name varchar2(20)
 3         , doj date
 4         , yoj generated always as (to_char(doj,'yyyy')) virtual
 5         )
 6 /
Table created.

Read more of this post

LISTAGG and PIVOT functions in Oracle 11g R2

Analytic functions are first introduced in Oracle 8i. They have got few functions in new release. The first function which I will discuss here will be LISTAGG. It creates a delimited list within a group. Some times you need to toil hard for this. Most of the times, grouping by and converting columns to rows.

With Oracle 11g R2, all you need is the following.

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from hr.employees
  6    group by deptno
  7    order by deptno
  8   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

You can decide what to append with. The syntax is LISTAGG(column, character). Read more of this post

Width Bucket – Analytic Function

WIDTH_BUCKET function is used to assign bucket value for a list of values based on maximum and minimum number. This is one of the new analytic function introduced in Oracle 10g. Consider the following table.

StudentId Mark
1 56
2 43
3 76
4 89
5 90
6 91
7 49
8 63
9 83
10 74
Read more of this post