41 lines
854 B
MySQL
41 lines
854 B
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for Function FNC_ROLE_VALIDATION
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE EDITIONABLE FUNCTION "ENVX_OPER"."FNC_ROLE_VALIDATION" (
|
||
|
|
pi_js_internal_profile IN CLOB,
|
||
|
|
pi_role_validation IN VARCHAR2
|
||
|
|
) RETURN BOOLEAN AS
|
||
|
|
v_role VARCHAR2(100);
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
SELECT roleName
|
||
|
|
INTO v_role
|
||
|
|
FROM
|
||
|
|
JSON_TABLE (
|
||
|
|
JSON(
|
||
|
|
pi_js_internal_profile
|
||
|
|
), '$.roles[*]'
|
||
|
|
COLUMNS (
|
||
|
|
roleId VARCHAR2 ( 100 ) PATH '$.roleId',
|
||
|
|
roleName VARCHAR2 ( 100 ) PATH '$.roleName'
|
||
|
|
)
|
||
|
|
)
|
||
|
|
j
|
||
|
|
WHERE upper(
|
||
|
|
j.roleName
|
||
|
|
) = upper(
|
||
|
|
pi_role_validation
|
||
|
|
);
|
||
|
|
|
||
|
|
RETURN true;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
RETURN false;
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN true;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/
|