Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: numeric or value error
PL/SQL: numeric or value error [message #420223] Thu, 27 August 2009 10:47 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

I am getting the ORA-06502: PL/SQL: numeric or value error from the below program.


DECLARE
TYPE emprectyp IS RECORD (emp_id NUMBER, salary NUMBER(8,2));
 TYPE emptab IS TABLE OF emprectyp;
 vrec_tab emptab;
 v_rec emptab := emptab();
 CURSOR emp_cur 
 IS SELECT empno, sal FROM pemp ORDER BY sal;
FUNCTION nth_highest_salary (n integer) RETURN emptab IS
   BEGIN
      OPEN emp_cur;
      FOR i IN 1..n LOOP
      FETCH emp_cur bulk COLLECT INTO vrec_tab;
      END loop;
      CLOSE emp_cur;
      RETURN vrec_tab;
      END nth_highest_salary;
BEGIN
   v_rec := nth_highest_salary(5);
   dbms_output.put_line(v_rec.count);
  FOR j IN v_rec.first..v_rec.LAST loop
   dbms_output.put_line(v_rec(j).emp_id);
   END loop;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 20


Please help me in fixing this issue.

Thanks in advance.
Re: PL/SQL: numeric or value error [message #420227 is a reply to message #420223] Thu, 27 August 2009 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Please help me in fixing this issue.
>ORA-06512: at line 20
Which is line #20?

Obviously some Oracle version before V10

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: PL/SQL: numeric or value error [message #420228 is a reply to message #420223] Thu, 27 August 2009 10:52 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Presumably becuase your array is empty - try wrapping the first and last calls in nvls.

I think there's a serious problem with your function - it doesn't make any sense. What are you trying to achieve exactly?
Re: PL/SQL: numeric or value error [message #420230 is a reply to message #420228] Thu, 27 August 2009 10:58 Go to previous messageGo to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

Thanks for the reply. I have written below code to find out the nth highest salary from employees table.



SQL> DECLARE
  2  TYPE emprectyp IS RECORD (emp_id NUMBER, salary NUMBER(8,2));
  3   TYPE emptab IS TABLE OF emprectyp;
  4   vrec_tab emptab;
  5   v_rec emptab := emptab();
  6   CURSOR emp_cur 
  7   IS SELECT empno, sal FROM pemp ORDER BY sal;
  8  FUNCTION nth_highest_salary (n integer) RETURN emptab IS
  9     BEGIN
 10        OPEN emp_cur;
 11        FOR i IN 1..n LOOP
 12        FETCH emp_cur bulk COLLECT INTO vrec_tab;
 13        END loop;
 14        CLOSE emp_cur;
 15        RETURN vrec_tab;
 16        END nth_highest_salary;
 17  BEGIN
 18     v_rec := nth_highest_salary(5);
 19     dbms_output.put_line(v_rec.count);
 20    FOR j IN v_rec.first..v_rec.LAST loop
 21     dbms_output.put_line(v_rec(j).emp_id);
 22    null;
 23     END loop;
 24  END;
 25  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 20
Re: PL/SQL: numeric or value error [message #420232 is a reply to message #420223] Thu, 27 August 2009 11:06 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've massively over complicated it.

This can be done in a single query without the need of loops, bulk collect or arrays by using rownum.

And if you want the nth highest you need to order by sal DESC otherwise you'll get the nth lowest.

Re: PL/SQL: numeric or value error [message #420267 is a reply to message #420230] Fri, 28 August 2009 00:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just some thoughts on your code, in addition to what cookiemonster already said:
You need to read up on BULK COLLECT. The loop you use to fetch makes no sense. Bulk collect is not simply a "fast = true" switch that you can insert without changing anything else; it fetches more than a single row at a time.
Further, you should declare vrec_tab in your local function. You don't use it in the outer code; always keep the scope of variables as small as possible.
Then note the naming of your variables. Suppose you would have to do maintenance on a function I built and that you have never seen before. You see two variables, v_rec and v_rec_tab. Would your first idea not be that v_rec_tab was a table of v_rec?
Re: PL/SQL: numeric or value error [message #420322 is a reply to message #420267] Fri, 28 August 2009 05:34 Go to previous messageGo to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi All,

Thanks for your valuable suggestions. I have gone through the bulk collect and completed the below programs successfully.

Please let me know if I made any mistakes in my programs.

Thanks


-- This is for finding the nth highest salary in the given range.

SQL> SELECT empno, sal FROM pemp ORDER BY sal DESC;

    EMPNO       SAL
--------- ---------
     7839      5000
     7902      3000
     7788      3000
     7566      2975
     7698      2850
     7782      2450
     7499      1600
     7844      1500
     7934      1300
     7521      1250
     7654      1250
     7876      1100
     7900       950
     7369       800

14 rows selected.

SQL> DECLARE
  2      TYPE emprec IS RECORD (empid NUMBER, sal NUMBER);
  3      TYPE emprec_tab IS TABLE OF emprec;
  4      vrec_tab1 emprec_tab;
  5      CURSOR emp_cur IS SELECT empno, sal FROM pemp ORDER BY sal DESC;
  6     FUNCTION nth_highest_sal (n INTEGER) RETURN emprec_tab IS
  7       vrec_tab emprec_tab := emprec_tab();
  8  BEGIN
  9        OPEN emp_cur;
 10        FETCH emp_cur BULK COLLECT INTO vrec_tab;
 11        dbms_output.put_line('Employee Number '|| vrec_tab(vrec_tab.FIRST).empid||' '||
 12        'Salary '||' '||vrec_tab(vrec_tab.FIRST).sal);
 13        CLOSE emp_cur;
 14        RETURN vrec_tab;
 15     END nth_highest_sal;
 16  BEGIN
 17  vrec_tab1 := nth_highest_sal(11);
 18  END;
 19  /
Employee Number 7839 Salary  5000

PL/SQL procedure successfully completed.


-- This is for finding the nth employee number and salary.

SQL> select rownum,empno, sal FROM pemp;

   ROWNUM     EMPNO       SAL
--------- --------- ---------
        1      7369       800
        2      7499      1600
        3      7521      1250
        4      7566      2975
        5      7654      1250
        6      7698      2850
        7      7782      2450
        8      7788      3000
        9      7839      5000
       10      7844      1500
       11      7876      1100
       12      7900       950
       13      7902      3000
       14      7934      1300

14 rows selected.

SQL> DECLARE
  2      TYPE emprec IS RECORD (empid NUMBER, sal NUMBER);
  3      TYPE emprec_tab IS TABLE OF emprec;
  4      vrec_tab1 emprec_tab;
  5      CURSOR emp_cur IS SELECT empno, sal FROM pemp;
  6     FUNCTION nth_sal (n INTEGER) RETURN emprec_tab IS
  7       vrec_tab emprec_tab := emprec_tab();
  8  BEGIN
  9        OPEN emp_cur;
 10        FETCH emp_cur BULK COLLECT INTO vrec_tab;
 11        dbms_output.put_line('Employee Number '|| vrec_tab(n).empid||' '||
 12        'Salary '||' '||vrec_tab(n).sal);
 13        CLOSE emp_cur;
 14        RETURN vrec_tab;
 15     END nth_sal;
 16  BEGIN
 17  vrec_tab1 := nth_sal(11);
 18  END;
 19  /
Employee Number 7876 Salary  1100

PL/SQL procedure successfully completed.

Re: PL/SQL: numeric or value error [message #420324 is a reply to message #420322] Fri, 28 August 2009 05:41 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Thu, 27 August 2009 17:06
You've massively over complicated it.

This can be done in a single query without the need of loops, bulk collect or arrays by using rownum.


@OP Do you also ice cakes with cricket bats?
Previous Topic: problem in pl/sql code
Next Topic: Comparing two or more cursors
Goto Forum:
  


Current Time: Tue Dec 06 04:26:50 CST 2016

Total time taken to generate the page: 0.07902 seconds