I am trying to work on setting value to a hidden item that is running at the background of the APEX page using PL\SQL expression as there is some checking need to be done. But somehow I am having some 'PLS-00103' Encountered the symbol "Declare".... error when compile.I would like to seek for help on how to solve this. Thank you

enter image description here

DECLARE
    p_batch_id_stg      NUMBER;
    p_batch_id_fail     NUMBER;
    p_final_batch_id    NUMBER;

BEGIN
        SELECT 
        BATCH_ID INTO  p_batch_id_stg
        FROM 
        NIOE_ORDER_HEADER_STG_V
        WHERE 
        BATCH_ID !='' OR BATCH_ID IS NOT NULL
        ORDER BY 
        BATCH_ID DESC
        FETCH FIRST 1 ROWS ONLY;
    
        SELECT 
        BATCH_ID INTO  p_batch_id_fail
        FROM 
        NI.NI_OM_ORDER_IMPORT
        WHERE 
        BATCH_ID !='' OR BATCH_ID IS NOT NULL
        ORDER BY 
        BATCH_ID DESC
        FETCH FIRST 1 ROWS ONLY;
    
    IF p_batch_id_fail > p_batch_id_stg THEN
        p_final_batch_id := p_batch_id_fail;
    ELSIF p_batch_id_stg > p_batch_id_fail THEN
        p_final_batch_id := p_batch_id_stg;
    END IF;

    RETURN p_final_batch_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

CodePudding user response：

... using PL\SQL expression

Well, this is not a PL/SQL expression. It is written in PL/SQL, but it represents a Function Body that returns some value.

It is not clear where exactly you're doing this (is it that item's "Source" or "Default" value?), but - nonetheless, I'd suggest you to change type to "Function Body".