Exception Handling in PLSQL

I need your help for handling the exception in my package.I dont know exactly where to write the exception. I am including the function. The problem is ACTY_COUNT is of varchar type .So if user enter some alphabets unknowingly the entire package will not work since we are the sum of that field. I need to write an exception for this so that if there is any alphabetical values entered for that field it shud handle the error for that employee while the report for others shud be shown. Thanks in advance

FUNCTION ACTY_Ld_DAT(V_STARTDATE IN DATE,V_ENDDATE IN DATE) RETURN SYS_REFCURSOR IS
  ACTY_DATA SYS_REFCURSOR;

  BEGIN
      OPEN ACTY_DATA FOR
  /*Query to fetch the report field into ref cursor*/
      SELECT
     MAIN_DATA.EMPID AS "EMP_ID",
     MAIN_DATA.FIRST_NAME AS "FIRST_NAME",
     MAIN_DATA.LAST_NAME AS "LAST_NAME",
     MAIN_DATA.LOCATION AS "LOCATION",
     MAIN_DATA.ACTY_NAME AS "ACTY_NAME",
     SUM(NVL(MAIN_DATA.ACTY_COUNT,0)) AS "ACTY_COUNT",
     SUM(MAIN_DATA.REGULAR_HRS) AS "REG_HRS",
     SUM(MAIN_DATA.OVERTIME_HRS) AS "OT_HRS",
     SUM(MAIN_DATA.TOTAL_HRS) AS "TOTAL_HRS",
     (
        CASE
           WHEN MAIN_DATA.ACTY_NAME = 'X' AND SUM(ACTY_COUNT) != 0  THEN
              (100/SUM(MAIN_DATA.TOTAL_HRS)) * ROUND((1.33 * SUM(ACTY_COUNT)),1)
           WHEN (MAIN_DATA.ACTY_NAME = 'Y' OR MAIN_DATA.ACTY_NAME = 'Z' OR MAIN_DATA.ACTY_NAME = 'U') AND SUM(ACTY_COUNT) != 0 THEN
              (100/SUM(MAIN_DATA.TOTAL_HRS)) * ROUND((5 * SUM(ACTY_COUNT)),1)
           WHEN MAIN开发者_如何学C_DATA.ACTY_NAME = 'V' AND SUM(ACTY_COUNT) != 0 THEN
              (100/SUM(MAIN_DATA.TOTAL_HRS)) * ROUND((8 * SUM(ACTY_COUNT)),1)
           ELSE
              0
        END
     ) "PROD_PERCENTAGE"
  FROM
     (
  SELECT
           DATA.EMPID,
           DATA.FIRST_NAME,
           DATA.LAST_NAME,
           DATA.LOCATION,
           DATA.ACTY_NAME,
           SUM(NVL(DATA.ACTY_COUNT,0)) AS "ACTY_COUNT",
           MIN("Regular_Hrs") AS "REGULAR_HRS",
           MIN("Overtime_Hrs") AS "OVERTIME_HRS",
           MIN("Regular_Hrs") + MIN("Overtime_Hrs") AS "TOTAL_HRS"
        FROM
        (
           SELECT
              P.PERSONID "EMPID",
              P.FIRSTNM "FIRST_NAME",
              P.LASTNM "LAST_NAME",
              LABACCT.LABORLEV1NM "LOCATION",
              ACT.ACTIVITYNM "ACTY_NAME",
              SUM(NVL(RES.REPTXT,0)) AS "ACTY_COUNT",
              MIN(SPAN.EVENTDTM) "EVENTDT",
              (
                 SELECT
                    SUM(DURATIONSECSQTY/3600)
                 FROM
                    WFCTOTAL TOT,
                    PAYCODE PCD
                 WHERE
                    P.PERSONID = TOT.EMPLOYEEID AND
                    TOT.PAYCODEID = PCD.PAYCODEID AND
                    PCD.NAME IN ('Regular 1','Regular 2','Regular 3') and
                    TOT.APPLYDTM BETWEEN TRIM(V_STARTDATE) AND TRIM(V_ENDDATE)
              ) AS "Regular_Hrs",
              (
                 SELECT
                    NVL(SUM(DURATIONSECSQTY/3600),0)
                 FROM
                    WFCTOTAL TOT,
                    PAYCODE PCD
                 WHERE
                    P.PERSONID = TOT.EMPLOYEEID AND
                    TOT.PAYCODEID = PCD.PAYCODEID AND
                    PCD.NAME IN ('Overtime 1','Overtime 2','Overtime 3') and
                    TOT.APPLYDTM BETWEEN TRIM(V_STARTDATE) AND TRIM(V_ENDDATE)
              ) AS "Overtime_Hrs"
           FROM
              PERSON P,
              WFAREPACTYSPAN SPAN,
              WFAREPLABACCT WFALABACCT,
              LABORACCT LABACCT,
              WFAREPACTYRES RES,
              WFAACTIVITY ACT
           WHERE
              P.PERSONID = SPAN.EMPLOYEEID AND
              WFALABACCT.WFAREPACTYSPANID = SPAN.WFAREPACTYSPANID AND
              LABACCT.LABORACCTID = WFALABACCT.LABORACCTID AND
              RES.WFAREPACTYSPANID(+) = SPAN.WFAREPACTYSPANID AND
              ACT.WFAACTIVITYID = SPAN.WFAACTIVITYID AND
              ACT.WFAACTIVITYID NOT IN (-1) AND
              SPAN.DELETEDSW = 0 AND
              SPAN.APPROVEDSW = 1 AND
              SPAN.EVENTDTM BETWEEN TRIM(V_STARTDATE) AND TRIM(V_ENDDATE) AND
              P.PERSONID IN  (
                                 SELECT
                                    EMPLOYEEID
                                 FROM
                                    WTKEMPLOYEE
                                 WHERE
                                    PAYRULEID IN (
                                                    SELECT
                                                       PAYRULEID
                                                    FROM
                                                       PAYRULEIDS
                                                    WHERE
                                                       NAME = 'XXXX'OR
                                                       NAME = 'YYYY'
                                                 )
                              )
           GROUP BY
              P.PERSONID,
              P.FIRSTNM,
              P.LASTNM,
              LABACCT.LABORLEV1NM,
              ACT.ACTIVITYNM,
              SPAN.EVENTDTM
        ) DATA
        GROUP BY
           DATA.EMPID,
           DATA.FIRST_NAME,
           DATA.LAST_NAME,
           DATA.LOCATION,
           DATA.ACTY_NAME
       )MAIN_DATA
     GROUP BY
        MAIN_DATA.EMPID,
        MAIN_DATA.FIRST_NAME,
        MAIN_DATA.LAST_NAME,
        MAIN_DATA.LOCATION,
        MAIN_DATA.ACTY_NAME;




     RETURN ACTY_DATA;
  END ACTY_LOAD_DATA;

You can create your own function to solve this. e.g.:

CREATE FUNCTION to_number_or_null (text IN VARCHAR2) RETURN NUMBER IS
BEGIN
  RETURN TO_NUMBER(text);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;
END to_number_or_null;

Now, change your query:

SUM(NVL(to_number_or_null(RES.REPTXT),0)) AS "ACTY_COUNT",