Abstract Confusions

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

Category Archives: PL/SQL Functions

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


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?


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.


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   /

---------  --------------------
  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