Mohamed Houri’s Oracle Notes

February 8, 2007

Senior Oracle developer interview II

Filed under: Oracle — hourim @ 12:08 pm

Question 3: Could you explain the difference between an empty collection and a not null collection object type?

Suppose we have the following object type definition


09:45:17 MHO> desc gsp.o_npk_stihis

Name           Null?              Type
 -------------- ----------------- -------- ----
 INDICEIP                         VARCHAR2(6)
 INDICEIC                         VARCHAR2(50)
 POSTINGQNV                       NUMBER
 MOVEMENTIDE                      NUMBER
 MOVEMENTTYPE                     VARCHAR2(6)
 TRADEDATE                        DATE
 BALANCEDATE                      DATE
 PORTFOLIOLABEL                   VARCHAR2(6)
 PORTFOLIOIDE                     VARCHAR2(50)
 COUNTERPARTIDE                   VARCHAR2(50)
 SECURITYTYPE                     VARCHAR2(3)
 SECURITYIDENTIFIER               VARCHAR2(30)
 NEWSOLDE                         NUMBER
 BALANCETYPE                      VARCHAR2(6)

to which we’ve attached a collection


09:45:26 MHO> desc gsp.col_npk_stihis
 gsp.col_npk_stihis TABLE OF GSP.O_NPK_STIHIS
 Name        Null?               Type
 ------------------------------- ------------

INDICEIP                        VARCHAR2(6)
 INDICEIC                        VARCHAR2(50)
 POSTINGQN                       NUMBER
 MOVEMENTIDE                     NUMBER
 MOVEMENTTYPE                    VARCHAR2(6)
 TRADEDATE                       DATE
 BALANCEDATE                     DATE
 PORTFOLIOLABEL                  VARCHAR2(6)
 PORTFOLIOIDE                    VARCHAR2(50)
 COUNTERPARTIDE                  VARCHAR2(50)
 SECURITYTYPE                    VARCHAR2(3)
 SECURITYIDENTIFIER              VARCHAR2(30)
 NEWSOLDE                        NUMBER
 BALANCETYPE                     VARCHAR2(6)

We would like to use them in a piece of PLSQL code which resemble to this:


DECLARE

v_npk_col_stihis Gsp.Col_Npk_Stihis;
 v_npk_stihis Gsp.O_Npk_Stihis;BEGIN

-- initialize object type collection
 v_npk_col_stihis := Gsp.Col_Npk_Stihis();

IF v_npk_col_stihis is null then
 dbms_output.put_line('collection is null');
 ELSE
 dbms_output.put_line('collection is not null');
 END IF;

END;

Execute the above plsql code and you will see that the collection is not null. We can conclude that once a collection is initialized it becomes not null.

Since the collection is not null, then let’s get its last binary integer index. The above code is re-used with a very few modifications


DECLARE

v_npk_col_stihis Gsp.Col_Npk_Stihis;
 v_npk_stihis Gsp.O_Npk_Stihis;
 j_last BINARY_INTEGER;BEGIN

-- initialize object type
 v_npk_col_stihis := Gsp.Col_Npk_Stihis();

IF v_npk_col_stihis is null then
 dbms_output.put_line('collection is null');
 ELSE
 dbms_output.put_line('collection is not null');
 END IF;

-- If collection is not null then let's get its elements
 j_last := v_npk_col_stihis.last;

IF j_last is null then
 dbms_output.put_line('Collection last index is null');
 ELSE
 dbms_output.put_line('Collection last index is not null');
 END IF;

END;

Execute it and you will see that the collection is not null while its last index is null. This is what we call a not null but empty collection.

We have identified what is a not null but empty collection. What I want to emphasize here is that if we don’t clearly understood this difference, we will be certainly confronted, at execution time, to very strange errors that are not easily fixed. Let’s show this via a simple example.

The above piece of code is enriched as follows:


DECLARE

v_npk_col_stihis Gsp.Col_Npk_Stihis;
 v_npk_stihis Gsp.O_Npk_Stihis;
 j_last BINARY_INTEGER;
 v_indiceIp VARCHAR2(6);

BEGIN

-- initialize object type
 v_npk_col_stihis := Gsp.Col_Npk_Stihis();

IF v_npk_col_stihis is null then
 dbms_output.put_line('collection is null');
 ELSE
 dbms_output.put_line('collection is not null');
 END IF;

-- If collection is not null then let's get its elements
 j_last := v_npk_col_stihis.last;

IF j_last is null then
 dbms_output.put_line('Collection last index is null');
 ELSE
 dbms_output.put_line('Collection last index is not null');
 END IF;
 -- I suppose I have the index and I start using it
 v_indiceIp := v_npk_col_stihis(j_last).INDICEIP;
 dbms_output.put_line('Indice IP := '||v_indiceIp);

EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Others');

raise;
 END;

Execute it and you will get ORA-06502: PL/SQL: numeric or value error: NULL index table key value.
In order to avoid such a kind of error I often advise to precede any use of collection elements by the following IF statement:


IF v_npk_col_stihis is not null then
 IF v_npk_col_stihis.exists(1) THEN
 -- If collection is not null then let's get its elements
 j_last := v_npk_col_stihis.last;
 ..../... -- all your use of collection elements should be put here
 END IF;
 END IF;

Hence you will be protected against errors like those mentioned above. Of course collection.exists(1) has a sense only if the option collection.delete is not used. When this feature is used, gaps may exist in the collection and index (1) may also do not exist within a collection full of other binary indexes

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: