Finding MAX of PL/SQL table element

[ad]

Hi,

Is there a way to get Maximum/Minimum value in a PL/SQL table ? For instance to get the number of records in a table called "mytable" I can use mytable.COUNT

There is a round about way to sort the table in ASCending order and I can get the last record. But any ideas other than this...would be greatly appreciated ...

Thanks,
AD

[jmodic]

What do you mean by "last record"? The one with highest index value? If so, you can use mytable.LAST.

If you mean sorted by some PL/SQL table's field then no, there is no such magical way. You have to read all the records and find the highest value in that field programaticaly.

[Heath]

Couldn't you query for the MAX value of the PL/SQL table by casting it into a table using the TABLE and CAST operators? Something like


    select MAX(<X.field>)
    from TABLE(CAST(<PL/SQL table> as <PL/SQL table type>)) AS X
    group by <whatever>;


Heath

[jmodic]

Correct me if I'm wrong, but you can only use CAST() on colection types that are stored in the database (ie nested tables and varrays), not on index-by tables thet exists only inside PL/SQL block.

[Heath]

I've only successfully used it on tables of object types (a collection, not an index=by table). The type must be defined previously in the database (i.e. create type <whatever> as table of <object type>). Any instances of these types declared and used in the PL/SQL block can be CAST into tables.

I have been trying to use object types as much as possible because it seems to me that Oracle is nudging us in that direction. That is, the newer cooler stuff that they add (like CAST and TABLE operators) only work on the collection data types and then only work well if the collection is of an object type.

I think we're in agreement. :)

Heath

[jmodic]

Originally posted by Heath
I think we're in agreement.

Heath

Yep