Use Variables to Insert Data

In this example, a user is asked for three kinds of variables: Character, Date & Number. They are then inserted into an 'INSERT INTO' statement. (In entering the date, the year is assumed to be the current one.) After insertion, a SELECT is performed to show the user the data entered as it is in the table.

Set Verify Off
Set Feedback Off
Prompt Please Enter...

--+------------------------------------------------------------------
--| Ask for data
--+------------------------------------------------------------------

Accept Who                 Prompt 'Name: '
Accept Month   Format 99   Prompt 'Month: '
Accept Day     Format 99   Prompt 'Day: '
Accept Amount              Prompt 'Amount: '

--+- 1 --------------------------------------------------------------
--| Insert the record based on the values typed (Assume current year)
--+------------------------------------------------------------------

Insert Into TestData Values
     (
        Test_Seq.NextVal
        ,
        Upper('&Who')
        ,
        &Day
        || '-'
        || Decode(&Month, 1,'JAN',  2,'FEB',  3,'MAR',  4,'APR',
                          5,'MAY',  6,'JUN',  7,'JUL',  8,'AUG',
                          9,'SEP', 10,'OCT', 11,'NOV', 12,'DEC',
                  '???')
        || '-'
        || To_Char(Sysdate,'YY')
        ,
        &Amount
     );
Commit;

--+- 2 --------------------------------------------------------------
--| Get the current value
--+------------------------------------------------------------------

Set Term Off

Column CurrVal New_Value CurrVal

Select Test_Seq.Currval CurrVal
 From Dual;

--+- 3 --------------------------------------------------------------
--| Display the record just entered.
--+------------------------------------------------------------------

Set Term On

Select *
 From Test_Data
Where Test_Id = &CurrVal
;
Undefine Who Month Day Amount
Set Feedback On
Set Verify On

Oracle documentation:

Accept,