Bulk UDFs

Bulk User-Defined Functions (UDFs) in Oracle are designed to process multiple rows in a single call, rather than operating on one row at a time like scalar UDFs. They are typically used for batch operations such as tokenization, encryption, or transformation of large datasets. In Oracle v10.0.0, bulk UDFs are implemented to improve efficiency when working with large tables or columns containing sensitive data.

The features of the bulk UDFs are listed below.

• Accept table name, source column(s), and data element name as arguments.
• Read multiple records, prepare batches, and process them collectively.
• Return results for all rows in one execution cycle.

The advantages of bulk UDFs over scalar UDFs are listed below.

Note: When ‘NULL’ is passed as a column name, it will be treated a standard SQL term and be processed appropriately. For example, the following query will return NULL under the result column.

select * from pty.ins_varchar2_bulk('tbl_tok_varchar_bulk_positive','NULL','cid','TE_A_S13_L0R0_ASTYES',NULL,0);

Note: In case of an error in executing the bulk UDFs, it is observed that failed queries return the audit log count based on the internal batch size. The range for the batch size ranges from a minimum of 1 to a maximum of 1000 entries.

Note: The source and primary key column names in the tables will be processed and executed as per SQL’s standard behavior.

pty.ins_encrypt_varchar2_bulk

This function is used to encrypt a column of VARCHAR2 data in bulk, returning a table of results with the primary key and encrypted value.

Note: The column_name data must be in the varchar format.

Signature:

pty.ins_encrypt_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:
This UDF returns a pipelined table of type raw_4000_table, where each row contains the primary key and the encrypted value for the column. If the input data is null or empty, the output will be NULL.

Example:

SELECT * FROM TABLE(
  pty.ins_encrypt_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'ID',
    'AES256',
    'WHERE status = ''ACTIVE''',
    0
  )
);

pty.sel_decrypt_varchar2_bulk

This function is used to decrypt a column of RAW (encrypted VARCHAR2) data in bulk, returning a table of results with the primary key and decrypted value.

Note: The source column data must be in the RAW format.

Signature:

pty.sel_decrypt_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:

This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the decrypted value for the column. If the input data is null or empty, the output will be NULL.

Example:

SELECT * FROM TABLE(
  pty.sel_decrypt_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'ID',
    'AES256',
    'WHERE status = ''ACTIVE''',
    0
  )
);

pty.ins_varchar2_bulk

This function is used to tokenize (protect) a column of VARCHAR2 data in bulk, returning a table of results with primary key and tokenized value.

Note: The column_name data must be in the varchar format.

Signature:

pty.ins_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:
This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the tokenized value for the column. If the input data is null or empty, the output will NULL.

Example:

SELECT * FROM TABLE(
  pty.ins_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'id',
    'TE_A_S13_L1R2_Y',
    'WHERE status = ''ACTIVE''',
    0
  )
);

Example of table to table insert with Bulk UDF:

insert into <target_table>(col1,col2,col3,col4,col5) 
select p.pk_value,e.col2,e.col3,e.col4,p.result
from <source_table> e join table(pty.ins_varchar2_bulk('<source_table>','col5','col1','de_TokName',NULL,0))
on e.col1 = p.pk_value; 

pty.sel_varchar2_bulk

This function is used to detokenize (unprotect) a column of VARCHAR2 data in bulk, returning a table of results with primary key and detokenized value.

Note: The column_name data must be in the VARCHAR2 format.

Signature:

pty.sel_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:

This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the detokenized value for the column. If the input data is null or empty, the output will NULL.

Example:

SELECT * FROM TABLE(
  pty.sel_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'id',
    'TE_A_S13_L1R2_Y',
    'WHERE status = ''ACTIVE''',
    0
  )
);