CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Jannatul Ferdous Srabonee
Jannatul Ferdous Srabonee

Reputation: 31

in normal oracle function : ORA-06531: Reference to uninitialized collection

my function is executing perfectly but while declaring it , showing errors. The code is:

set serveroutput on

create or replace type myarray is varray(10000) of number;

create or replace function T_Bill (cus_id in integer) return number as
    t_cost number:=0;
    n integer;   
    bk myarray;
    pr myarray;
    q myarray;   
begin
    select count(book_id) into n from issue where cust_id=cus_id;
    select book_id,quantity BULK COLLECT into bk,q from issue where cust_id=cus_id;
    for i in 1..n
    loop
        select price into pr(i) from book where ISBN=bk(i);
        DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
        t_cost:=pr(i);
    end loop;

    return t_cost;
end;
/

And Im declaring it like this:

declare 
 x integer:=1;
 begin
 DBMS_OUTPUT.PUT_LINE(T_Bill(x));
 end;
 /

And the errors are :

ORA-06531: Reference to uninitialized collection
ORA-06512: at "R1507090.T_BILL", line 12
ORA-06512: at line 4

Thanks in advance.

Upvotes: 1

Views: 9354

Answers (1)

MT0
MT0

Reputation: 167822

You are trying to set pr(i) when you have not initialised the collection. Also, you are overwriting t_cost each iteration of the loop.

create or replace function T_Bill (
  cus_id in integer
)
  return number
as
  t_cost number:=0;
  bk myarray;
  pr myarray;
  q  myarray;   
begin
  select book_id,quantity
  BULK COLLECT into bk,q
  from issue
  where cust_id=cus_id;

  pr := myarray();               -- Initialise the collection
  pr.EXTEND( bk.COUNT );         -- Set the size of the collection

  FOR i IN 1..bk.COUNT LOOP
    select price into pr(i) from book where ISBN=bk(i);
    DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
    t_cost:= t_cost + pr(i);
  END LOOP;

  return t_cost;
end;
/

However, the simplest solution is to use a join and populate all the collections at the same time (assuming there is a many-to-one relationship from issue to book on the ISBN primary key):

create function T_Bill (
  cus_id in ISSUE.CUST_ID%TYPE
)
  return number
as
    t_cost number :=0;
    bk myarray;
    pr myarray;
    q  myarray;   
begin
  select i.book_id,
         i.quantity,
         b.price
  BULK COLLECT into
         bk,
         q,
         pr
  from   issue i
         LEFT OUTER JOIN book b     -- INNER JOIN?
         ON ( i.book_id = b.ISBN )
  where  i.cust_id = cus_id;

  FOR i IN 1..n LOOP
    DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
    t_cost:= t_cost + pr(i);
  END LOOP;

  return t_cost;
end;
/

Upvotes: 2

Related Questions