21st Century PL/SQL

March 31, 2005

Allow me to specify a range for the COUNT method.

COUNT tells me how many rows are in my collection. What I sometimes need to know is: how many (are there any) rows are defined between rows N and M?

It would also be nice to be able to specify whether or not to include the endpoints in the count.

Posted by sf at 08:32 AM | Add your thoughts (1)

April 04, 2005

Extend support for MULTISET operators to associative arrays.

Oracle10g added the capability to perform high-level set operations on nested tables, including MULTISET UNION, MULTISET EXCEPT and even = and != operations.

Very useful stuff, and it would be great to offer the same or similar functionality for associative arrays, which are more commonly used by PL/SQL developers.

Posted by sf at 11:01 PM | Add your thoughts (1)

August 19, 2005

The foreach Collection iterator Loop

Hello,

if you want to iterate a collection you have to code following to do it the right way :

aCollectionItem := aCollection.FIRST;
WHILE aCollectionItem IS NOT NULL LOOP
  -- Your Code
  aCollectionItem := aCollection.NEXT(aCollectionItem);
END LOOP;

You may not forget the first line : aCollectionItem := aCollection.FIRST; and not forget the next step line : aCollectionItem := aCollection.NEXT(aCollectionItem);

Why do not let PL/SQL control the correct looping? and save two lines of code?

FOREACH aCollectionItem IN aCollection LOOP
  -- Your Code
END LOOP;

The acceptance of PL/SQL in the .NET/JAVA Community would grow if the client/gui oriented developers would detect some nice language features they already know from C#/JAVA.

PL/SQL could learn a lot of good stuff from them.

Carl

Posted by Carl Reitschuster at 09:50 AM | Add your thoughts (4)

September 29, 2005

Add ORA_ROWSCN to DML trigger records (OLD and NEW)

These records take on the structure of the table the trigger is applied to including a ROWID column. It would be handy to include the ORA_ROWSCN (new in 10G) column as well. This would allow trigger logic to only work with records not modified since the trigger started. It could also be used to avoid trigger restarts using syntax something like this:

CREATE TRIGGER b4_update
BEFORE UPDATE
ON ABC
FOR EACH ROW
WHEN ( OLD.ORA_ROWSCN = NEW.ORA_ROWSCN )
BEGIN

...or...

IF :NEW.ORA_ROWSCN <> :OLD_ORA_ROWSCN

Posted by Darryl Hurley at 04:36 PM | Add your thoughts (1)