oracle sql not accepting the input from the user

Question

Whenever i try to run a program consisting of user input the compiler always throws the same error of i dont know what that means. the error does not explain what the error is that i am encountering, can anyone please help regarding this problem. Here is the error:

Unsupported Command
Unsupported Command
ORA-06550: line 6, column 4:
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

Here is the code for plsql in which i am taking user input for temperature and returning the celcius or farenheight temp of the same.

declare
f number;
n number;
ch char;
begin
f:=&f;
ch:=&ch;
if(ch!='F') then
n:=(f-32)*5/9;
else
n:=(f*9/5)+32;
end if;
dbms_output.put_line('Temperature: '||f||' '||ch);
end;
/

I would be highly grateful for your efforts. Thank you

It tells you right there Encountered the symbol "&"... what is this BTW? -- f:=&f; ???? — T.S.
– T.S., Commented Oct 3, 2022 at 14:07
As in your previous question you are mixing up SQL, PL/SQL and client commands and functionality. Substitution variables are a client thing - so you could run that from SQL*Plus etc., but not from - guessing from the previous question - Apex. Whether you're now using Apex or some other front end, you need to see how to prompt for user input and pass that to the database. (You can't prompt directly from PL/SQL...) — Alex Poole
– Alex Poole, Commented Oct 3, 2022 at 14:09
@MohdArslaan - only if you are using a client that recognises substitution variables, like SQL*Plus, SQL Developer, SQLcl or some third-party clients. Whatever you are using (Apex?) does not. What client/application are you trying to run this in? — Alex Poole
– Alex Poole, Commented Oct 3, 2022 at 14:18
@MohdArslaan No, you should use the correct syntax for the online compiler you are using. (Rather than trying to use a syntax for a different tool where it is not supported or changing tools). — MT0
– MT0, Commented Oct 3, 2022 at 14:22
@MohdArslaan This is invalid syntax for PL/SQL. &-variables are part of SQL Plus etc — T.S.
– T.S., Commented Oct 3, 2022 at 15:35

MT0 · Accepted Answer · 2022-10-03 14:19:50Z

1

The & syntax indicates the variable is a substitution variable which is processed in the client application (it is effectively a find-replace on the variable).

Substitution variables are only supported by a limited number of client applications (including SQL*Plus and SQL Developer); they are not supported in other applications such as Oracle Apex, Java, C#, Python, etc.

You are getting the error because your client application does not support substitution variables; instead, you need to use the correct syntax for the application you are using (which may be to use bind variables).

answered Oct 3, 2022 at 14:19

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

Mohd Arslaan Over a year ago

can you please suggest any online compiler where i can run my plsql commands.

MT0 Over a year ago

@MohdArslaan You can run PL/SQL code in many online fiddles such as fiddle but they usually do not support substitution or bind variables.

Koen Lostrie · Accepted Answer · 2022-10-04 06:33:00Z

0

This can be run in the "SQL Commands" window in apex, but you need to use the bind variable syntax :

declare
  f number;
  n number;
  ch char;
begin
  f:=:f;
  ch:=:ch;
  if(ch!='F') then
    n:=(f-32)*5/9;
  else
    n:=(f*9/5)+32;
  end if;
  dbms_output.put_line('Temperature: '||f||' '||ch);
end;
/

This will invoke a popup window in which you are prompted for the values of :F and :CH.

answered Oct 4, 2022 at 6:33

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Collectives™ on Stack Overflow

oracle sql not accepting the input from the user

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related