Whenever One need to fetch column value from table I have seen following two variance of coding. One is straight away using SELECT INTO (example1) to do so and other one is through use of cursor(example2) .

In my company I am following CURSOR WAY i.e example2 of doing it, as I think its a elegant way of handling it. But recently one of my ex collegue told me that its not a right way and their company is follwing example1. When I asked why he told me that it makes finding bugs easy, Cursor way bugs get hidden.

My point is that in both ways we need to handle errors, but NO_DATA_FOUND is a condition, its not an error.

Any way discussion on this can go to never ending tale.

So Later I though about it and did some research in terms of resources both consume.

I created following two functions:

CREATE OR REPLACE FUNCTION example1 RETURN VARCHAR2
IS
i VARCHAR2(10);
BEGIN
BEGIN
SELECT ‘x’ INTO i FROM DUAL WHERE 1 = 2;
RETURN i;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
END;
END example1;

CREATE OR REPLACE FUNCTION example2 RETURN VARCHAR2
IS
BEGIN
FOR c1 IN (SELECT ‘x’ col1 FROM DUAL WHERE 1 = 2) LOOP
RETURN c1.col1;
END LOOP;
RETURN NULL;
END example2;

Then I ran following PL/SQL block to collect statistics:

DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ’selectinto’);
FOR i IN 1 .. 1000000 LOOP
x := example1;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
END;

DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ‘forcusor’);
FOR i IN 1 .. 1000000 LOOP
x := example2;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
l_result := DBMS_PROFILER.FLUSH_DATA;
END;

After this when I checked my result I was shocked to see the results (I am not saying everybody will feel same):

SQL> SELECT runid, run_date, run_comment, run_total_time
2 FROM plsql_profiler_runs
3 ORDER BY runid;

RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
—— ——— ————————————————– ———————–
11 14-AUG-07 selectinto 289,487,951,977,136.00
12 14-AUG-07 forcusor 19,888,421,734,047.00

Other observation for both procedure my CPU usage went more than 50% percent (Intel Core 2 T7200 2.00 GHz), but time it running at that level was as per result!

wow!