title: How can i build a dynamic order by in oracle sql
body: <p>NOTE:<code>p_sort_field &amp; p_order_by are NVARCHAR2</code> parameters to the stored procedure</p>
  
  <pre><code>SELECT ... FROM table
  ORDER BY CASE WHEN p_sort_field = 'name' THEN table.Name END ASC
  </code></pre>
  
  <p>This works...</p>
  
  <pre><code>ORDER BY CASE WHEN p_sort_field = 'name' THEN table.Name ASC END
  </code></pre>
  
  <p>This don't work..</p>
  
  <p>So I need to do soemthing like this</p>
  
  <pre><code>SELECT ... FROM table
  ORDER BY CASE WHEN p_sort_field = 'name' THEN table.Name END
  CASE WHEN p_order_by = 'asc' THEN 
    ASC
  ELSE
    DESC
  END
  </code></pre>
  
  <p>And this don't work,..  anyone know how I can get this to work?
     I need to append <code>ASC or DESC</code> to the <code>ORDER BY</code> dynamically.</p>
  
  <p>Thanks in Advance.</p>
  
  <h1>EDIT</h1>
  
  <p>I took your advice, it certainly should work.  But with the fully modified stored procedure posted below, my client is getting the below error at: using(OracleDataReader reader = cmd.ExecuteReader() { .... }</p>
  
  <p>ORA-00604: error occurred at recursive SQL level 1,
    ORA-01003: no statement parsed</p>
  
  <p>As you can see I am attempting to do paging and sorting.  The paging works perfectly and always has, but with this post, I'm trying to integrate sorting (by multiple fields)</p>
  
  <p>Any help solving the above error is much appreciated!</p>
  
  <pre><code>PROCEDURE sp_se_paged_list 
  (
          p_sort_field           IN VARCHAR2,
          p_order_by             IN VARCHAR2,
          p_page_index           IN NUMBER,
          p_page_size            IN NUMBER,
          p_total_count          OUT NUMBER,
          list_cursor            OUT SYS_REFCURSOR
  )
     IS
     v_select_stmt VARCHAR2(4000);
     BEGIN
         v_select_stmt := 'SELECT * FROM (';     
         v_select_stmt := v_select_stmt || 'SELECT p.*, RowNum rnum FROM(';
         v_select_stmt := v_select_stmt || 'SELECT * FROM se s ORDER BY s.';
         v_select_stmt:= v_select_stmt || p_sort_field;
         v_select_stmt:= v_select_stmt ||' '|| p_order_by;         
         v_select_stmt:= v_select_stmt || ') p';
         v_select_stmt:= v_select_stmt || 'WHERE RowNum &lt;= p_page_index + p_page_size)';
         v_select_stmt:= v_select_stmt || 'WHERE rnum &gt;= p_page_index + 1';
      OPEN list_cursor FOR v_select_stmt;
      SELECT COUNT(*) into p_total_count from se;
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);                        
  END sp_se_paged_list;
  </code></pre>