Pl/SQL: How to sort a table of records?

Question

I'm new to pl/sql ! I'm trying to sort a table of records, using a simple bubble-sort algorithm. What is the problem?

Where could I find more information about using table of records ?

DECLARE
    text VARCHAR2(50);
TYPE TIP_VECTOR
IS
    TABLE OF INT INDEX BY BINARY_INTEGER;
TYPE contorRecord
IS
    record
    (
     codASCII VARCHAR2(3),
     contor   SMALLINT);
TYpe tip_vector2
IS
    TABLE OF contorRecord;
    VECTOR TIP_VECTOR;
    VECTOR2 TIP_VECTOR2 := TIP_VECTOR2();
  aux tip_vector2 := tip_vector2();
    v_char VARCHAR2(3);
    FLAG   BOOLEAN := TRUE;
    t smallint;
    n      SMALLINT := 1;
    ind    SMALLINT := 0;

begin
        AUX.EXTEND(1);
            WHILE(FLAG)
                 LOOP
                 FLAG  := FALSE;
    -- here is the problem; what i'm doing wrong?
                 FOR I IN 1..(vector2.count-1) LOOP 
    -- here is the problem; what i'm doing wrong?
                 IF VECTOR2(I).CONTOR  < VECTOR2(I+1).contor THEN
                 AUX         := VECTOR(I+1);
                 VECTOR(i+1) := VECTOR(I);
                 VECTOR(I)   := AUX;
                 FLAG        := TRUE;
                 END IF;
                 END LOOP;
                 END LOOP;
end;

Error:

Error report:

PLS-00382: expression is of wrong type

PL/SQL: Statement ignored

PLS-00382: expression is of wrong type

PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

You have your types and variables mixed up. Try:

DECLARE
    TYPE contorRecord IS record ( codASCII VARCHAR2(3), contor   SMALLINT);
    TYpe tip_vector2 IS TABLE OF contorRecord;
    VECTOR2 TIP_VECTOR2 := TIP_VECTOR2();
    aux contorRecord;
    FLAG   BOOLEAN := TRUE;
begin
    WHILE(FLAG)
    LOOP
        FLAG  := FALSE;
        FOR I IN 1..(vector2.count-1) LOOP
            IF VECTOR2(I).CONTOR  < VECTOR2(I+1).contor THEN
                AUX         := VECTOR2(I+1);
                VECTOR2(i+1) := VECTOR2(I);
                VECTOR2(I)   := AUX;
                FLAG        := TRUE;
            END IF;
        END LOOP;
    END LOOP;
end;
/

(Tidied up by removing all unused types and variables)

Answer 2

Also it's perhaps not the way you really need. Generally you can benefit from using SQL in PL/SQL code - in SQL you can just ORDER BY your results. What's the problem you're trying to solve?

Information on PL/SQL collections is available here: PL/SQL Language Reference

Answer 3

Here's a nice technique if you do not want to roll-your-own: http://technology.amis.nl/blog/1217/sorting-plsql-collections-the-quite-simple-way-part-two-have-the-sql-engine-do-the-heavy-lifting