Display a Calendar and Prompt for a Date

The code for the calendar procedure was found on OTN and was created by Pablo Rovedo, DBA at ADEA S.A., in Buenos Aires, Argentina.

My script gets the current month and year, and passes it to the procedure. I then prompt the user for the day of the month. The goal of this is to run a report based on a date. If the users picks something later than the Sysdate, they are prompted with an error message (but the script doesn't really stop.)

In the above picture, the script is run twice. The first time, a report is returned; the second, an error message.

This is the test table I created:
Create Table Test (Person Varchar2(10) ,Testdate Date ); Insert Into Test Values('Diane' ,Sysdate-2); Insert Into Test Values('Lynn' ,Sysdate-3); Insert Into Test Values('Craig' ,Sysdate-4); Insert Into Test Values('Denise',Sysdate-5); Insert Into Test Values('Glenn' ,Sysdate-6); Insert Into Test Values('Jacque',Sysdate-7);

The script:

-------------------------------------------------------------------- -- Get month and year to display this month's calendar -------------------------------------------------------------------- Set Term Off Serveroutput On Verify Off Column mm New_Value mm Column yyyy New_Value yyyy Select To_Char(Sysdate,'mm' ) mm, To_Char(Sysdate,'yyyy') yyyy From Dual; Set Term On exec calendar ('&mm','&yyyy'); -------------------------------------------------------------------- -- Explain the purpose of report and Get the day of month -------------------------------------------------------------------- Prompt Prompt This report displays records for a day. Accept DOM Number Prompt 'Please choose the day: ' -------------------------------------------------------------------- -- DOM needs to be trimmed since it was defined as 'Number' in the -- Accept statement. -- Use case to set up for possible error msg. The prompt will either -- display a message or it will display nothing -------------------------------------------------------------------- Set Term Off Column DateError New_Value DateError Column Test_DOM New_Value Test_DOM Select Ltrim(&DOM,' ') Test_DOM, Case When &DOM > To_Char(Sysdate,'dd') Then 'Error: Day must be today or before.' Else ' ' End As DateError From Dual; Set Term On Prompt &DateError -------------------------------------------------------------------- -- Select everything from our test table where the date matches the -- date we put together. The table date will have to be -- truncated to remove any time, and get a match -------------------------------------------------------------------- Select * From Test Where To_Date('&Test_Dom'||'-'||'&mm'||'-'||'&yyyy','dd-mm-yyyy') = Trunc(Testdate);