Why is order by only working for date column oracle

Why is my query order by only working for the date column i.e. col4. I am not sure why it will not work as this approach works fine with sql server and mysql.

I am using oracle 11 g and it is a stored procedure.

I would appreciate any help. Thanks

procedure GET_DATA( p_sort_col IN VARCHAR2, p_sort_order IN VARCHAR2,p_page_index IN NUMBER DEFAULT null, p_page_size IN NUMBER DEFAULT null, p_cursor  out l_cursor)   AS    begin    OPEN p_cursor FOR      select * from (  select rownum rn, col1, col2, col3, col4 from data_table where  rownum < ((p_page_index * p_page_size) + 1 )  ORDER BY   CASE          WHEN p_sort_col = 'Col1' and p_sort_order = 'asc' THEN           Col1          WHEN p_sort_col = 'Col2' and p_sort_order = 'asc' THEN           Col2           WHEN p_sort_col = 'Col3' and p_sort_order = 'asc' THEN    Col3              WHEN p_sort_col = 'Col4' and p_sort_order = 'asc' THEN              to_char(Col4,'DD/MM/YYYY HH24:MI:SS')             END ASC,  CASE           WHEN p_sort_col = 'Col1' and p_sort_order = 'desc' THEN           Col1          WHEN p_sort_col = 'Col2' and p_sort_order = 'desc' THEN           Col2                      WHEN p_sort_col = 'Col3' and p_sort_order = 'desc' THEN           Col3               WHEN p_sort_col = 'Col4' and p_sort_order = 'desc' THEN              to_char(Col4,'DD/MM/YYYY HH24:MI:SS')                  END DESC                )WHERE rn >= (((p_page_index-1) * p_page_size) + 1);    END GET_DATA;  

This example has been changed just for demo

https://stackoverflow.com/questions/65838719/why-is-order-by-only-working-for-date-column-oracle January 22, 2021 at 10:56AM