I have table in the database storing reservation dates. I want to search for all reservations made in a period of months; that is, I need to find all the reservation made between any two months or a particular month.My input will be something like:
"Jan-2005" or "Nov-2004 to Feb-2005".
If you were willing to edit a script every time you had to do this sort of query, you might write a script like this:
-- res_dt.sql -- Find reservations made in or between -- two given months. -- Dates are hard-coded in this script. To do a query -- for any two months, you have to change two lines below. -- (Look for the comments on the right-hand side.) -- Note: the first month IS included, the second one -- IS NOT. SELECT * FROM reservation WHERE made_dt >= TO_DATE ( '01-Nov-2004' -- ^^^^^^^^ <== Change above , 'DD-Mon-YYYY' ) AND made_dt < TO_DATE ( '01-Mar-2005' -- ^^^^^^^^ <== Change above , 'DD-Mon-YYYY' ) ORDER BY made_dt , id;There are three main problems with this:
- You have to edit a file (typing the months in question) and then say "
@res_dt" in SQL*Plus. It would be much easier to just say something like "
@res_dt Nov_2004 Feb-2005"
- You can't let users do this themselves. Someone is bound to make a editing mistake that will ruin the script. Also, two users trying to use it at the same time would get in each other's way (unless they had their own copies).
- Instead of typing the last month you want, you have to type the first month you don't want. That's confusing, plus you have to figure it out.
We can solve problems 1 and 2 by using SQL*Plus substitution variables to pass the variable parts of the script. When SQL*Plus parses the script, it will substitute the value of the first parameter (from the command that called the script) for all occurrences of the string
&1, and it will replace all occurrences of the string
&2 with the second parameter. SQL*Plus shows exactly what it is doing by printing each line that includes substitution variables before and after making the substitutions, like this:
old 3: WHERE made_dt >= TO_DATE ( '01-&1' new 3: WHERE made_dt >= TO_DATE ( '01-Nov-2004'The line labeled "new" is what SQL*Plus will execute.
Problem 3 can be solved by using the
ADD_MONTHS function. Making these changes, we have:
-- res_dt.sql -- Find reservations made in or between -- two given months. -- Call this script with two Mon-Year arguments, -- like this: -- @res_dt Nov-2004 Feb-2005 -- All reservations made in either month, or -- in months between them, will be shown. SELECT * FROM reservation WHERE made_dt >= TO_DATE ( '01-&1' , 'DD-Mon-YYYY' ) AND made_dt < ADD_MONTHS ( TO_DATE ( '01-&2' , 'DD-Mon-YYYY' ) , 1 ) ORDER BY made_dt , id;
This script takes two arguments. To run this script for a single month, you still have to pass two arguments, like "
@res_dt Jan-2005 Jan-2005". This will produce the correct results, but it's not very convenient. Using optional arguments in a SQL*Plus script is a separate question.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.