Oracle

Accept/Prompt

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2008

Posts: 478

Reputation: maydhyam is an unknown quantity at this point

Solved Threads: 1

maydhyam

Offline

Posting Pro in Training

Accept/Prompt

May 11th, 2009

Hi All,

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

Query:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
/
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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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>
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?

The man who in view of gain thinks of righteousness; who in the view of danger is prepared to give up his life; and who does not forget an old agreement however far back it extends - such a man may be reckoned a complete man.
~ Confucius, The Confucian Analects

•

Join Date: May 2009

Posts: 1

Reputation: djbabu is an unknown quantity at this point

Solved Threads: 0

djbabu

Offline

Newbie Poster

Re: Accept/Prompt

May 13th, 2009

Try using $$empno on the where clause.

Lemme know whether it fixes your problem ..

•

Originally Posted by maydhyam

Hi All,

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

Query:
Help with Code Tags

sql Syntax (Toggle Plain Text)
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;
/
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:
Help with Code Tags

sql Syntax (Toggle Plain Text)
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>
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?