Adding Sort Keys and Filter to Oracle Stored Procedure

Question

I have a stored procedure in Oracle that returns a select statement cursor reference. I want to be able to pass column names and sort direction (example: 'CompanyName DESC') and be able to sort the results, or pass a filter such as 'CompanyID > 400' and be able to apply that to the select statement. what is the best way to accomplish this? This table is in an old database and has 90 columns, and I don't want to do logic for every possible combination.

Answer 1

See here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279 and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6711305251199

I especially like this method: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#4988761718663

Answer 2

I think you want a cursor with an OPEN-FOR and USING.

CREATE OR REPLACE FUNCTION sort_table (
    p_sort VARCHAR2
    )
IS
    TYPE cursor_type IS REF_CURSOR;
    cur_out cursor_type;
    lv_cursor_txt VARCHAR2(300);
BEGIN
    lv_cursor_txt = 'SELECT * FROM table :sort';
    OPEN cur_out FOR lv_cursor_txt USING p_sort;
    -- Opening the cursor isn't probably what you want, but I'm not sure how to associate the variables except on open
    RETURN cur_out;
END;

This isn't a very good example, but I hope it helps somewhat.

Oracle talks about this in the Dynamic SQL page. The DBMS_SQL package may help as well.

Answer 3

As you probably figured out, R. Bemrose's answer is the simpler to implement, using a dynamic SQL. For that reason, it's probably used the most often. If you do this, be sure to do it the way he(/she?) did, using a bind variable (e.g. USING p_sort) rather than just concatenating the string onto the lv_cursor_txt. This method gives better performance and security than concatenating.

The second method uses application contexts, which I haven't seen used much, but I suspect would provide better query performance if you're calling the query a lot.

Good luck.