You Asked

Hi,

I've the following problem.

I call the function with the order-by parameter, but it will not be used.
When I use the order-by hardcoded, the values are sorted.

Do you have any idee what is wrong in the function or the calling?
Thanks a lot,
Marlies

function call:

select VL_ORD_XML2('ORDID DESC') FROM DUAL;

select VL_ORD_XML2(null) FROM DUAL;

function:

create or replace FUNCTION VL_ORD_XML2 (
      ORDER_BY_IN VARCHAR2
)
      
RETURN clob IS
ret_val clob;

BEGIN
  DECLARE 
  ORDER_STATEMENT VARCHAR2(1000);
  V_SQL VARCHAR2(3000);
  
  BEGIN
  IF ORDER_BY_IN IS NULL OR ORDER_BY_IN = '' THEN
    ORDER_STATEMENT := ' ORDID DESC';
  ELSE  
    ORDER_STATEMENT := ORDER_BY_IN;
  END IF;
  
  SELECT 
  'BEGIN SELECT XMLSERIALIZE(CONTENT XMLROOT(
          XMLELEMENT("TEST", XMLAGG(
            XMLELEMENT("TEST_ELEMENT", 
              XMLFOREST(  o.ORDID, 
                          o.PATIENT_FIRSTNAME, 
                          o.PATIENT_SURNAME)))), VERSION ''1.0'', STANDALONE YES)) 
                          INTO :ret_val FROM (SELECT * FROM OEORD WHERE SRCHPATTERN LIKE ''%MARKUS%'' AND SRCHPATTERN LIKE ''%FISCHER%'' ORDER BY :ORDER_STATEMENT) o;END;' INTO V_SQL FROM DUAL;
   
    EXECUTE IMMEDIATE V_SQL using OUT ret_val, IN ORDER_STATEMENT;
                  
    return ret_val;
END;
END;

and Chris said...

When you order by a bind variable, you're sorting by its value. Not the name of the column you pass!

For example, if you have:

open cur for 'select * from t order by :val' using 'y'

Then the resulting query is:

select * from t order by 'y'

Note the quotes around 'y'! So the rows appear in the "wrong" order:

create table t as 
  select level x, 11-level y from dual
  connect by level <= 10;
  
var cur refcursor;

begin
  open :cur for
    'select * from t order by :val' using 'y';
end;
/

print :cur

         X          Y
---------- ----------
         1         10
         2          9
         3          8
         4          7
         5          6
         6          5
         7          4
         8          3
         9          2
        10          1

For an example of how you could pass a column to do a dynamic order by, see:

https://asktom.oracle.com/pls/apex/asktom.search?tag=order-by-at-runtime#9537893900346381974

Is this answer out of date? If it is, please let us know via a Comment