Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
v_query           CLOB;
--much processing to construct the query
            OPEN cursor_out FOR v_query
               USING casestatus_in,
                     --and so on....

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

share|improve this question
Where do you want to save the query? You could write the data to a new table, of course. Oracle is already storing the query in the shared pool if you run the same query again (with or without the same bind variable values). If the query you're actually using uses bind variables (a very good thing in general), a similar query that uses literals is not the same query. Running queries without bind variables will flood your shared pool with non-reusable SQL and may cause serious performance problems. – Justin Cave Nov 7 '13 at 22:17
If your goal is to allow individual users to save their settings so that they don't have to enter values for the "many other parameters" each time, it would seem reasonable to create a new table to store those parameters (possibly along with a name for the parameter set) and then let your users choose a parameter set in the application. You probably wouldn't implement that functionality inside the procedure you posted, it would probably be done in a different procedure that your application calls. – Justin Cave Nov 7 '13 at 22:54
This certainly seems to me like something that should be done in the front-end. – Max Vernon Nov 7 '13 at 23:02
@MaxVernon given that the reporting tool I use is free but crippled it's actually easier to do this in the database – kevinsky Nov 7 '13 at 23:08
Does the non-free version do what you want? How much does it cost? You could make an ROI case for buying it if it would make life easier for everyone concerned... – Max Vernon Nov 7 '13 at 23:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.