Home » SQL & PL/SQL » SQL & PL/SQL » code should be fairly simple but it is not working?
code should be fairly simple but it is not working? [message #930] Wed, 13 March 2002 15:10 Go to next message
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 Go to previous message
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;
/
Previous Topic: order by procedure parameter
Next Topic: create new record with unique primary key...
Goto Forum:
  


Current Time: Thu Mar 28 17:52:35 CDT 2024