Select No-Encryption, Token, and FPE UDFs

These UDFs unprotect the protected data. Unprotect access is required to use these UDFs.

pty.sel_char

This UDF unprotects the CHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_char(dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_char('DE_DTP2_AES256_AN', PTY.ins_char('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT CHAR func" from dual;

pty.sel_varchar2

This UDF unprotects the VARCHAR2 data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_varchar2('DE_DTP2_AES256_AN', PTY.ins_varchar2('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT VARCHAR2 func" from dual;

pty.sel_unicodenvarchar2

This UDF unprotects the protected NVARCHAR data.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2(dataelement CHAR, inval NVARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NVARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example:

select pty.sel_unicodenvarchar2('fpe_unicode', PTY.ins_unicodenvarchar2('fpe_unicode', 'Original data', 0),0) "Test of SELECT NVARCHAR2 func" from dual;

pty.sel_unicodevarchar2_tok

This UDF unprotects the VARCHAR2 data protected by a Unicode Base64 and Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodevarchar2_tok(dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as VARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Base64:

select pty.sel_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', pty.ins_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', 'Protegrity123',0),0) from dual;

Example for Unicode Gen2:
select pty.sel_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual; select pty.sel_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;

pty.sel_unicodenvarchar2_tok

This UDF unprotects the NVARCHAR2 data protected by a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as NVARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:
select pty.sel_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual;

```
select 
pty.sel_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;
```

pty.sel_date

This UDF unprotects the DATE data with a No Encryption data element.

Signature:

pty.sel_date(dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:

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

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

Example:

select PTY.sel_date('DE_NoEnc', PTY.ins_date('DE_NoEnc', '23-OCT-14', 0),0) "Test of SELECT DATE func" from dual;

pty.sel_integer

This UDF unprotects the INTEGER data with tokenization and No Encryption data elements.

Signature:

pty.sel_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_integer('Integer4',PTY.ins_integer('integer',12344567,0),0) “Test of SELECT INT func” from dual;

pty.sel_real

This UDF unprotects the REAL data with a No Encryption data element.

Signature:

pty.sel_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:

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

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_real('DE_NoEnc', PTY.ins_real('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT REAL func" from dual;

pty.sel_float

This UDF unprotects the FLOAT data with a No Encryption data element.

Signature:

pty.sel_float(dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:

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

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_float('DE_NoEnc', PTY.ins_float('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT FLOAT func" from dual; 

pty.sel_number

This UDF unprotects the NUMBER data with tokenization and No Encryption data elements.

Signature:

pty.sel_number(dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_number('DE_Integer', PTY.ins_number('DE_Integer', 123455667, 0),0) "Test of SELECT NUMBER func" from dual; 

pty.sel_raw

This UDF unprotects the RAW data with a No Encryption data element.

Signature:

pty.sel_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 NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_raw('DE_NoEnc', PTY.ins_raw('DE_NoEnc', 'FFDD12345', 0),0) "Test of SELECT RAW func" from dual;