Object type returns error

[getjbb]

I am using 9i:

My problem is I am trying to return a table from a function, but I am getting the following error:

ORA-06550: line 2, column 36:
PLS-00302: component 'GET_TAB_PRODS' must be declared
ORA-06550: line 2, column 10:
PL/SQL: Item ignored
ORA-06550: line 8, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

It may be the way I declared my table as an object:

CREATE OR REPLACE TYPE get_mort_prods AS OBJECT (
rec_id number(4),
loan_num number(12),
no_days number(4),
pap_code varchar2(8),
prod_code varchar2(2),
name varchar2(75)
);

create or replace type get_tab_prods as table of get_mort_prods;
==============================================

The function within the package, get_mort_info:

Create or Replace package body pk_mortg_get_info is

FUNCTION get_mort_info ( a_account IN varchar2) return get_mort_prods IS

a_mortg get_tab_prods;
i_count number := 0;

cursor c_mort is
SELECT distinct rec_id, loan_num, no_days, pap_code, prod_code, name
FROM mort
WHERE mort.acct_no = a_account
ORDER BY rec_id;

BEGIN



FOR r_mort in c_mort LOOP
i__count := i_count + 1;
a_mortg(i_count).rec_id := r_mort.rec_id;
a_mortg(i_count).loan_num := r_mort.loan_num;
a_mortg(i_count).no_days := r_mort.no_days;
a_mortg(i_count).pap_code := r_mort.pap_code;
a_mortg(i_count).prod_code := r_mort.prod_code;
a_mortg(i_count).name := r_mort.name);
END LOOP;


----

return a_mortg;


END get_mort_info;


END pk_mortg_get_info ;

I did grant permission for, 'GET_TAB_PRODS' , but that did not help. Is there any other way of during this with out implementing declaring objects if that is the problem?

Thanks.

 

[Dagon]

Are all the objects being created by the same user?

 

[Dagon]

All objects and packages, that is.

 

[Dagon]

BTW, even if you get round the problem of it not recognising your type, the code is completely wrong. You haven't initialized your objects or extended them to add new elements. I've put together an example to show you how you should use object types in PL/SQL.

CREATE OR REPLACE TYPE get_mort_prods AS OBJECT (
rec_id number(4),
loan_num number(12),
no_days number(4),
pap_code varchar2(8),
prod_code varchar2(2),
name varchar2(75)
);
/

create or replace type get_tab_prods as table of get_mort_prods;
/

Create or Replace package pk_mortg_get_info is
FUNCTION get_mort_info ( a_account IN varchar2) return get_mort_prods;
end; 
/

Create or Replace package body pk_mortg_get_info is
FUNCTION get_mort_info ( a_account IN varchar2) return get_mort_prods IS
a_mortg get_tab_prods := get_tab_prods();
m get_mort_prods;
begin
  for i in 1..5 loop
    a_mortg.extend;
    a_mortg(i) := get_mort_prods(i, i, i, 'X'||i, 'Y'||i, 'Z'||i);
  end loop;
  m := a_mortg(5);
  return m;
end;
END;
/

declare
  m get_mort_prods;
begin
  M := pk_mortg_get_info.get_mort_info('xx');
  dbms_output.put_line(m.rec_id);
end;
/

 

[getjbb]

Thanks, Dagon