Problem solve Get help with specific problems with your technologies, process and projects.

Substitution variables, part 1

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.

ACCEPT in_beg_date  PROMPT 'Enter beginning extract date mm/dd/yy '
ACCEPT in_end_date  PROMPT 'Enter ending extract date mm/dd/yy '

   beg_date VARCHAR2(8) := &in_beg_date;
   end_date VARCHAR2(8) := &in_end_date;
   beg_dte  DATE;
   end_dte  DATE;
   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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.