Abstract Confusions

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

Tag Archives: Oracle

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

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 this helps in greatly reducing the execution time of an often repeated complex process inside a function which gets same repeated input values. This will increase performance in high concurrent applications.

In Oracle 11g Release 1

It is mentioned, the best candidate for result caching is function that is invoked frequently and depend on information that changes infrequently or never. Consider the following function.

CREATE OR
REPLACE FUNCTION get_sal (p_emp_id in hr.employees.employee_id%type)
RETURN NUMBER
RESULT_CACHE
IS
lv_sal_rtn   hr.employees.salary%type;
BEGIN
   select salary
      into lv_sal_rtn
     from hr.employees
   where employee_id = p_emp_id;

 RETURN lv_sal_rtn;

EXCEPTION
  WHEN others
    then 
           RETURN 0;
 END;

Note: the function execution values are stored in SGA by explicitly mentioning RESULT_CACHE. And the downside of this is, at times you can get wrong return values. This is because the values are already cached in memory and not calculated again. Consider the following example. Read more of this post