PLSQL table behavior in oracle 9i

Just a quick question I thought I'd pass along. In Oracle 8.1.7 I used a procedure to populate a PLSQL table. I used a cursor and an iterative process to populate the PLSQL table as shown below.

BEGIN
    FOR spc_gp_rec IN slip_spc_gp_cur LOOP

      inout_spc_gp_tab (v_indx ) := spc_gp_rec;
      v_indx := v_indx + 1;

CURSOR slp_spc_gp_cur IS
SELECT DECODE(src_species_code_sddc, NULL, NULL, Fisp$ce_Sumdoc.f_get_display_seq(src_species_code_sddc)) spc_code_item, dflt_species_code,
DECODE(src_weight_sddc, NULL, NULL,
Fisp$ce_Sumdoc.f_get_display_seq(src_weight_sddc)) weight_item, etc.. FROM SD_COL_GRP_SLP_SPC s, SUM_DOC_DEFN_COLS c WHERE s.sum_doc_defn_id = in_sum_doc_defn_id AND s.src_weight_sddc = c.sum_doc_defn_col_id;

Notice how I used the DECODE statements in case there was a null. When I tried compiling this in Oracle 9i then I would get a PLS-00382 error (expression is of wrong type) when populating the PLSQL table. I would get this on the spc_gp_rec.

In 9i I had to use the CASE statement to get the procedure to compile.

CURSOR slp_spc_gp_cur IS
SELECT CASE WHEN
src_species_code_sddc = NULL THEN NULL ELSE Fisp$ce_Sumdoc.f_get_display_seq(src_species_code_sddc) END spc_code_item,
 dflt_species_code,
CASE WHEN src_weight_sddc= NULL THEN NULL ELSE Fisp$ce_Sumdoc.f_get_display_seq(src_weight_sddc) END weight_item, etc.
FROM SD_COL_GRP_LOG_SPC s, SUM_DOC_DEFN_COLS c WHERE s.sum_doc_defn_id = in_sum_doc_defn_id AND s.src_weight_sddc = c.sum_doc_defn_col_id;

I just thought I'd pass this along and see if anybody could tell me why this was so. Thanks.

Glenn Received on Tue Jun 14 2005 - 22:20:58 CEST