Check digit function

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

This Content Component encountered an error

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 on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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