Use Variabel for 'ORDER BY ' Declartion in a FOR IN LOOP Oralce-CodePudding

In ORACLE please.

Is it possible to have a variable in the 'ORDER BY' Statment?

So iam currenty creating a procedure in which I need to have a dynamic Order by. Is there any way to do this ?

CREATE OR REPLACE PROCEDURE TEST1

vorder varchar(250);
...

vOrder := 'number';
FOR o IN (SELECT * FROM TABLENAME order by vOrder)
LOOP
-- Some stuff ---

END LOPP;
....

CodePudding user response：

The only way to really have a dynamic order by clause instead of just having a list of predefined options as suggested by Alex, would be to use a dynamic sql cursor.


TYPE curType IS REF CURSOR;
vCur curType;
vRec tablename%ROWTYPE;
vOrd VARCHAR2(250) := 'column';

BEGIN
   OPEN vCur FOR 'SELECT * FROM tablename ORDER BY '||vOrd';
   LOOP
      FETCH vCur INTO vRec;
      EXIT WHEN cur%NOTFOUND;
   END LOOP;
   CLOSE vCur;
END;
/