mikcutu mikcutu - 6 months ago 87
SQL Question

PLS-00222: no function with name 'INFO_TYPE' exists in this scope

I am trying to return a table of records in 2 situations:


  1. using a function

  2. using a anonymous block



When I am using the function, everything is working just fine but when I am trying to transform it into anonymous block i receive the above error.
Here are the codes:

For the function:

create or replace
function get_info(p_city varchar2) return info_type_table
as
l_info info_type_table := info_type_table();
begin
for i in (select e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
from ntw_employees e
inner join
ntw_orders o
on e.employeeid = o.employeeid
inner join
ntw_customers c
on o.customerid = c.customerid
where e.city = p_city)
loop
l_info.extend;
l_info(l_info.count) := (info_type(i.employeeid, i.lastname, i.customerid, i.companyname, i.orderid, i.orderdate));
end loop;
return l_info;
end;


And here is for anonymous block:

declare
type info_type is record
(
emp_no number(3),
lastname varchar2(26),
cust_no varchar2(5),
CO_name varchar2(50),
orderid number(5),
orderdate date
);

type info_type_table is table of info_type;

l_info info_type_table := info_type_table();
begin
for i in (select e.employeeid,
e.lastname,
c.customerid,
c.companyname,
o.orderid,
o.orderdate
from ntw_employees e
inner join
ntw_orders o
on e.employeeid = o.employeeid
inner join
ntw_customers c
on o.customerid = c.customerid
where e.city = 'London')
loop
l_info.extend;
l_info(l_info.count) := (info_type(i.employeeid, i.lastname, i.customerid, i.companyname, i.orderid, i.orderdate));
dbms_output.put_line('angajat = ' || i.employeeid);
end loop;
end;


Can anyone explain me what is wrong in my anonymous block, please?

Thank you.

Answer

Your function is (presumably) referring to an object type called info_type. Your anonymous block is using a record type. Record types do not have constructors. You have to assign each column individually, and have a record-type variable:

...
    l_info_rec info_type;
begin
    ...
    loop
        l_info.extend;
        l_info_rec.emp_no := i.employeeid;
        l_info_rec.lastname := i.lastname;
        l_info_rec.cust_no := i.customerid;
        l_info_rec.CO_name := i.companyname;
        l_info_rec.orderid := i.orderid;
        l_info_rec.orderdate := i.orderdate;

        l_info(l_info.count)  :=  l_info_rec;
        dbms_output.put_line('angajat = ' || i.employeeid);
    end loop;
end;

You could also have an explicit cursor and use the row that returns:

declare
    cursor c is select  e.employeeid, 
                      e.lastname, 
                      c.customerid,
                      c.companyname,
                      o.orderid,
                      o.orderdate
              from  ntw_employees e
                inner join 
                    ntw_orders    o
                    on e.employeeid = o.employeeid
                inner join 
                    ntw_customers c
                    on o.customerid = c.customerid
              where e.city  = 'London';

    type info_type_table is table of c%rowtype;

    l_info info_type_table := info_type_table();
begin
    for r in c
    loop
        l_info.extend;
        l_info(l_info.count)  :=  r;
        dbms_output.put_line('angajat = ' || r.employeeid);
    end loop;
end;
/

Or even with bulk collect straight into your table type:

declare
    cursor c is select  e.employeeid, 
              ...
              where e.city  = 'London';

    type info_type_table is table of c%rowtype;

    l_info info_type_table;
begin
    open c;
    fetch c bulk collect into l_info;
    close c;

    for i in 1..l_info.count loop
        dbms_output.put_line('angajat = ' || l_info(i).employeeid);
    end loop;
end;
/

... with a loop just to display the values from that table.