code should be fairly simple but it is not working? [message #930] |
Wed, 13 March 2002 15:10 |
Sid
Messages: 38 Registered: May 1999
|
Member |
|
|
Why does oracle give this error message on line 12?
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 12
Line 12 is DBMS_OUTPUT.PUT_LINE('There is ' || v_counter ... );
SET SERVEROUTPUT ON
ACCEPT p_sal NUMBER PROMPT 'Please enter the salary: '
DECLARE
v_sal empp.sal%TYPE := TO_number(&p_sal);
CURSOR emp_cursor IS
select sal from emp;
v_counter NUMBER(40);
BEGIN
FOR i IN emp_cursor LOOP
IF i.sal BETWEEN v_sal-100 AND v_sal+100 THEN
v_counter := v_counter + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('There is ' || v_counter || ' employee(s) with a salary
between ' || v_sal-100 || ' and ' || v_sal+100);
end;
/
|
|
|
Re: code should be fairly simple but it is not working? [message #931 is a reply to message #930] |
Wed, 13 March 2002 18:29 |
sokeh
Messages: 77 Registered: August 2000
|
Member |
|
|
sql*plus sometimes requires that a number datatype be converted to character when using dbms_output.put_line package especially when mixing with character datatypes.
so in your situation, you need to convert salary to a character datatype.
So try this and let me know if it solves your problem:
DECLARE
v_sal emp.sal%TYPE := TO_number(&p_sal);
CURSOR emp_cursor IS
select sal from emp;
v_counter NUMBER(40);
BEGIN
FOR i IN emp_cursor LOOP
IF i.sal BETWEEN v_sal-100 AND v_sal+100 THEN
v_counter := v_counter + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('There is ' || to_char(v_counter) ||' '|| 'employee(s) with a salary between ' ||' '|| to_char(v_sal-100) ||' '|| ' and' ||' '|| to_char(v_sal+100));
end;
/
|
|
|