Skip to Main Content
  • Questions
  • How to read a value from the user at run time?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rakhshanda.

Asked: October 15, 2018 - 3:21 pm UTC

Last updated: October 15, 2018 - 4:37 pm UTC

Version: Oracle Live

Viewed 10K+ times! This question is

You Asked

Hey!

I am a beginner. I was watching YouTube tutorials on Oracle PL/SQL and I came across this feature that uses the placeholder to assign a value to a variable by reading it from the user at the run time.
I tried my best to use the given format (link of the Oracle Live session has been provided with my query). However, I always encountered the error which fails to recognize the '&' symbol and suggests me various other symbols that should be used instead.
I found a similar question on this portal 'Dynamic variable initialization', it said. The user there faced the same issue. The solution that was given said to use:
SET DEFINE ON
I have modified the code accordingly yet the error doesn't get resolved. Please, help me.

My code is given in bold and the error displayed is shown in italics:

SET DEFINE ON

Unsupported Command

DECLARE
age VARCHAR2(2);
BEGIN
age:= &age;
dbms_output.put_line('Your age is:' || age);
END;


ORA-06550: line 4, column 9: PLS-00103: Encountered the symbol "&" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specific


How do we read an input from the user?

with LiveSQL Test Case:

and Chris said...

As "Unsupported Command" suggests, "set define on" doesn't work in LiveSQL. The ampersand indicates a substitution variable.

To use these you need to use an interactive environment link SQLcl, SQL*Plus or SQL Developer.

For example, when I run it in SQLcl I get:

SQL> SET DEFINE ON
SQL>
SQL> DECLARE
  2  age VARCHAR2(2);
  3  BEGIN
  4   age:= &age;
  5   dbms_output.put_line('Your age is:' || age);
  6  END;
  7  /
Enter value for age: 21
old:DECLARE
age VARCHAR2(2);
BEGIN
 age:= &age;
 dbms_output.put_line('Your age is:' || age);
END;

new:DECLARE
age VARCHAR2(2);
BEGIN
 age:= 21;
 dbms_output.put_line('Your age is:' || age);
END;
Your age is:21


They're called substitution variables because the client replaces the &variable with the value you pass. Then sends that to the database.

LiveSQL has no back-and-forth to do this.

Substitution variables are handy for command line scripts where you want users to supply some of their own values.

But it's rare for people to call PL/SQL from the command line. The vast majority of the time it's called via an application written in some other language (C, Java, ...).

In these applications, you can't use substitution variables. You need to use bind variables instead. These are placeholders. So the user input never becomes part of the executed statement. These are critical for security and performance.

If you want to know more about these, the following articles give a good comparison:

https://community.oracle.com/docs/DOC-915518
https://oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables

PS - your LiveSQL link appears to be invalid...

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library