Rem drv: Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_fmtlib_pkgdef.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $ Rem Rem dm_fmtlib_pkgdef.sql Rem Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dm_fmtlib_pkgdef.sql - Data Masking format library. Rem Rem DESCRIPTION Rem This file creates a package that contains UDFs and PPFs used in Rem Defining common data masking formats. Rem Ex: Functions to generate valid credit card numbers adhering to Rem Luhn check. Rem This file in conjunction with 'dm_fmtlib.xml' forms the data masking Rem library. Rem Rem NOTES Rem Most of the 'user defined functions' defined in this package are Rem mainly for illustration purposes. Some of these functions needed Rem to be customized further to suit individual customer specific needs. Rem Rem MODIFIED (MM/DD/YY) Rem bkuchibh 11/04/08 - cleanup Rem kmckeen 10/29/08 - Updated for 10.2.0.5 Rem bkuchibh 01/09/08 - Created Rem CREATE OR REPLACE PACKAGE DBSNMP.DM_FMTLIB authid current_user IS ------------------------------------------------------------------------------- -- Data Masking Format Library Functions -- Which will be used as either UDFs or Post UDF in masking format definitions ------------------------------------------------------------------------------- -- Generate Master card -- Most of these functions come in three flavours -- 1) standard credit card with out any formatting -- 2) space ' ' as the format -- 3) hyphen '-' as the format function mgmt_dm_gen_mc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Master card with ' ' (space) as format as space function mgmt_dm_gen_mc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Master card with format as hyphen ('-') function mgmt_dm_gen_mc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate VISA card function mgmt_dm_gen_vc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_vc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_vc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate AMEX card function mgmt_dm_gen_ac(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ac_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ac_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate DISCOVER card function mgmt_dm_gen_disc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_disc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_disc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate DINER card function mgmt_dm_gen_dinc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_dinc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_dinc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate ANY card function mgmt_dm_gen_anyc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_anyc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_anyc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------US SOCIAL SECURITY NUMBERS---------------------------------------- ---------------------------------------------------------------------------------------- DM_SSN_FORMAT varchar2(4) := NULL; function mgmt_dm_gen_ssn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- With hyphen format function mgmt_dm_gen_ssn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------ISBN NUMBERS------------------------------------------------------ ---------------------------------------------------------------------------------------- DM_ISBN_FORMAT varchar2(4) := NULL; function mgmt_dm_gen_isbn10(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_isbn10_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_isbn13(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_isbn13_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------UPC NUMBERS- ----------------------------------------------------- ---------------------------------------------------------------------------------------- DM_UPC_FORMAT varchar2(4) := NULL; function mgmt_dm_gen_upc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_upc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------CANADA SOCIAL INSURANCE NUMBERS----------------------------------- ---------------------------------------------------------------------------------------- DM_CN_SIN_FORMAT varchar2(4) := NULL; function mgmt_dm_gen_cn_sin(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_cn_sin_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_cn_sin_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------UK NATIONAlL INSURANCE NUMBERS------------------------------------ ---------------------------------------------------------------------------------------- function mgmt_dm_gen_uk_nin_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; ---------------------------------------------------------------------------------------- ----------------------PHONE NUMBER NUMBER GENERATION------------------------------------ ---------------------------------------------------------------------------------------- -- All these functions are non-deterministic. -- Generate North America phone numbers DM_PH_FORMAT varchar2(4) := NULL; function mgmt_dm_gen_ph_na(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate North America phone numbers with '-' format function mgmt_dm_gen_ph_na_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate Canada phone numbers function mgmt_dm_gen_ph_canada(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_canada_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- Generate USA phone numbers function mgmt_dm_gen_ph_usa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; -- USA states -- Just add '_XX', where XX is the state code, to th usa function signature function mgmt_dm_gen_ph_usa_al(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_az(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ar(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ca(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_co(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ct(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_de(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_dc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_fl(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ga(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_hi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_id(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_il(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_in(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ia(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ks(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ky(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_la(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_me(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_md(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ma(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ms(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mo(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mt(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ne(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nv(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nj(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nm(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ny(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nd(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_oh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ok(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_or(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_pa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ri(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_sc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_sd(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_tn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_tx(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ut(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_vt(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wv(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wy(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; --Formatted function mgmt_dm_gen_ph_usa_al_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_az_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ar_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ca_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_co_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ct_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_de_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_dc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_fl_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ga_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_hi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_id_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_il_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_in_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ia_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ks_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ky_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_la_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_me_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_md_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ma_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ms_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mo_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_mt_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ne_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nv_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nh_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nj_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nm_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ny_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_nd_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_oh_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ok_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_or_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_pa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ri_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_sc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_sd_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_tn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_tx_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_ut_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_vt_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wv_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; function mgmt_dm_gen_ph_usa_wy_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2; END DM_FMTLIB; / SHOW ERRORS;