I want to call a PL/SQL function with
select consult_trac.get_detail_dos_amo('12345') from dual but I get the error:
java.sql.SQLException: ORA-06530: Reference to uninitialized composite
ORA-06512: à "CNSS_SERVICES.GET_DETAIL_DOS_AMO" at line 60 (ret(v_counter).num_doss := DS_DT.NUM_DOSS;)
My PL/SQL function is defined in a package with all the types.
Package definition:
CREATE OR REPLACE PACKAGE consult_trac AS FUNCTION get_detail_dos_amo (p_num_doss VARCHAR2) RETURN tab_dos_t_amo; END consult_trac; / CREATE OR REPLACE PACKAGE BODY consult_trac AS FUNCTION get_detail_dos_amo (p_num_doss VARCHAR2) RETURN tab_dos_t_amo IS CURSOR DOSS_DET (num_doss VARCHAR2) IS SELECT NUM_DOSS, DAT_DEP, NUM_IMMA, NUM_IND, P_DATE_ACTE, CODE_EVOP, LIB_EVOP, CODE_DR, LIB_DR, C_USER, C_GENCE, C_NIV, L_NIV, DAT_SUI, C_D_ETAT, L_D_ETAT, L_NAT, NUM_RECOM, ACCUSE, P_MONTANT, NUM_D_PARENT, P_INP_DOS, P_ICE_DOS, P_CIN, P_NOM, P_PRENOM, P_N_PAGE, P_D_SORTIE, P_C_CAT, P_C_SOURCE, C_ERROR FROM TYP_DOSS_AMO WHERE NUM_DOSS = p_num_doss; CURSOR SEL_MEDIC(num_doss VARCHAR2) IS SELECT CODE_MEDIC, NOMBRE FROM DOSS_MEDIC WHERE NUM_DOSS = num_doss; CURSOR SEL_INP(num_doss VARCHAR2) IS SELECT CODE_INP FROM DOSS_INP WHERE NUM_DOSS = num_doss; ret tab_dos_t_amo; ret_med tab_medic; ret_inp tab_inp; v_counter number := 0; v_counter_med number := 0; v_counter_inp number := 0; BEGIN FOR DS_DT IN DOSS_DET(p_num_doss) LOOP ret(v_counter).num_doss := DS_DT.NUM_DOSS; ret(v_counter).dat_dep := DS_DT.DAT_DEP; ret(v_counter).num_imma := DS_DT.NUM_IMMA; ret(v_counter).num_ind := DS_DT.NUM_IND; ret(v_counter).p_date_acte := DS_DT.P_DATE_ACTE; ret(v_counter).code_evop := DS_DT.CODE_EVOP; ret(v_counter).lib_evop := DS_DT.LIB_EVOP; ret(v_counter).code_dr := DS_DT.CODE_DR; ret(v_counter).lib_dr := DS_DT.LIB_DR; ret(v_counter).c_user := DS_DT.C_USER; ret(v_counter).c_gence := DS_DT.C_GENCE; ret(v_counter).c_niv := DS_DT.C_NIV; ret(v_counter).l_niv := DS_DT.L_NIV; ret(v_counter).dat_sui := DS_DT.DAT_SUI; ret(v_counter).c_d_etat := DS_DT.C_D_ETAT; ret(v_counter).l_d_etat := DS_DT.L_D_ETAT; ret(v_counter).l_nat := DS_DT.L_NAT; ret(v_counter).num_recom := DS_DT.NUM_RECOM; ret(v_counter).accuse := DS_DT.ACCUSE; ret(v_counter).p_montant := DS_DT.P_MONTANT; ret(v_counter).num_d_parent := DS_DT.NUM_D_PARENT; ret(v_counter).p_INP_dos := DS_DT.P_INP_DOS; ret(v_counter).p_ICE_dos := DS_DT.P_ICE_DOS; ret(v_counter).p_cin := DS_DT.P_CIN; ret(v_counter).p_nom := DS_DT.P_NOM; ret(v_counter).p_prenom := DS_DT.P_PRENOM; ret(v_counter).p_n_page := DS_DT.P_N_PAGE; ret(v_counter).p_d_sortie := DS_DT.P_D_SORTIE; ret(v_counter).p_c_cat := DS_DT.P_C_CAT; ret(v_counter).p_c_source := DS_DT.P_C_SOURCE; ret(v_counter).c_error := DS_DT.C_ERROR; FOR SM IN SEL_MEDIC(p_num_doss) LOOP ret_med(v_counter_med).code_medic := SM.CODE_MEDIC; ret_med(v_counter_med).nombre := SM.NOMBRE; v_counter_med := v_counter_med +1; END LOOP; FOR SI IN SEL_INP(p_num_doss) LOOP ret_inp(v_counter_inp).code_inp := SI.CODE_INP; v_counter_inp := v_counter_inp +1; END LOOP; ret(v_counter).p_tab_medic := ret_med; ret(v_counter).p_tab_inp := ret_inp; v_counter := v_counter + 1; END LOOP; return ret; END; END consult_trac; / Types:
create or replace TYPE MEDIC AS OBJECT ( num_doss VARCHAR2(9), code_medic VARCHAR2(10), nombre NUMBER); create or replace TYPE tab_medic IS TABLE OF MEDIC; create or replace TYPE INP AS OBJECT ( num_doss VARCHAR2(9), code_inp VARCHAR2(20)); create or replace TYPE tab_INP IS TABLE OF INP; create or replace TYPE TYP_DOS_AMO AS OBJECT ( num_doss VARCHAR2(9), dat_dep DATE, num_imma VARCHAR2(13), num_ind VARCHAR2(20), p_date_acte DATE, code_evop VARCHAR2(20), lib_evop VARCHAR2(30), code_dr VARCHAR2(20), lib_dr VARCHAR2(30), c_user VARCHAR2(20), c_gence VARCHAR2(20), c_niv VARCHAR2(20), l_niv VARCHAR2(20), dat_sui DATE, c_d_etat VARCHAR2(20), l_d_etat VARCHAR2(20), l_nat VARCHAR2(30), num_recom VARCHAR2(30), accuse VARCHAR2(30), p_montant VARCHAR2(20), num_d_parent VARCHAR2(9), p_INP_dos VARCHAR2(20), p_ICE_dos VARCHAR2(20), p_cin VARCHAR2(10), p_nom VARCHAR2(20), p_prenom VARCHAR2(20), p_n_page VARCHAR2(20), p_d_sortie DATE, p_c_cat VARCHAR2(15), p_c_source VARCHAR2(15), p_tab_medic tab_medic, p_tab_inp tab_inp, c_error INTEGER ); create or replace TYPE tab_INP IS TABLE OF INP; 1 Answer
A nested table object is not the same as an associative array, so it must be initialised before usage.
Thus
ret tab_dos_t_amo probably needs to be
ret tab_dos_t_amo := tab_dos_t_amo(); Now as you want to add elements to it, you need to add an index to the array, for example
ret.extend; which now allows you to reference ret(1).
You didn't show us the definition for tab_dos_t_amo but I assume it is a table of dos_t_amo. So each entry in that table is an object, which means your code:
ret(v_counter).num_doss := DS_DT.NUM_DOSS; ret(v_counter).dat_dep := DS_DT.DAT_DEP; ret(v_counter).num_imma := DS_DT.NUM_IMMA; would now look like
ret.extend; ret(v_counter) := dos_t_amo( DS_DT.DAT_DEP, DS_DT.NUM_IMMA, ... ...); 
