Why this Code is Giving ERROR

Question

I am unable to use simple SELECT statement to initialize a variable.

DECLARE
A VARCHAR2(10);
BEGIN
A := (SELECT FIRST_NAME FROM FIMS_OWNER.EMPLOYEE_T WHERE WWID = 'NA734');
END;
/

ERROR:

[Error] Execution (4: 11): ORA-06550: line 4, column 11:
PLS-00103: Encountered the symbol "SELECT" 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 alternat
ORA-06550: line 4, column 76:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   * & - + ; / at for mod remainder rem <an exponent (**)> and
   or group having intersect minus order start union where
   connect || multiset

And if it's not possible, what's the alternative for the same.

PS: I am using such query to set the variable(and later use it) with my cursor which takes the WWID dynamically and then changes the value accordingly.

Image: https://i.stack.imgur.com/wzSDk.png

score 2 · Answer 1 · answered Dec 10 '14 at 12:24

2

Because in Oracle you cannot assign a query to a variable. However, you can query into a variable, as in ...

DECLARE
    A VARCHAR2(10);
BEGIN
    SELECT FIRST_NAME
    INTO A
    FROM FIMS_OWNER.EMPLOYEE_T
    WHERE WWID = 'NA734';
END;
/

answered Dec 10 '14 at 12:24

peter.hrasko.sk

4,043
2
19
34

score 1 · Accepted Answer · answered Dec 10 '14 at 12:24

1

your code should be

DECLARE
A VARCHAR2(10);
BEGIN
SELECT FIRST_NAME INTO A FROM FIMS_OWNER.EMPLOYEE_T WHERE WWID = 'NA734';
END;

You should use the INTO clause to select a value into a variable

answered Dec 10 '14 at 12:24

theDbGuy

903
1
9
22

Why this Code is Giving ERROR

2 Answers2