Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Unanswered: sotred procedure calling another stored procedure

    Hi, I have a SP calling another SP with input and output Parameters. But the child SP doesn't pass back the Ouput Parameters to the first SP


    CREATE OR REPLACE PROCEDURE SP_SAT_PERSON_GC ()
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN
    ...
    DECLARE v_errMsg_logic VARCHAR(4096) default null;
    DECLARE v_errState_logic CHAR(5) DEFAULT '00000';
    ...
    call BDV.SP_SAT_PERSON_GC_logic ( ENTITY_ID_PAD , MEMBER_ID_PAD,v_errState_logic ,currentTSP,v_errMsg_logic ) ;
    ...
    return 0;
    END P1





    CREATE OR REPLACE PROCEDURE BDV.SP_SAT_PERSON_GC_logic ( in ElENTITYID CHAR(64 ) , in ElMEMBERID CHAR(64 ) , out v_errState CHAR(5 ) , in currentTSP timestamp , out v_errMsg VARCHAR(4096) )
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You don't seem to be able to debug your sprocs...or use DBMS_OUTPUT.PUT_LINE to find your mistakes. It's normally faster to find your own mistakes by debugging than posting on forums and waiting for replies (especially if you hide your broken code).

    Notice that your child-routine is returning a result-set (in addition to output parameters), but your parent sproc does not appear to correctly harvest that result-set (you don't show this in your post).

  3. #3
    Join Date
    Mar 2015
    Posts
    31
    Hi, I'm running it in debug mode but still don't know why it doesn't pass back the parameters. All error variables
    v_errState and v_errMsg ( it abort for duplicate ) are populated. It enter in the if v_errState <> '00000' correctly but does not pass back those values to the father SP



    CREATE OR REPLACE PROCEDURE BDV.SP_SAT_PERSON_GC_logic ( in ElENTITYID CHAR(64 ) , in ElMEMBERID CHAR(64 ) , out v_errState CHAR(5 ) , in currentTSP timestamp , out v_errMsg VARCHAR(4096) )
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL


    ...

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    get diagnostics exception 1 v_msg = MESSAGE_TEXT;


    SET v_errState = SQLSTATE;
    SET v_Sqlerr = SQLCODE;
    SET v_errMsg = SQLERRMC;

    SET v_errMsg = 'SQL error at location: '
    CONCAT v_errLabel
    CONCAT ' SQLSTATE: '
    CONCAT v_errState
    CONCAT ' MSG: '
    CONCAT v_msg;

    SET EOF = 1;
    END;

    ...

    INSERT INTO BDV.SAT_PERSON_GC (META_LDTS, META_LOADED_BY, META_SRC, PER_DATE_OF_BIRTH, PER_DECEASED_FLAG, PER_DISABILITY_CAT_CODE, PER_FIRST_INITIAL, PER_FORENAME, PER_GC_HUB_KEY, PER_GENDER_CODE, PER_MARITAL_STATUS_CODE, PER_NATIONAL_ID,PER_NATIONAL_ID_CAT_CODE, PER_PREFERRED_LANG_CODE, PER_PREFERRED_NAME, PER_SURNAME, PER_TITLE_CODE)
    VALUES (META_LDTS_BACK, META_LOADED_BY_BACK, META_SRC_BACK, PER_DATE_OF_BIRTH_BACK, PER_DECEASED_FLAG_BACK, PER_DISABILITY_CAT_CODE_BACK, PER_FIRST_INITIAL_BACK, PER_FORENAME_BACK, PER_GC_HUB_KEY_BACK, PER_GENDER_CODE_BACK, PER_MARITAL_STATUS_CODE_BACK, PER_NATIONAL_ID_BACK, PER_NATIONAL_ID_CAT_CODE_BACK, PER_PREFERRED_LANG_CODE_BACK, PER_PREFERRED_NAME_BACK, PER_SURNAME_BACK, PER_TITLE_CODE_BACK);



    if v_errState <> '00000' then
    rollback;
    return 1310;
    end if;
    ...

    P2: BEGIN

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    As previously advised, in your exception handler don't use your style of assignment to capture sqlstate,sqlcode, but instead use
    values(sqlstate,sqlcode) into v_errState, v_errCode
    and always use coalesce() around variables that might be null if you use concat....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •