Posts

Showing posts from January, 2011

Israeli Identity Card Valiadation

Needed to check the validity of an Israeli identity card number I created this simple function CREATE OR REPLACE FUNCTION checkid (id_number IN VARCHAR2) RETURN VARCHAR2 IS fixed_number VARCHAR2 (10); digit NUMBER := 0; sum_digits NUMBER := 0; BEGIN CASE WHEN LENGTH (id_number) < 9 THEN fixed_number := LPAD (id_number, 9, '0'); WHEN LENGTH (id_number) > 9 THEN RETURN 'Too Long'; ELSE fixed_number := id_number; END CASE; FOR i IN 1 .. 9 LOOP digit := TO_NUMBER (SUBSTR (fixed_number, i, 1)) * (CASE WHEN MOD (i, 2) = 0 THEN 2 ELSE 1 END); IF LENGTH (digit) > 1 THEN digit := SUBSTR (digit, 1, 1) + SUBSTR (digit, 2, 1); END IF; DBMS_OUTPUT.put_line (i || '#'); sum_digits := sum_digits + digit; IF MOD (sum_digits, 10) = 0 THEN RETURN 'OK'; ELSE RETURN 'BAD'; END IF; END LOOP; RETURN TO_CHAR (sum_digits); END; / references: http://goo.gl/z2roI http://goo.gl/dCbS0

impdp appending data with query

Trying to retrieve lost records from a datapump backup directly to the production database using impdp syntax: impdp User/password directory=my_directory dumpfile=my_full_backup.dmp logfile=imp_lost_records.log QUERY=MY_TABLE:\"where code=1 and recid in \(2,5,8\) \" job_name=imp_lost_records INCLUDE=TABLE:\"=\'MY_TABLE\'\" CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND