Conference orarep::nomahs::sql

3895.0. "SQL-F-UNDPARAM but parameter was declared" by ukvms3.uk.oracle.com::PJACKSON (Oracle UK Rdb Support) Thu Jun 05 1997 06:10

    A customer using Rdb 6.1 is having problems with indicator variables in
    a multistatement procedure. The problem seems to be that they are using
    them for variables declared within the procedure, but error message is
    confusing. Could someone confirm my diagnosis? Part of the mesage the
    customer sent follows.
    
    Peter
    
When they compile the SQL code they get the following error:

SQL-F-UNDPARAM, (1) Parameter V_SUPP_QTY is not declared in procedure
NN2_GET_SALES_BY_GROUP

If they take the indicator out it then compiles Ok but it falls over
with NULL not being trapped.
    .
    .
    .
MODULE          NN2_GET_SALES_BY_GROUP  -- Module name
DIALECT         SQL92
LANGUAGE        COBOL           -- Language of calling program
AUTHORIZATION   HO              -- Default authorization ID
PARAMETER COLONS                -- Parameters are prefixed with colons


------------------------------------------------------------------------
-
-- Declare Statement Section
------------------------------------------------------------------------
-

-- Attach to databases using aliases.
DECLARE HO ALIAS FILENAME HO_DB

------------------------------------------------------------------------
-
-- Procedure Section
------------------------------------------------------------------------
-
procedure NN2_GET_SALES_BY_GROUP_START
sqlstate;

set transaction read only reserving
        HDI_HOUSE_DP_ISSUES_TAB,
        SDT_TRANSACTIONS_TAB,
        SGL_TRANS_GROUP_LINKS_TAB for shared read;

procedure NN2_GET_SALES_BY_GROUP
--Parameters
--Input
:PI_HOUSE_NUMBER    House_no_dom,
:PI_ISSUE_SEQ_NO    Seq_no_dom,
--Output
:PO_TOTAL_SUPP      Qty_Nodecpl_dom,
:PO_TOTAL_RETR      Qty_Nodecpl_dom,
:PO_TOTAL_EARLY_RET Qty_Nodecpl_dom,
:PO_TOTAL_MISC      Qty_Nodecpl_dom,
:PO_PENETRATION     Qty_Nodecpl_dom,
:PO_NO_RETURNs      Qty_Nodecpl_dom,
:PO_INDICATOR       integer,
sqlstate;

BEGIN ATOMIC
DECLARE :V_SUPP_QTY  integer  ;
DECLARE :V_SUPP_IND  integer  ;
DECLARE :V_RETR_QTY  integer  ;
DECLARE :V_RETR_IND  integer  ;
DECLARE :V_MISC_QTY  integer  ;
DECLARE :V_MISC_IND  integer  ;
DECLARE :V_ELTR_QTY  integer  ;
DECLARE :V_ELTR_IND  integer  ;
DECLARE :V_CORR_QTY  integer  ;
DECLARE :V_CORR_IND  integer  ;

SET :V_SUPP_QTY  = 0;
SET :V_SUPP_IND  = 0;
SET :V_RETR_QTY  = 0;
SET :V_MISC_QTY  = 0;
SET :V_ELTR_QTY  = 0;
SET :V_CORR_QTY  = 0;
HDILOOP:
FOR  :HDIFOR as each row of
        SELECT DEL_PNT_SEQ_NO FROM HDI_HOUSE_DP_ISSUES_TAB
        WHERE HOUSE_NO = :PI_HOUSE_NUMBER
        AND   ISSUE_SEQ_NO = :PI_ISSUE_SEQ_NO
        AND   RECORD_STATUS= 'L'

        do
                SELECT SUM (QTY)
                into :v_supp_qty indicator :po_indicator
                FROM SDT_TRANSACTIONS_TAB SDT,
                SGL_TRANS_GROUP_LINKS_TAB SGL
                WHERE SGL.TRANS_TYP_GROUP = 'SUPP'
                AND SGL.TRANS_TYP = SDT.TRANS_TYP
                AND SDT.HOUSE_NO = :PI_HOUSE_NUMBER
                AND SDT.ISSUE_SEQ_NO = :PI_ISSUE_SEQ_NO
                AND SDT.DEL_PNT_SEQ_NO = :hdifor.del_pnt_seq_no;
    .
    .
    .

please submit a BUG report (a reproduceable example would be appreciated).

thanks,

Ian

    Reproduced and bugged.
    
    Peter