I am a new user of Oracle products and I'm having problems with two commands: ACCEPT and PROMPT. I have changed the order of the line sometimes putting in CHAR sometimes removing it, sometimes putting in PROMPT, sometimes removing it. I would receive errors no matter what I did. I even tried putting the ACCEPT and PROMPT before and after the BEGIN statement.
Here's what I want to do. At the beginning of the script I read beginning and ending dates from the keyboard, and down in the main part of the program I read from a table using a SELECT and testing to see if a date is between the dates that were entered from the keyboard. I am currently getting a ORA-00922 error when I try to execute/compile this script under SQL*Plus.
PROMPT ACCEPT in_beg_date PROMPT 'Enter beginning extract date mm/dd/yy ' PROMPT ACCEPT in_end_date PROMPT 'Enter ending extract date mm/dd/yy ' DECLARE beg_date VARCHAR2(8) := &in_beg_date; end_date VARCHAR2(8) := &in_end_date; beg_dte DATE; end_dte DATE; ... BEGIN beg_dte := TO_DATE (beg_date, 'MM/DD/YY'); end_dte := TO_DATE (end_date, 'MM/DD/YY'); ... IF (outrec_abs_date >= beg_dte and <= end_dte) THEN ...
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.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.