Check digit function

Have a check-digit in id-columns, especially if values are entered manually.

It is a good idea to have a check digit in ID columns, especially if values are entered manually. Here is a check...

function to use:

CREATE OR REPLACE  FUNCTION add_check_digit( in_val IN NUMBER ) RETURN NUMBER AS
   c_in_val VARCHAR2(100);
   c_sum  NUMBER := 0; 
   i NUMBER;
   BEGIN
      c_in_val := TO_CHAR( in_val );                
      i := LENGTH( c_in_val );
      WHILE i > 0 LOOP
         c_sum := c_sum + TO_NUMBER( SUBSTR( c_in_val, i, 1 ) );
         i := i - 1;
      END LOOP; 
   RETURN TO_NUMBER( c_in_val || MOD( c_sum, 10 ) );
END add_check_digit;
/
SHOW ERRORS

Then create the table:

CREATE TABLE a_table ( id_col number(10) );  

Here is a trigger to enforce the rule, since Oracle doesn't allow self-defined functions in check-constraints:

CREATE OR REPLACE TRIGGER ar_iu_a_table 
     AFTER INSERT 
        OR UPDATE OF id_col
     ON a_table 
     FOR EACH ROW 
BEGIN
  IF :new.id_col != add_check_digit( TRUNC( :new.id_col / 10 ) ) THEN
    RAISE_APPLICATION_ERROR( -20000, 'id_col failed check-digit-test !' );
  END IF;
END;
/
SHOW ERRORS

Finally, here is how to use the function for generating new datasets:

INSERT INTO a_table ( id_col) VALUES ( add_check_digit( your_id_sequence.nextval() ) )

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in May 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close