Concurrent Programs Warning-Error: ERRBUFF and RETCODE

The ERRBUFF can be returned with any message.

The RETCODE can be returned with one of three values:

   0  -- Success
   1  -- Warning
   2  -- Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message 'No Employee Found'. If there is any other error occurs, it will turn RED with SQLERRM message.

----------------------------------------------------------

-- Package Specification

----------------------------------------------------------

CREATE OR REPLACE PACKAGE apps.emp_test_pkg

IS

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2;

    

END emp_test_pkg;

/

----------------------------------------------------------

-- Package Body

----------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg

IS

 

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2

   IS

      lv_emp_name   VARCHAR2(300)  DEFAULT  NULL;

    

   BEGIN

       

      SELECT (papf.first_name || ' ' || papf.last_name)

        INTO lv_emp_name

        FROM per_all_people_f  papf

       WHERE 1=1

         AND papf.employee_number = p_emp_number;

    

      RETURN (lv_emp_name);

    

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         errbuff := 'No employee found for ' || p_emp_number;

         retcode := '1';        -- warning

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

       

      WHEN OTHERS THEN

       

         errbuff := SQLERRM;

         retcode := '2';        -- error

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

 

   END emp_name;

 

END emp_test_pkg;

/

Query to validate email address in Oracle

Until Oracle comes up with an in-built functionality to validate an email address, you can use the following custom stored function. Compile it in the database and pass an email address to see if the email address is VALID, INVALID or UNKNOWN.

For example, if you pass following email addresses, the returning result will be as follow:

   aopu.mohsin@gmail.com      -  VALID

   aopu.mohsin@gmailcom       -  INVALID

   @gmail.com                 -  INVALID

   aopu@.com                  -  INVALID

   aopu.mohsin@gmail.         -  INVALID

   aopu. mohsin@gmail.com     -  INVALID

   NULL                       -  UNKNOWN

CREATE OR REPLACE FUNCTION xx_validate_email(p_email  IN  VARCHAR2)

   RETURN VARCHAR2

  

   --============================================================================

   -- Filename    :  XXCC_VALIDATE_EMAIL_FNC.sql

   ------------------------------------------------------------------------------

   -- Programmer  :  Abul Mohsin

   -- Date        :  07-Mar-2008

   -- Language    :  PL/SQL

   -- Location    :  $XXCC_TOP/install/

   -- Purpose     :  This function validates an email address using PL/SQL

   ------------------------------------------------------------------------------

   -- Description :

   --    Takes a string (p_email) as an input parameter. Through a series of

   --    validation checks, this function determines whether the email address is

   --    valid or invalid.

   --

   ------------------------------------------------------------------------------

   -- Rules:

   --    The following rules are based on the article "Characters in the local part

   --    of a mail address" by Jochen Topf, found in:

   --    <http://www.remote.org/jochen/mail/info/chars.html>

   --

   --      1) email cannot be NULL (length of NULL value is undetermined)

   --      2) email has to be minimum 5 characters long

   --      3) email requires at least one '@' and one '.' sign

   --      4) email cannot start or end with '@' sign

   --      5) email cannot end with '.' sign

   --      6) '.' sign cannot be before or after '@' sign

   --      7) double dots '..' are not permitted in an email

   --      8) invalid characters are: ` ~ ! # $ % ^ & * ( ) " | { } [ ] : ; , < > ? \ /

   --         and single quote and space characters

   --      9) domain part must consists of strings

   --     10) email cannot contain two '@' sign

   --

   -- Note:

   --    The returned value from this function is usually used against an email

   --    address, which will be (or is supposed to be) used with UNIX's "mail" command.

   --    Therefore, few more restrictions are applied to this functions.

   --

   ------------------------------------------------------------------------------

   -- Update History:

   -- Date       Updated By   Description

   ---------  -----------  ------------------------------------------------------

   -- 11-Mar-08  Jack Hirsch  Added test for double 'at' symbol in email.

   --                         Modified invalid character test to be in a single 

   --                         string.

   -- 07-Mar-08  Abul Mohsin  First version.

   --===============================================================================

IS

   v_at_pos         PLS_INTEGER;           -- position variable for at (@) sign

   v_dot_pos        PLS_INTEGER;           -- position variable for dot (.) sign

   v_length         PLS_INTEGER;           -- holds the length of email

   v_email_ch       VARCHAR2(1);           -- check character in an email

   v_result         VARCHAR2(10)  DEFAULT  'VALID';   -- result variable

BEGIN

   ----------------------------------------------------------

   -- variable assignments

   ----------------------------------------------------------

   v_at_pos  := INSTR(p_email, '@', -1);  -- position for last occurrence of '@'

   v_dot_pos := INSTR(p_email, '.', -1);  -- position for last occurrence of '.'

   v_length  := LENGTH(p_email);          -- length of entire email address

   ----------------------------------------------------------

   -- First Round Check:

   --   Rules for invalid email address

   ----------------------------------------------------------

   IF p_email IS NULL  OR   -- email cannot be null

      v_length  < 5    OR   -- email length should be at least 5 characters

      v_at_pos  = 0    OR   -- email requires at least one '@' sign

      v_dot_pos = 0    OR   -- email requires at least one '.' sign

      v_at_pos  = 1    OR   -- email cannot start with '@' sign

      v_dot_pos = v_at_pos - 1  OR   -- dot (.) sign cannot be right before at (@) sign

      v_dot_pos = v_at_pos + 1  OR   -- dot (.) sign cannot be right after at (@) sign

      v_dot_pos = v_length      OR   -- email cannot end with dot '.' sign

      v_at_pos  = v_length      OR   -- email cannot end with at '@' sign

     

      -- double dots are not permitted

      INSTR(SUBSTR(p_email, 1, (v_at_pos - 1)), '..') > 0 OR

      INSTR(SUBSTR(p_email, v_at_pos + 1), '..')      > 0 OR

     

      -- requires one '.' sign after '@' sign

      INSTR(SUBSTR(p_email, v_at_pos), '.') = 0           OR

      (INSTR(p_email, '@') > 0 AND INSTR(p_email, '@', INSTR(p_email, '@') + 1) > 0) OR

     

      -- this following code finds out if the domain part is a number or a string

      UPPER(SUBSTR(p_email, v_at_pos, v_length)) = LOWER(SUBSTR(p_email, v_at_pos, v_length))

   THEN

      v_result := 'INVALID';

   END IF;

   ----------------------------------------------------------

   -- Second Round Check:

   --   Invalid characters should not be in the email address.

   --   Validate using INSTR method

   ----------------------------------------------------------

   FOR i IN 1..v_length

   LOOP

      v_email_ch  :=  SUBSTR(p_email, i, 1);

     

      IF INSTR(' `~!#$%^&*"|(){}[]:;,<>?\/''''', v_email_ch) > 0

      THEN

         v_result := 'INVALID';

         EXIT;

      END IF;

     

   END LOOP;

  

   RETURN (v_result);

EXCEPTION

   WHEN OTHERS THEN

      RETURN 'UNKNOWN';

  

END xx_validate_email;