Select Decryption UDFs

The UDFs in this section decrypt the encrypted data. Unprotect access is required to use these procedures.

pty.sel_decrypt

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt('DE_AES256', PTY.ins_encrypt('DE_AES256', 'Original data', 0),0) "Test of SELECT dec func" from dual;

pty.sel_decrypt_char

This UDF decrypts the CHAR data with an encryption data element.

Signature:

pty.sel_decrypt_char(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_char('AES256', PTY.ins_encrypt_char('AES256', 'Original data', 0),0) "Test of SELECT dec CHAR func" from dual; 

pty.sel_decrypt_varchar2

This UDF decrypts the VARCHAR2 data with an encryption data element.

Signature:

pty.sel_decrypt_varchar2(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_varchar2('AES256', PTY.ins_encrypt_varchar2('AES256','Original data', 0),0) "Test of SELECT dec VARCHAR2 func" from dual;

pty.sel_decrypt_date

This UDF decrypts the DATE data with an encryption data element.

Signature:

pty.sel_decrypt_date(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the DATE datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_date('DE_AES256', PTY.ins_encrypt_date('DE_AES256', '23-OCT-14', 0),0) "Test of SELECT dec DATE func" from dual;

pty.sel_decrypt_integer

This UDF decrypts the INTEGER data with an encryption data element.

Signature:

pty.sel_decrypt_integer(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_integer('DE_AES256', PTY.ins_encrypt_integer('DE_AES256', 12345, 0),0) "Test of SELECT dec INT func" from dual;

pty.sel_decrypt_real

This UDF decrypts the REAL data with an encryption data element.

Signature:

pty.sel_decrypt_real(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the REAL datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_real('AES256', PTY.ins_encrypt_real('AES256',1234.1234,0),0) “Test of SELECT dec REAL func” from dual;

pty.sel_decrypt_float

This UDF decrypts the FLOAT data with an encryption data element.

Signature:

pty.sel_decrypt_float(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the FLOAT datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_float('DE_AES256', PTY.ins_encrypt_float('DE_AES256', 1234.1234, 0),0) "Test of SELECT dec FLOAT func" from dual;

pty.sel_decrypt_number

This UDF decrypts the NUMBER data with an encryption data element.

Signature:

pty.sel_decrypt_number(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_number('DE_AES256', PTY.ins_encrypt_number('DE_AES256', 12345, 0),0) "Test of SELECT dec NUMBER func" from dual;

pty.sel_decrypt_raw

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the RAW data.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_raw('AES256', PTY.ins_encrypt_raw('AES256', 'FFDD12345', 0),0) "Test of SELECT dec RAW func" from dual;