Script to take in data
Now that we have used a script to create a report, let's go back and look at script to take
in data to insert into a table. The table that I am going to work with is the donation table
shown below. First, I issue the edit command followed by a name for the script, I am calling
it define form. Since the script is new it comes back with a can't find warning asking if I
want to create new. I respond yes.
SQL CODE:
SQL> SELECT * FROM DONATION;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
6 rows selected.
SQL> DESC DONATION;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(5)
DRIVENO VARCHAR2(3)
CONTDATE DATE
CONTAMT NUMBER(6,2)
SQL> EDIT DEFINEFORM
The script that I create is shown below. The accept will take the data in and the INSERT
will put the information into the fields on the form. Notice that I am taking the data
into fields I define as in_followed by the column name. In fact I could call them anything.
Remember the & is used with temporary variables. In the INSERT, the varchar2 and the date
fields are inserted in quotes and the number field is left without the quotes. This means
that the user does not have to enter quotes.
SQL CODE:
ACCEPT in_idno PROMPT 'Please enter the idno: ';
ACCEPT in_driveno PROMPT 'Please enter the driveno: ';
ACCEPT in_contdate PROMPT 'Please enter the contribution date: ';
ACCEPT in_contamt PROMPT 'Please enter the contribution amount: ';
INSERT INTO donation
VALUES('&in_idno', '&in_driveno', '&in_contdate', &in_contamt);
After the script has been written it, I save and go back to the SQL> prompt where I issue
the command @ defineform, this executes the script. The accept prompts are shown, the data
is entered. Again remember because there are quotes in the INSERT, the user does not have
to enter quotes. To avoid the old/new you can set verify off, I decided to leave it on to
show what was happening. As you can see, the insert works and the row is created. Another
execution creates another row in the table.
SQL CODE:
SQL> @ defineform
Please enter the idno: 11111
Please enter the driveno: 200
Please enter the contribution date: 12-JUN-99
Please enter the contribution amount: 35
old 2: VALUES('&in_idno', '&in_driveno', '&in_contdate', &in_contamt)
new 2: VALUES('11111', '200', '12-JUN-99', 35)
1 row created.
Input truncated to 1 characters
SQL> select * from donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
7 rows selected.
SQL> @ defineform
Please enter the idno: 23456
Please enter the driveno: 300
Please enter the contribution date: 14-JUN-99
Please enter the contribution amount: 10
old 2: VALUES('&in_idno', '&in_driveno', '&in_contdate', &in_contamt)
new 2: VALUES('23456', '300', '14-JUN-99', 10)
1 row created.
Input truncated to 1 characters
SQL> SELECT * FROM donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
8 rows selected