Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: check if recordset is empy

    Hello,

    Using Oracle 8.1.7

    In the following package how do I determine whether a value for pROW_ID is returned or not (within the package)?

    Merry X-mas,
    Ronan van Riet

    CREATE OR REPLACE PACKAGE VALIDATE_USER_PKG
    AS
    PROCEDURE VALIDATEUSER
    ( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
    pPWD in S_CONTACT.PWD%TYPE,
    pROW_ID out S_CONTACT.ROW_ID%TYPE,
    pFST_NAME out S_CONTACT.FST_NAME%TYPE,
    pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
    pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
    );
    END VALIDATE_USER_PKG;


    /
    CREATE OR REPLACE PACKAGE BODY VALIDATE_USER_PKG
    AS
    PROCEDURE VALIDATEUSER
    ( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
    pPWD in S_CONTACT.PWD%TYPE,
    pROW_ID out S_CONTACT.ROW_ID%TYPE,
    pFST_NAME out S_CONTACT.FST_NAME%TYPE,
    pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
    pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
    )
    IS

    -- Purpose: Authenticate user with Oracle DB

    -- MODIFICATION HISTORY
    -- --------- ------ -------------------------------------------
    -- RVR 03-MAY-2003 Created

    BEGIN
    SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
    INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
    FROM S_CONTACT, S_ORG_EXT
    WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
    AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
    AND S_CONTACT.PWD = pPWD;

    --EXCEPTION
    -- WHEN exception_name THEN
    -- statements ;
    END; -- VALIDATEUSER

    END VALIDATE_USER_pkg;

  2. #2
    Join Date
    Aug 2003
    Posts
    40
    handle the execption, such as...

    PHP Code:
    BEGIN
      your PL
    /SQL statement

      EXCEPTION
        WHEN OTHERS THEN
          pRowId 
    := NULL;
    END;

    IF 
    pRowId IS NULL THEN
      trapTheError
    ();
    END IF; 
    Where "trapThe Error()" is the built-in that handles the exception,
    or simply handle it thru the exception. Your preference.
    Last edited by olerag; 12-26-03 at 00:25.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Use %FOUND or %NOTFOUND or %ROWCOUNT .
    SATHISH .

  4. #4
    Join Date
    Dec 2003
    Posts
    74

    Re: check if recordset is empy

    Originally posted by ronanvanriet
    SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
    INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
    FROM S_CONTACT, S_ORG_EXT
    WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
    AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
    AND S_CONTACT.PWD = pPWD;
    you should not select directly into variables

    use a cursor and
    EXIT WHEN cursor_name%NOTFOUND;
    Last edited by edwinjames; 01-06-04 at 09:23.

Posting Permissions

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