17 Jan 2013 @ 11:26 PM 

In this post, we’ll talk about fetching collections of data into structures, using CURSORS and FETCH.

To fetch a collection of data row by row, consider the following block of code –

declare

cursor cursor1 is
select tabname, defname from REFTABLE order by tabname,refno;
tableName REFTABLE.tabname%TYPE;
expresion REFTABLE.defname%TYPE;

begin

open cursor1;
loop

fetch cursor1 into tableName,expresion;
EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL;

dbms_output.put_line(cursor1%ROWCOUNT || ‘. ‘ || tableName);
— INSERT FURTHER PROCESSING HERE

end loop;
close cursor1;

end;

In case you are dealing with huge collections and you only want to process the first 100, substitute the following line with the ‘EXIT WHEN’ in the code above –

EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL OR cursor1%ROWCOUNT>100;

CURSOR ATTRIBUTES (such as NOTFOUND) can be used to control the fetching behaviour.

The above code block is a useful way of fetching collections of data row-by-row. In the next post we’ll talk about BULK COLLECT, which fetches entire collections much faster.

Posted By: Kevin
Last Edit: 17 Jan 2013 @ 11:26 PM

EmailPermalinkComments (0)
Tags
Tags:
Categories: basics, Snippets, SQL
 09 Jul 2012 @ 10:53 PM 

Improper handling of NULL values is a common cause of application failure. This is true for applications built on both Oracle and Sybase.

We’ll talk about how to avoid such problems when building where clauses in your SQLs.

Firstly, a NULL value is a value that is not known. A NULL value cannot be compared correctly with other values- even with another NULL value.

When building your where clauses, use the IS NULL and the IS NOT NULL statements. The following example can be used to compare columns between 2 tables –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(T1.COL1<>T2.COL1) or (T1.COL1 is null and T2.COL1 is not null) or
(T1.COL1 is not null and T2.COL1 is null)

Another solution is to use the DECODE statement. To perform the above operation, consider –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(decode(T1.COL1,T2.COL1,1,0)=0)

Depending on the needs of your application, you may need to handle NULL values in different ways. NVL can be used in select statements to replace returned NULLs with a user defined expression.

Posted By: Kevin
Last Edit: 09 Jul 2012 @ 10:57 PM

EmailPermalinkComments (0)
Tags
Tags: ,
Categories: basics, Snippets

 Last 50 Posts
Change Theme...
  • Users » 66
  • Posts/Pages » 25
  • Comments » 4
Change Theme...
  • VoidVoid « Default
  • LifeLife
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LightLight