CODEWITHSUNDEEP
sqloracle-databasevariablesplsql
TheSpaceboy0
TheSpaceboy0

Reputation: 95

simple Variable Daclaration

I can't understand why that variable declaration command doesn't work. Could you help me?

SET SERVEROUTPUT ON
ACCEPT rek_pers PROMPT 'Employee name: '
ACCEPT year_sal PROMPT 'Yearly salary: '

VARIABLE vENAME VARCHAR2,vSAL NUMBER

DECLARE
:vENAME := &rek_pers;
:vSAL := &year_sal;

BEGIN  
UPDATE Emp SET Emp.Sal = vSAL
Where Emp.Ename = vENAME;

COMMIT;
END;
/

I receive answer: Bind Variable "vENAME" is NOT DECLARED

Thanks in advance for your help!

Upvotes: 1

Views: 128

Answers (2)

DazzaL
DazzaL

Reputation: 21993

you have several errors.

variables should be defined seperatley, not with commas. i.e.:

VARIABLE vENAME VARCHAR2
VARIABLE vSAL NUMBER

secondly this is invalid syntax:

:vENAME := &rek_pers;
:vSAL := &year_sal;

it should just be simply (without the variable defines needed):

DECLARE
vSAL Emp.Sal%type := &year_sal;
vENAME Emp.Ename%type := '&rek_pers';
BEGIN  
UPDATE Emp SET Emp.Sal = vSAL
Where Emp.Ename = vENAME;

COMMIT;
END;
/

if you did want to assign a variable, the correct way would be:

VARIABLE vENAME VARCHAR2(200)
VARIABLE vSAL NUMBER
EXEC :vSAL := &year_sal;
EXEC :vENAME := '&rek_pers';
BEGIN  
UPDATE Emp SET Emp.Sal = :vSAL
Where Emp.Ename = :vENAME;
COMMIT;
END;
/

(p.s. don't forget the length of the varchar2)

Upvotes: 4

agim
agim

Reputation: 1841

Do not use colon by declaring the variable. try:

DECLARE

vENAME := &rek_pers;

vSAL := &year_sal;

Upvotes: 0

Related Questions