|
Actually, it looks like you have a good understanding of the PROMPT and ACCEPT commands. PROMPT tells SQL*Plus to write a line of text the screen. You use two PROMPT commands here; each of them writes a blank line to the screen, which can be useful for vertical spacing. The ACCEPT command waits for the user to enter a value for a substitution variable. The optional PROMPT clause in the ACCEPT command causes a message to be displayed on the same line where the user will type the value. And you're right in putting the PROMPT and ACCEPT commands outside of the PL/SQL block: they're SQL*Plus commands, not PL/SQL.
You may notice that when you run the script, every line that contains
a substitution variable causes two lines of output on your screen,
such as:
old 3: beg_date VARCHAR2(8) := &in_beg_date;
new 3: beg_date VARCHAR2(8) := 09/01/03;
This shows exactly what SQL*Plus does when it sees a line of code that
contains a substitution variable: it replaces the variable name (starting
with the ampersand) with the value of that variable. It's exactly the same
as if you did a search and replace in text editor like Notepad, and changed
every instance of one string (&in_beg_date in this case) into another
string (09/09/03). The "old" line is exactly what you have in your code.
The "new" line is exactly what will be executed. What would happen if
you executed the statement "beg_date VARCHAR2(8) := 09/01/03;"? SQL*Plus
would create a string variable and initialize it to the value on the right
side of the ":=" sign. In this case, that's a numeric expression, so SQL*Plus
would evaluate the expression "(9/1) / 3", and convert the answer to a string,
'3'. What you really meant to say is:
beg_date VARCHAR2(8) := '&in_beg_date';
end_date VARCHAR2(8) := '&in_end_date';
The single quotes are important.
Click for part 2 of substitution variables.
|
