oracle pl/sql input error

Question

Code:

DECLARE
 N NUMBER :=&N;
BEGIN
 DBMS_OUTPUT.PUT_LINE(N);
END;

Error:

ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "&" when expecting one of the following:

( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

May I know the IDE you are working with?

Dinesh V
– Dinesh V

2016-02-17 03:53:15 +00:00
Commented Feb 17, 2016 at 3:53 — Dinesh V
– Dinesh V, Commented Feb 17, 2016 at 3:53
oracle 10g sql command

Anjan
– Anjan

2016-02-17 03:59:14 +00:00
Commented Feb 17, 2016 at 3:59 — Anjan
– Anjan, Commented Feb 17, 2016 at 3:59
Did you try set define on;

Dinesh V
– Dinesh V

2016-02-17 04:37:02 +00:00
Commented Feb 17, 2016 at 4:37 — Dinesh V
– Dinesh V, Commented Feb 17, 2016 at 4:37

Lalit Kumar B · Accepted Answer · 2016-02-17 06:22:35Z

3

You need to do:

SET DEFINE ON

I think you are using GUI client like SQL Developer, and you do not have define on. & is used for a substitution variable.

In SQL Developer:

SET serveroutput ON
SET define OFF
DECLARE
  N NUMBER :=&N;
BEGIN
  DBMS_OUTPUT.PUT_LINE(N);
END;
/

Error starting at line : 8 in command -
DECLARE
  N NUMBER :=&N;
BEGIN
  DBMS_OUTPUT.PUT_LINE(N);
END;
Error report -
ORA-06550: line 2, column 14:
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 specification>
   <an alternatively
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Set define on, and try again:

SQL> SET serveroutput ON
SQL> SET DEFINE ON
SQL> DECLARE
  2    N NUMBER :=&N;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE(N);
  5  END;
  6  /
Enter value for n: 10
old   2:   N NUMBER :=&N;
new   2:   N NUMBER :=10;
10

PL/SQL procedure successfully completed.

SQL>

edited Feb 17, 2016 at 6:22

answered Feb 17, 2016 at 6:15

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anjan Over a year ago

Its work successfully in SQL developer but it not working in oracle 10g "sql commands". Its says missing or invalid option.

Anjan Over a year ago

i can not define it proprly but it run in browser which 10g express edition and its "SQL commands " named where execute sql query.

Lalit Kumar B Over a year ago

You must tell what exactly you are doing. Which client you are using? At least, tell us if you know about the client?

Anjan Over a year ago

oracle 10g express edition and i write command in sql commands which open in browser (firefox,chrome etc.).

Collectives™ on Stack Overflow

oracle pl/sql input error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related