Skip to Main Content
  • Questions

  • Problem in dynamic variable initialization.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, ELIAHIM.

Asked: March 14, 2018 - 4:43 pm UTC

Last updated: August 08, 2023 - 1:48 pm UTC

Version: 18

Viewed 10K+ times! This question is

You Asked

My code is 
DECLARE
  a INTEGER;
BEGIN
  a :=  &a;
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;
/

Error:

SQL Worksheet
ClearView SessionResetSaveRun


1
2
3
4
5
6
7
DECLARE
  a INTEGER;
BEGIN
  a :=  &a;
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
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 specifi


How can i get the input value from keyboard??


with LiveSQL Test Case:
https://livesql.oracle.com/apex/f?p=590:1:7354930207414::NO:::

and Chris said...

You need to:

set define on


If it's off it won't work:

set define off
DECLARE
  a INTEGER;
BEGIN
  a := &a;
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;
/

Error report -
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
...


PS - when sharing LiveSQL script, you need to save them first. Then share the link this gives you!

Rating

  (2 ratings)

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

Comments

ELIAHIM JEEVARAJ, March 15, 2018 - 5:46 pm UTC

I faced same error after adding set define on
Unsupported Command

ORA-06550: line 4, column 7:
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 specifi



Unsupported Command
Chris Saxon
March 16, 2018 - 1:20 pm UTC

Please show us all the code you executed. And tell us which environment you used (SQL Developer, SQL*Plus, Java, ...)

error with & in sql live

sowmya, August 04, 2023 - 6:16 am UTC

declare
v_name scott.emp.ename%type;
v_no scott.emp.empno%type := &v_no;
begin
select ename into v_name
from scott.emp where
scott.empno=v_no;
dbms_output.put_line('emp name:'||v_name);
end;
ORA-06550: line 3, column 30:
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 specif


Chris Saxon
August 08, 2023 - 1:48 pm UTC

You can't use ampersand to accept user input in Live SQL. You need to replace &v_no with the value you want.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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