Home » SQL & PL/SQL » SQL & PL/SQL » use of simple_integer with collections (Oracle 11.2.0.2.0 Solaris 10)
use of simple_integer with collections [message #577385] Thu, 14 February 2013 12:07 Go to next message
uman2631
Messages: 4
Registered: November 2011
Junior Member
I'm exploring converting our use of pls_integer to simple_integer. My concern is that simple_integer cannot be null. It seems safe to use for count(*), since count(*) cannot return null. But I am not sure about its use in collection methods like
i := collection.count 
or for loops like
for i in 1..collection.count


Re: use of simple_integer with collections [message #578667 is a reply to message #577385] Sun, 03 March 2013 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not matter, Oracle will convert into the target type.
What is your actual concern?

Regards
Michel
Re: use of simple_integer with collections [message #578675 is a reply to message #577385] Sun, 03 March 2013 10:11 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
uman2631 wrote on Thu, 14 February 2013 19:07
I'm exploring converting our use of pls_integer to simple_integer. My concern is that simple_integer cannot be null

The problem is that you don't always explore a collection by using a FOR LOOP. There are collection methods such as NEXT and PRIOR that could return NULL based on the current position in the collection. You can have sparse collections that you may want to explore within a LOOP . . . EXIT<condition> . . . END LOOP. For example something like this (in the below pseudo code we assume that l_collection is an associated array and l_indx is of type SIMPLE_INTEGER)

IF l_collection.FIRST IS NOT NULL
THEN
    l_indx := l_collection.FIRST;
        
    LOOP

     . . .

        EXIT WHEN l_collection.NEXT(l_indx) IS NULL;
        l_indx := l_tab_countries.NEXT(l_indx);
    END LOOP;
        
END IF;


If the collection is empty and the first if statement "IF l_collection.FIRST IS NOT NULL" is not present before the loop you will get ORA-06502: PL/SQL: numeric or value error.

The same thing happens if you remove the EXIT WHEN l_collection.NEXT(l_indx) IS NULL from inside the loop.

So for me it's ok to use SIMPLE_INTEGER as long as you check everywhere BEFORE you assign a value to it.

By the way why do you want to switch from PLS_INTEGER to SIMPLE_INTEGER?


Regards,
Dariyoosh

[Updated on: Sun, 03 March 2013 10:36]

Report message to a moderator

Re: use of simple_integer with collections [message #578676 is a reply to message #578675] Sun, 03 March 2013 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better:
IF l_collection.FIRST IS NOT NULL
THEN
    l_indx := l_collection.FIRST;
        
    LOOP
        EXIT WHEN l_indx IS NULL;

     . . .

        l_indx := l_tab_countries.NEXT(l_indx);
    END LOOP;
        
END IF;

In case there is no elements in the array and in this case it is better to NOT use a SIMPLE_INTEGER.

Regards
Michel
Re: use of simple_integer with collections [message #578677 is a reply to message #578676] Sun, 03 March 2013 11:23 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
Your solution as you said is better when the collection is empty but again your code will crash by giving you "ORA-06502: PL/SQL: numeric or value error" once you arrive at the last element. Because here is what happens for a collection (let's say an associative array) of N elements (assuming that l_indx again of type SIMPLE_INTEGER is now equal to N and all precedent elements in the array have already been explored)

LOOP
    EXIT WHEN l_indx is NULL;  -- It's ok the program will not exit 
                               -- because l_indx = N (from the previous NEXT call
                               -- from the previous loop iteration (N - 1)

    . . .
    l_indx := l_collection.NEXT(l_indx) -- But now the program will crash because 
                                        -- NEXT(l_indx) = what comes after N which 
                                        -- is NULL So here you assign a NULL value 
                                        -- without any control. That's why the 
                                        -- EXIT loop must come just before the 
                                        -- NEXT call



My conclusion: NEVER use SIMPLE_INTEGER as index in collections

Well, there are experts who believe that doing so will improve significantly the performance. Here is what Steven feuerstein writes in his PL/SQL book chapter 7
Quote:

...
SIMPLE_INTEGER, introduced in Oracle Database 11g, has the same range of values
as PLS_INTEGER, but it does not allow NULL values, nor does it raise an exception
when an overflow occurs. SIMPLE_INTEGER, like SIMPLE_FLOAT and
SIMPLE_DOUBLE, is extremely speedy--especially with natively compiled code. I've
measured stunning performance improvements using SIMPLE_INTEGER compared
to other numeric datatypes
.
...


Well, if he has done the tests, I think maybe he's right. Personally, if I see that my code is slow, I think there could be much more interesting places in the code that I want to start searching (data modelling/normalization, indexes, joins, etc.) rather than the types of indexes in my collection objects (unless we are talking about hash tables), IMHO Smile


Regards,
Dariyoosh

[Updated on: Sun, 03 March 2013 11:31]

Report message to a moderator

Re: use of simple_integer with collections [message #578678 is a reply to message #578677] Sun, 03 March 2013 13:55 Go to previous message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
once you arrive at the last element


This is why I said SIMPLE_INTEGER should not be used.

Quote:
rather than the types of indexes in my collection objects


Sure. I think this is important in 0.0001% of the cases (that is maybe 1 or 2 accounts in the world).

Regards
Michel
Previous Topic: how to get which query is taking time while executing
Next Topic: NON-EXISTENT in xxxx_dependencies
Goto Forum:
  


Current Time: Thu Jul 31 00:40:41 CDT 2014

Total time taken to generate the page: 0.10648 seconds