DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Ref_cursor issue

  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Ref_cursor issue

    First off, bear with me since I'm a newbie..

    I have a function that returns a ref_cursor... I need (within the function) to check if the ref_cirsor is empty, and if it is, call another function to populate the ref_cursor. basically if ithe data isn't in the database, it's gonna be in the archieve database, which has the same table/layouts.

    Here's a snipet of code... Any help would be appreciated.

    FUNCTION Get_summary_by_policy_number(N_POLICY_IN IN policy.N_POLICY%TYPE)
    RETURN REF_CURSOR
    AS
    claim_summary_cursor ref_cursor;
    BEGIN

    open claim_summary_cursor for
    SELECT CLAIM.N_CLAIM_NUMBER, CLAIM.C_CLAIM_STATUS,
    POLICY.N_POLICY,
    LOSS.D_DATE,LOSS.T_FNOL_LOSS_DESC, LOSS.C_LOSS_TYPE,
    CN.M_FIRST_NAME, CN.M_MIDDLE_NAME, CN.M_LAST_NAME
    FROM
    claim CLAIM,
    policy POLICY,
    loss LOSS,
    client_name CN,
    ins_involvement II,
    involvement_role IR
    WHERE
    CLAIM.N_POLICY_ID = POLICY.N_POLICY_ID AND
    CLAIM.N_LOSS_ID = LOSS.N_LOSS_ID AND
    CLAIM.N_CLAIM_ID = II.N_ENTITY_ID AND
    II.N_INS_INVL_ID = IR.N_INS_INVL_ID AND
    II.N_PREF_NAME_ID = CN.N_NAME_ID (+) AND
    CLAIM.C_CLAIM_STATUS <> 'ER' AND
    CLAIM.C_RCD_DEL <> 'Y' AND
    CLAIM.C_CLAIM_FILE_TYPE NOT IN ('E','CN') AND
    LOSS.C_RCD_DEL <> 'Y' AND
    POLICY.C_RCD_DEL <> 'Y' AND
    II.C_RCD_DEL <> 'Y' AND
    IR.C_RCD_DEL <> 'Y' AND
    IR.C_STATUS <> 'ER' AND
    IR.C_INVL_ROLE = 'NIS' AND
    POLICY.N_POLICY = RPAD(N_POLICY_IN,12)
    ORDER BY D_DATE DESC;

    IF*claim_summary_cursor%FOUND THEN
    claim_summary_cursor := claiminq_proc.arch_claim_inquiry.Get_summary_by_policy_number(N_POLICY_IN) ;
    End If;



    return claim_summary_cursor;


    END;

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool %isopen

    The open cursor statement will only return "%ISOPEN"{=TRUE} variable after executing. It will NOT return any other of the cursor variables (like %ROWCOUNT).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    okay..

    If I use "%ISOPEN"{=TRUE} after I open the cursor, and it's empty ( no rows selected) would %ISOPEN still return tru regardless if rows were selected?


    And thanks for your reply

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Bummer

    Quote Originally Posted by kmkfmly
    okay..

    If I use "%ISOPEN"{=TRUE} after I open the cursor, and it's empty ( no rows selected) would %ISOPEN still return tru regardless if rows were selected?

    And thanks for your reply
    YES, it will return TRUE regardless if rows selected or not.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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


Click Here to Expand Forum to Full Width