recently a customer had the request to export a column to a flat file. nothing special, but the request was to encrypt the column as the data needs to be delivered to a third site. as you might know oracle provides the dbms_obfuscation_toolkit package which might be used for requests like this. so, here is an example ( as wordpress does not allow to upload scripts, sorry for the long pasting :) ):
/**
Export a column for a given table and schema in encrypted format to csv
PARAMETERS: OWNER
TABLE
COLUMN
ENRYPTION-KEY
**/
SET LINES 164 VERIFY OFF PAGES 999 SERVEROUT ON FEEDBACK OFF
VARIABLE owner VARCHAR2(30)
VARIABLE table_name VARCHAR2(30)
VARIABLE column_name VARCHAR2(30)
VARIABLE encryption_key VARCHAR2(100)
VARIABLE continue VARCHAR2(1);
EXEC dbms_output.put_line ( chr(13) );
EXEC dbms_output.put_line ( '------------------------------------------------------' );
EXEC dbms_output.put_line ( '-- INFO: Starting encrypted column export ');
EXEC dbms_output.put_line ( chr(13) );
/**
do some sanity checks
**/
DECLARE
lv_owner all_users.username%TYPE;
lv_directory all_directories.directory_path%TYPE;
FUNCTION schema_exists ( pv_schema IN all_users.username%TYPE )
RETURN BOOLEAN
IS
CURSOR cur_schema
IS SELECT 'ok'
FROM all_users
WHERE username = pv_schema
;
lv_cursor_result VARCHAR2(2);
BEGIN
OPEN cur_schema;
FETCH cur_schema INTO lv_cursor_result;
CLOSE cur_schema;
RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
END schema_exists;
FUNCTION table_exists ( pv_schema IN all_users.username%TYPE
, pv_table IN all_tables.table_name%TYPE
) RETURN BOOLEAN
IS
CURSOR cur_table
IS SELECT 'ok'
FROM all_tables
WHERE owner = pv_schema
AND table_name = pv_table
;
lv_cursor_result VARCHAR2(2);
BEGIN
OPEN cur_table;
FETCH cur_table INTO lv_cursor_result;
CLOSE cur_table;
RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
END table_exists;
FUNCTION column_exists ( pv_schema IN all_users.username%TYPE
, pv_table IN all_tables.table_name%TYPE
, pv_column IN all_tab_columns.column_name%TYPE
) RETURN BOOLEAN
IS
CURSOR cur_column
IS SELECT 'ok'
FROM all_tab_columns
WHERE owner = pv_schema
AND table_name = pv_table
AND column_name = pv_column
;
lv_cursor_result VARCHAR2(2);
BEGIN
OPEN cur_column;
FETCH cur_column INTO lv_cursor_result;
CLOSE cur_column;
RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
END column_exists;
FUNCTION exp_directory_exists RETURN BOOLEAN
IS
CURSOR cur_directory
IS SELECT directory_path
FROM all_directories
WHERE directory_name = 'DATA_PUMP_DIR'
;
BEGIN
OPEN cur_directory;
FETCH cur_directory INTO lv_directory;
CLOSE cur_directory;
RETURN lv_directory IS NOT NULL;
END exp_directory_exists;
BEGIN
:owner := UPPER('&1');
:table_name := UPPER('&2');
:column_name := UPPER('&3');
:encryption_key := '&4';
IF :encryption_key IS NULL
OR
-- encryption key must be at least 80 characters
length ( :encryption_key ) < 80
THEN
raise_application_error ( -20001
, '--- CHECK INPUT: The encryption key "'||:encryption_key||'" is too short or empty ! ');
:continue := 'N';
END IF;
IF NOT ( schema_exists ( pv_schema => :owner ) )
THEN
raise_application_error ( -20001
, '--- CHECK INPUT: The schema '||:owner||' does not exist in this database ! ');
:continue := 'N';
END IF;
IF NOT ( table_exists ( pv_schema => :owner
, pv_table => :table_name
) )
THEN
raise_application_error ( -20001
, '--- CHECK INPUT: The table '||:table_name||' does not exist for the schema '||:owner||' ! ');
:continue := 'N';
END IF;
IF NOT ( column_exists ( pv_schema => :owner
, pv_table => :table_name
, pv_column => :column_name
) )
THEN
raise_application_error ( -20001
, '--- CHECK INPUT: The column '||:column_name||' does not exist for the table '||:table_name||' in schema '||:owner||'! ');
:continue := 'N';
END IF;
IF NOT ( exp_directory_exists )
THEN
raise_application_error ( -20001
, '--- CHECK DIRECTORY: Can not find directory DATA_PUMP_DIR ! ');
:continue := 'N';
ELSE
dbms_output.put_line (' --- INFO: File will be located here : '||lv_directory );
END IF;
:continue := 'Y';
END;
/
DECLARE
lv_owner all_users.username%TYPE := UPPER(:owner);
lv_table all_tables.table_name%TYPE := UPPER(:table_name);
lv_column all_tab_columns.column_name%TYPE := UPPER(:column_name);
lt_file_type utl_file.file_type;
lv_statement VARCHAR2(4000);
ln_cursor NUMBER;
ln_amount_of_rows INTEGER := 0;
lv_value VARCHAR2(4000);
lv_seed VARCHAR2(4000) := :encryption_key;
BEGIN
IF ( :continue = 'Y' )
THEN
dbms_output.put_line (' --- INFO: selected schema is : '||lv_owner );
dbms_output.put_line (' --- INFO: selected table is : '||lv_table );
dbms_output.put_line (' --- INFO: selected column is : '||lv_column );
dbms_output.put_line (' --- INFO: exporting to : DATA_PUMP_DIR ');
-- open file for writing
lt_file_type := utl_file.fopen ( location => 'DATA_PUMP_DIR'
, filename => 'exp_'||lv_owner||'_'||lv_table||'_'||lv_column||'_'
||TO_CHAR(sysdate,'DD.MM.YYYY HH24:MI:SS')||'.csv'
, open_mode => 'w'
);
lv_statement := 'SELECT '||lv_column||' FROM '||lv_owner||'.'||lv_table;
dbms_output.put_line (' --- INFO: statement is: '||lv_statement );
-- prepare statement
ln_cursor := dbms_sql.open_cursor;
dbms_sql.parse ( c => ln_cursor
, statement => lv_statement
, language_flag => dbms_sql.v7
);
dbms_sql.define_column ( c => ln_cursor
, position => 1
, column => lv_column
, column_size => 4000
);
ln_amount_of_rows := dbms_sql.execute ( c => ln_cursor );
LOOP
-- fetch all the rows
IF dbms_sql.fetch_rows ( c => ln_cursor ) = 0
THEN
EXIT;
ELSE
ln_amount_of_rows := ln_amount_of_rows + 1;
-- get the column value
dbms_sql.column_value ( c => ln_cursor
, position => 1
, value => lv_value
);
-- input must be a multiple of 8 bytes, so rpadding with blanks
dbms_obfuscation_toolkit.desencrypt ( input_string => rpad ( lv_value
, ( trunc ( length ( lv_value ) / 8 ) + 1 ) * 8
, chr(0)
)
, key_string => lv_seed
, encrypted_string => lv_value
);
-- write to output file
utl_file.put_line ( file => lt_file_type
, buffer => utl_raw.cast_to_raw ( lv_value )
);
END IF;
END LOOP;
-- close cursor
dbms_sql.close_cursor ( c => ln_cursor );
-- close file handle
utl_file.fclose ( file => lt_file_type );
dbms_output.put_line (' --- Exported '||ln_amount_of_rows||' rows !!! ');
END IF;
END;
/
for decrypting one could use this snippet:
/**
Decrypt the encrypted data in the exported csv files
**/
SET LINES 164 VERIFY OFF PAGES 999 SERVEROUT ON FEEDBACK OFF
VARIABLE filename VARCHAR2(4000)
VARIABLE encryption_key VARCHAR2(100)
VARIABLE continue VARCHAR2(1);
EXEC dbms_output.put_line ( chr(13) );
EXEC dbms_output.put_line ( '------------------------------------------------------' );
EXEC dbms_output.put_line ( '-- INFO: Starting decryption ');
EXEC dbms_output.put_line ( chr(13) );
/**
do some sanity checks
**/
DECLARE
lv_directory all_directories.directory_path%TYPE;
FUNCTION exp_directory_exists RETURN BOOLEAN
IS
CURSOR cur_directory
IS SELECT directory_path
FROM all_directories
WHERE directory_name = 'DATA_PUMP_DIR'
;
BEGIN
OPEN cur_directory;
FETCH cur_directory INTO lv_directory;
CLOSE cur_directory;
RETURN lv_directory IS NOT NULL;
END exp_directory_exists;
BEGIN
:filename := '&1';
:encryption_key := '&2';
IF ( length ( :encryption_key ) < 80 )
THEN
raise_application_error ( -20001
, '--- CHECK INPUT: The encryption key "'||:encryption_key||'" is too short or empty ! ');
:continue := 'N';
END IF;
IF NOT ( exp_directory_exists )
THEN
raise_application_error ( -20001
, '--- CHECK DIRECTORY: Can not find directory DATA_PUMP_DIR ! ');
:continue := 'N';
ELSE
dbms_output.put_line (' --- INFO: File to be loaded : '||lv_directory );
END IF;
:continue := 'Y';
END;
/
DECLARE
lt_file_type utl_file.file_type;
lv_buffer VARCHAR2(4000);
lv_buffer_raw RAW(4000);
lraw_decrypted_value RAW(4000);
lv_value RAW(32767);
BEGIN
IF ( :continue = 'Y' )
THEN
-- open file handle
lt_file_type := utl_file.fopen ( location => 'DATA_PUMP_DIR'
, filename => :filename
, open_mode => 'r'
-- , max_linesize => 4000
);
-- read through the file line by line
LOOP
BEGIN
utl_file.get_line ( file => lt_file_type
, buffer => lv_buffer
);
lv_buffer_raw := lv_buffer;
dbms_obfuscation_toolkit.desdecrypt ( input => lv_buffer_raw
, key => utl_raw.cast_to_raw ( :encryption_key )
, decrypted_data => lraw_decrypted_value
);
dbms_output.put_line ( 'encrypted value: '||utl_raw.cast_to_varchar2(lraw_decrypted_value) );
EXCEPTION
WHEN no_data_found THEN EXIT;
END;
END LOOP;
-- close file handle
utl_file.fclose ( file => lt_file_type );
ELSE
NULL;
END IF;
END;
/
for encrypting and decrypting two simple calls now do the trick:
# to encrypt: @encrypted_column_export.sql USER TABLE_NAME COLUMN_NAME ENCRYPTION_KEY # to decrypt @decrypt_exported_csv.sql FILE_NAME ENCRYPTION_KEY
Daniel Westermann's Blog 