Re: PLSQL table behavior in oracle 9i

Glenn wrote:
> Hi All,
>
> 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;
> END LOOP;
>
> Here is the CURSOR used for 8i.
>
> 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
>

I suppose that the change in behaviour is due to a problem discerning the data type of the column spc_code_item. Normally, NULL is interpreted as a VARCHAR2.

If spc_code_item is of type NUMBER, you could try changing the original cursor-statement to

CURSOR slp_spc_gp_cur ...
... DECODE(src_species_code_sddc, NULL, TO_NUMBER(NULL), Fisp$ce_Sumdoc.f_get_display_seq(src_species_code_sddc)) spc_code_item, ...

/Jørn Received on Wed Jun 15 2005 - 22:32:21 CEST