Expert: Suchitra Joshi - 6/8/2009

Question
Hi suchitra,
           i have question regarding sorting values using Plsql.. My requirement is to get N values from user and sort it in ascending or descending order. i tried the following query

DECLARE
     Type numsort is TABLE OF
     NUMBER
     INDEX BY BINARY_INTEGER;
     v_num numsort;
     v_total number;
     v_no number;
BEGIN
   --get the number of values u want to sort
   v_total :=:TotalNum;
dbms_output.put_line(v_total);
for i in 1..v_total
  LOOP
      v_no :=:no; -- read the values
      v_num(i):=v_no;
       dbms_output.put_line(v_num(i)||chr(10));
   END LOOP;
END;    

its not complete code.. Reading itself is not working

The problem is the bind variable is not reseting with each iteration.. I'm using toad and it asking me to pass all bind variable at once.. But i want to read the values one by one into the array with each iteration.. Is it possible in PLSQL.. or is there any other way to achieve this.

hope you are clear with the requirement.. If need any extra infomartion please revert bk..

Waiting for your replay

Thanks and regards
Ranjith

Answer
Hi Ranjith,

Sorry for the delay.

Reading value one by one in PL/SQL is not possible. For that you can use Unix shell script to read values in a loop and store them in an array variable. Then call procedure with this variable as input parameter.

This way you can use your pl/sql code having the entire array variable as input. I cannot think of any other solution.

Hope this helps.

Regards

Suchitra