I have a search page where users can select from multiple variables. When the page is submitted to a package in the database a cursor is constructed and opened.
a snippet to show what I mean
PROCEDURE COMPLAINTQUERY(status_in IN NUMBER, userid IN NUMBER, many other parameters cursor_out OUT SYS_REFCURSOR) IS v_query CLOB; BEGIN --much processing to construct the query OPEN cursor_out FOR v_query USING casestatus_in, userid_in, --and so on.... ; END COMPLAINTQUERY;
How could I save the user input in the query as text? The problem is the dynamic substitution. If the user entered 1 for casestatus_in and 390 for userid_in I would like to save the query with the substituted variable for reuse later :
Select * from cases where status = 1 and userId = 390;
Edit: @Justin Cave Good point. The query starts out using bind variables and to store it in a table for reuse will product a static query. I guess I want the best of both worlds. I have a few users for this application who do a lot of querying so it would be a nice feature to save their input. Maybe I should save the the bind variables.
Edit @Justin Cave If you were to suggest that I hold the user parameters in a collection I would accept that as an answer