Accept User Input in SQL*Plus

http://www.zorbathegeek.com/163/accept-user-input-in-sqlplus.html

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.

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’

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.

    For example,

wanghao@sf3:~/cs6203[513]$ more table.sql
DROP TABLE employee
/

commit
/

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)
/
wanghao@sf3:~/cs6203[514]$

  • 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;


Iklan