You can allow users to enter parameters in SQLPlus queries by using accept and prompt. An example of the syntax is
startdate is the parameter value and the where condition would then include the parameter as follows
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.
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
SELECT /* The purpose of this query is to …… Always good policy to annotate queries */
carrier_id, carrier_name, calls, minutes, turnover FROM
trunc(adj_start_time) >= ‘&startdate’
AND trunc(adj_start_time) < ‘&enddate’
AND carrier_id = ‘&code’
Run SQL statements in a batch
To run SQL commands in a batch, you can put all your SQL commands into a text file and execute these commands in this file in SQL*PLUS.
- Use your favorite editor to type in your SQL queries into a text file.
wanghao@sf3:~/cs6203$ more table.sql
DROP TABLE employee
CREATE TABLE employee (
empno INTEGER NOT NULL,
name VARCHAR2(50) NOT NULL,
sal REAL NOT NULL,
primary key (empno));
INSERT INTO employee VALUES (1, ‘Jack’, 6000);
INSERT INTO employee VALUES (2, ‘Tom’, 6000);
INSERT INTO employee VALUES (3, ‘John’, 6000);
INSERT INTO employee VALUES (4, ‘Jane’, 6000);
UPDATE employee SET sal=500 WHERE name=’Jack’
CREATE INDEX test_index on employee(sal)
- Connect into SQL*Plus, and run the batch of commands. For example, assume that you name the SQL file as table.sql.
SQL> START table.sql;