Accept User Input in SQL*Plus

Saturday 05 May, 2007 - 17:11pm | 0 comments |

You can allow users to enter parameters in SQLPlus queries by using accept and prompt. An example of the syntax is

accept startdate prompt "Enter Start Date: "

startdate is the parameter value and the where condition would then include the parameter as follows

WHERE date >= '&startdate'

The example below I saved for use in a batch file. A user could then click the batch file which would open SQLPlus and prompt the user for the query parameters including a "Save File As" prompt, as the output was generating a csv file. The batch file icon can be changed in Windows to something other than the default, something more meaningful for the user.

The appearance might be very retro, but its a simple tool of the "cheap and cheerful" variety and the user is, after all, more interested in the output file.

The Retro SQL*Plus Screen

accept filesave prompt "Save File As: "
accept code prompt "Enter Carrier Code: "
accept startdate prompt "Enter Start Date: "
accept enddate prompt "Enter End Date: "
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL C:\reports\'&filesave'.csv;
SELECT /* The purpose of this query is to ...... Always good policy to annotate queries */
carrier_id, carrier_name, calls, minutes, turnover FROM
carriers_data
WHERE
trunc(adj_start_time) >= '&startdate'
AND trunc(adj_start_time) < '&enddate'
AND carrier_id = '&code'

Posted in: Business
Tags: SQL*Plus | Oracle | CSV

| Link | back to top |

Accept User Input in SQL*Plus

Sponsors