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;handle the execption, such as...

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.|||Hi,

Use %FOUND or %NOTFOUND or %ROWCOUNT .|||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;