CODEWITHSUNDEEP
sqloracle-sqldeveloper
Braide
Braide

Reputation: 167

Provide a message prompt for user input in SQL Developer 3.1.07

I am trying to get the user to input the date into a substitution variable but want the prompt message to prompt the user to input the date in the correct format.

SELECT claimdate, assessmentid
FROM claimassessment
WHERE claimdate > to_date('&Date_as_yyyy_mm_dd', 'yyyy-mm-dd');

I keep reading that I should be using the ACCEPT and PROMPT keywords but I am using SQL Developer 3.1.07 and these keywords aren't recognised. How would I get the pop up to display a prompt like 'Enter the effective date in the format 'yyyy-mm-dd'. The default prompt on the pop up just says 'Enter Substitution Variable'

Upvotes: 1

Views: 15295

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

The accept and prompt keywords are recognised in SQL Developer 3.1.07.42.

Here's a demo using this form:

accept Date_as_yyyy_mm_dd date format "YYYY-MM-DD" prompt "Enter the effective date in the format 'yyyy-mm-dd'"

which creates the prompt window with the text you want (the double-quotes around the prompt value aren't necessary in 3.1 but I think they make it clearer; the single quotes aren't preserved either way though in 3.1, but they are in 18.3), and also checks the format of the supplied values; and then uses the accepted value:

enter image description here

When run as a script (F5) the pop-up window appears with the specified text:

enter image description here

If I enter a value that doesn't match the format it re-prompts:

enter image description here

and one it has an acceptable value it uses that substitution variable value in subsequent statements:

enter image description here

And as long as you've run the accept in script mode, you can run your main query as a statement (control-enter) to get results in a grid in a 'Query results' window.

As Jeff mentioned, 3.1 is very old - it seems to have been released in February 2012 - so you might want to consider getting a newer version.

enter image description here

Upvotes: 3

thatjeffsmith
thatjeffsmith

Reputation: 22437

Option 1: Build it into your SUBSTITUTION Variable name

SELECT *
  FROM hr.employees
 WHERE hire_date > TO_DATE('&date_mm_dd_yyyy', 'MM-DD-YYYY');

enter image description here

The date format is kind of in the prompt - the user will at least know you want the 2 digit month then the 2 digit day and then the 4 digit year.

Option 2: Use a report with bind variables.

enter image description here

For the report, instead of using a substitution variable, we're using a BIND. Your query basically stays the same, it's just :X vs &X. The benefit is that your query will be cached in the optimizer once vs multiple times, once for each query...although more recent copies of the database are smart enough to figure this out on it's own.

You can define in the report how the prompt is labeled.

enter image description here

And the query behind the report

SELECT *
  FROM hr.employees
 WHERE hire_date > to_date(:user_date, 'MM-DD-YYYY')

Upvotes: 4

Related Questions