Accept/Prompt

[maydhyam]

Hi All,

I am working in Oracle10g and I have been trying to run the following query, and am getting an error:

Query:

SET VERIFY OFF

VARIABLE emp_salary NUMBER

ACCEPT empno PROMPT 'Please enter a valid employee number: '

SET AUTOPRINT ON

DECLARE
empno NUMBER(6):= &empno;
BEGIN
SELECT salary INTO :emp_salary FROM employees
WHERE employee_id = empno;
END;
/

Error:

empno NUMBER(6):= ;
                                     *
ERROR at line 2:
ORA-06550: line 2, column 19:
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> 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-quoted S



EMP_SALARY
----------


SQL>

If the error shows up distorted...the asterisk is located under the semi-colon in the error...
Could anyone guide me accordingly?

[djbabu]

Try using $$empno on the where clause.

Lemme know whether it fixes your problem ..

Hi All,

I am working in Oracle10g and I have been trying to run the following query, and am getting an error:

Query:

SET VERIFY OFF

VARIABLE emp_salary NUMBER

ACCEPT empno PROMPT 'Please enter a valid employee number: '

SET AUTOPRINT ON

DECLARE
empno NUMBER(6):= &empno;
BEGIN
SELECT salary INTO :emp_salary FROM employees
WHERE employee_id = empno;
END;
/

Error:

empno NUMBER(6):= ;
                                     *
ERROR at line 2:
ORA-06550: line 2, column 19:
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> 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-quoted S



EMP_SALARY
----------


SQL>

If the error shows up distorted...the asterisk is located under the semi-colon in the error...
Could anyone guide me accordingly?

[maydhyam]

Hi djbabu,

I did try $$empno in the where clause, but it still showed the same error...

[alit2002]

I'm no PL/SQL expert but have you tried a ; at the end of line 2?

VARIABLE emp_salary NUMBER;

Might help.

[maydhyam]

I did, and that brings about an error...

[gajanan123]

we cannot pass bind variables in interval area

[maydhyam]

Oh ok, thanks for your help.