Getting Error ORA-00932: inconsistent datatypes: expected UDT got NUMBER

Hi,

Please please could someone help me here... I am getting an error when trying to assign the value of a cursor to a varray (10g). I am not very familiar with collections so don't know if I am doing it correctly. The problem is at this line:

v_tab_prim_cust(i) := type_amal_indiv_row(rec_prim_indiv.cust_id,rec_prim_indiv.title,rec_prim_indiv.forename,rec_prim_indiv.surname,rec_prim_indiv.name_known_by,rec_prim_indiv.payee_name,rec_prim_indiv.suffix,rec_prim_indiv.gender,rec_prim_indiv.birth_date,rec_prim_indiv.death_yn,rec_prim_indiv.death_date,rec_prim_indiv.audit_date,rec_prim_indiv.audit_user,rec_prim_indiv.cur_hist_flag );            -- add history data to current element in array for display to screen

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [pfrsxi_sql_xlt_ind()+122] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
ORA-00932: inconsistent datatypes: expected UDT got NUMBER

I originally had this line as "v_tab_prim_cust(i) := rec_prim_cust;"  and was getting the same error so I made the changes that are here to try to convert the cursor to the format of the User Defined Type ie. the varray.

Would appreciate any help.

1) Types Definition from Package Spec:

CREATE OR REPLACE TYPE  type_amal_indiv_row 
AS OBJECT (cust_id    		NUMBER(12),
	   title		VARCHAR2(6),
	   forename		VARCHAR2(35),
	   surname		VARCHAR2(35),
	   name_known_by	VARCHAR2(35),
	   payee_name		VARCHAR2(35),
	   suffix		VARCHAR2(6),
	   gender		VARCHAR2(10),
	   birth_date		DATE,
	   death_yn		VARCHAR2(1),
	   death_date		DATE,
	   audit_date		DATE,
	   audit_user		VARCHAR2(10),
	   cur_hist_flag	VARCHAR2(1));
/ 

CREATE TYPE type_amal_indiv_array AS VARRAY(50) OF type_amal_indiv_row; 
/ 



2) Cursor Definition from Package Body:

  CURSOR cur_prim_indiv (p_prim_cust_id	tdco_customers.cust_id%TYPE)
  RETURN type_amal_indiv_row
  IS
  SELECT cust_id,ttl_abbr title,cust_forename forename,cust_surname surname,cust_name_known_by name_known_by,
	 cust_payee_name payee_name,suf_abbr suffix,gen_desc gender,cust_birth_date birth_date,cust_death_yn death_yn,
	 cust_death_date death_date,cust_audit_date audit_date,cust_audit_user audit_user, 'H' cur_hist_flag -- JD.28-Jan-2008
  FROM   taco_customers,
	 tsco_titles,
	 tsco_suffixes,
	 tsco_genders
  WHERE  cust_id =  p_prim_cust_id
  AND    cust_ttl_code = ttl_code(+)
  AND	 cust_suf_code = suf_code(+)
  AND    cust_gen_code = gen_code(+)
  UNION
  SELECT cust_id,ttl_abbr title,cust_forename forename,cust_surname surname,cust_name_known_by name_known_by,
	 cust_payee_name payee_name,suf_abbr suffix,gen_desc gender,cust_birth_date birth_date,cust_death_yn death_yn,
	 cust_death_date death_date,cust_audit_date audit_date,cust_audit_user audit_user, 'C' cur_hist_flag -- JD.28-Jan-2008
  FROM   tdco_customers,
	 tsco_titles,
	 tsco_suffixes,
	 tsco_genders
  WHERE  cust_id =  p_prim_cust_id
  AND    cust_ttl_code = ttl_code(+)
  AND	 cust_suf_code = suf_code(+)
  AND    cust_gen_code = gen_code(+)
  ORDER BY audit_date; 	



3) Procedure from Package Body:

PROCEDURE pr_get_individual_history(p_prim_cust_id	tdco_customers.cust_id%TYPE,
			 p_prim_cust_list	    OUT rcco_indiv_history) IS

  sql_statement     VARCHAR2(100) := 'ALTER SESSION SET NLS_DATE_FORMAT =  ''DD-MON-YYYY HH24:MI:SS''';

  i NUMBER(3);
  j NUMBER(3);

  v_tab_prim_cust   type_amal_indiv_array  := type_amal_indiv_array(type_amal_indiv_row(NULL,'','','','','','','','','','','','','')); -- JD.28-Jan-2008

  BEGIN

   i := 1;    -- current record
   j := 0;    -- previous record
   FOR rec_prim_indiv IN cur_prim_indiv(p_prim_cust_id) LOOP


       IF rec_prim_indiv.cur_hist_flag = 'C'		
	  OR i = 1 					
	  OR (i > 1 AND (NVL(v_tab_prim_cust(j).title, ' ') != NVL(rec_prim_indiv.title, ' ') OR  
		    	NVL(v_tab_prim_cust(j).forename, ' ') != NVL(rec_prim_indiv.forename, ' ') OR 
		    	NVL(v_tab_prim_cust(j).surname, ' ') != NVL(rec_prim_indiv.surname, ' ') OR
		    	NVL(v_tab_prim_cust(j).name_known_by, ' ') != NVL(rec_prim_indiv.name_known_by, ' ') OR
		    	NVL(v_tab_prim_cust(j).payee_name, ' ') != NVL(rec_prim_indiv.payee_name, ' ') OR
		    	NVL(v_tab_prim_cust(j).suffix, ' ') != NVL(rec_prim_indiv.suffix, ' ') OR
		    	NVL(v_tab_prim_cust(j).gender, ' ') != NVL(rec_prim_indiv.gender, ' ') OR
		    	NVL(v_tab_prim_cust(j).birth_date, to_date('01/01/2000', 'DD/MM/YYYY')) != NVL(rec_prim_indiv.birth_date, to_date('01/01/2000', 'DD/MM/YYYY')) 
		    									))  THEN

          v_tab_prim_cust(i) := type_amal_indiv_row(rec_prim_indiv.cust_id,rec_prim_indiv.title,rec_prim_indiv.forename,rec_prim_indiv.surname,rec_prim_indiv.name_known_by,rec_prim_indiv.payee_name,rec_prim_indiv.suffix,rec_prim_indiv.gender,rec_prim_indiv.birth_date,rec_prim_indiv.death_yn,rec_prim_indiv.death_date,rec_prim_indiv.audit_date,rec_prim_indiv.audit_user,rec_prim_indiv.cur_hist_flag);		
          i := i+1;
	  j := j+1;
          v_tab_prim_cust.EXTEND();			
          v_tab_prim_cust(i) := type_amal_indiv_row(NULL,'','','','','','','','','','','','','');   

        END IF; 
   END LOOP;
   v_tab_prim_cust.trim();   				


  OPEN p_prim_cust_list
  FOR  SELECT * FROM TABLE(CAST (v_tab_prim_cust AS type_amal_indiv_array)) newtab1
       ORDER BY audit_date DESC, cur_hist_flag;							

END pr_get_individual_history;

Select allOpen in new window