Updating a table which stores queries which requires user inputs

Question

I have a table in the database which stores queries for generating reports. I need to update a query in the table, and when that query is executed during the report generation, it will prompt the user for inputs according to which the table will be generated.

Below is how the query is structured.

UPDATE report_table
SET sql_query = 'SELECT *
FROM book
where value1 = upper(''&testvalue1'') and value2 = upper(''tastvalue2'')'
where report_id = 1;

When executing the above query in the SQL developer, it prompts me for value1 and value2 whereas it should be prompted at the time of generating the report in the application.

Any help or idea on how to achieve this is highly appreciated.

Answer 1

it should be prompted at the time of generating the report in the application.

SQL does not work like that; it does not dynamically accept user input during the evaluation of a query.

Certain client applications (SQL*Plus, SQL Developer, Toad, etc.) may support dynamically modifying queries while they are being processed by the client application before it is sent to the database but once the query has been sent to the database it is processed as-is and is not modified.

In your case, & signifies a substitution variable that is evaluated by the client application when it pre-processes the statement before sending it to the database and asks for user input and then effectively performs a find-and-replace on the statement replacing the substitution variable with the user's input. It then sends the modified statement to the database and the database is unaware that the statement has been changed because all that happened on the client.

Within the database, & has no syntactic meaning so SELECT &user_input FROM DUAL would result in a syntax error when evaluated by the database's SQL engine. It is only client applications pre-processing the statement (before the query is sent to the database) where & may have special meaning.

If you want to stop the client application from processing substitution variables then either:

• Turn off processing substitution variables in the client application's settings; or
• Use the SQL*Plus command SET DEFINE OFF

However, that will just allow the client application to treat the & as part of the query and not as a substitution variable; it will not enable the query to later be modified by the database taking input for a user - because databases do not do that.

---

If you want to use a parameterised query then use bind variables:

UPDATE report_table
SET sql_query = 'SELECT *
FROM book
where value1 = upper(:testvalue1) and value2 = upper(:testvalue2)'
where report_id = 1;

Then later on you could use:

DECLARE
  v_sql VARCHAR2(200);
BEGIN
  SELECT sql
  INTO   v_sql
  FROM   report_table
  WHERE  report_id = 1;

  EXECUTE IMMEDIATE v_sql USING 'value1', 'value2';
END;
/

The query persisted in the table would be executed but it would not dynamically ask for user-input (because the database cannot do that); instead you can use the USING clause to bind variables into the bind parameters (but you need to know how many bind variables are in the query).

EXECUTE IMMEDIATE would also not output the query results; it would just execute the query and appear to do nothing. You would probably want to use a cursor but this is steadily getting more-and-more complicated and you probably need to rethink the design of your application.

(This also doesn't consider the potential for SQL injection attacks.)