Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure: Prompt
Stored Procedure: Prompt [message #202205] Wed, 08 November 2006 13:58 Go to next message
Messages: 12
Registered: May 2006
Junior Member
I am kinda new in Stored Procedures and just got a new requirement.

I am using Crystal Reports in front end and select data through Stored Procedure. My new requirement is to have a prompt 2 for date where the default would be beginning of last month and end of last month, but user can select any date if they want to run on ad hoc basis. Could someone please help me with the syntax of the prompt.

Prompt 1: Type Date
Default Value: Beginning of last month.

Prompt 2: Type Date
Default Value: End of last month.

I currently have a function to get the beginning and end of last month.

Thank you in advance.
Re: Stored Procedure: Prompt [message #202217 is a reply to message #202205] Wed, 08 November 2006 15:10 Go to previous messageGo to next message
Messages: 20847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know anything about Crystal Reports, but perhaps you'll find it useful: query (in SQL) could look like this:
SELECT column_list
FROM your_table
WHERE date_column BETWEEN NVL(&start_date, TRUNC(ADD_MONTHS(SYSDATE, -1), 'mm')
                      AND NVL(&end_date, TRUNC(SYSDATE, 'mm') - 1);
Re: Stored Procedure: Prompt [message #202246 is a reply to message #202217] Wed, 08 November 2006 20:01 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
PL/SQL is run on the Oracle Server, whereas prompts require interaction with the client. For this reason, any prompt must be generated by the client software (Crystal, in your case).

In Littlefoots example, it is SQL*Plus that parses the SQL, identifies the & variables, prompts, replaces the values, then submits the replaced SQL to Oracle.

For a Stored Procedure, you must write your own software using Crystal to do the prompt, and then pass the entered values to the SP as parameters.

Ross Leishman
Previous Topic: Dynamically naming and declaring variables
Next Topic: UNION clause
Goto Forum:

Current Time: Thu Oct 27 21:39:55 CDT 2016

Total time taken to generate the page: 0.09803 seconds