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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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. [...]

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 [...]

Automatic Detection of Data Sources of Result-Cached Function

Oracle 11g release 1 gave the option of caching function results. It is done using the optional reference of mentioning RESULT_CACHE keyword. The values of these function calls are stored in system global area (SGA). It will have one result for every unique combination of parameter of passed. These are available for every session. Thus [...]