HomeContact

Oracle APEX API With JWT Token

By Shady Nagy
Published in Oracle
July 21, 2021
1 min read
Oracle APEX API With JWT Token

Oracle APEX Introduction

Oracle Application Express (APEX) is a low-code development platform that enables you to build scalable, secure enterprise apps, with world-class features, that can be deployed anywhere. Using APEX, developers can quickly develop and deploy compelling apps that solve real problems and provide immediate value. You won’t need to be an expert in a vast array of technologies to deliver sophisticated solutions. Focus on solving the problem and let APEX take care of the rest.

Create Login Endpoint

We have to create login endpoint which will be POST and contains username and password. It will check if the user exist in database and check the password then create the JWT token which will contain the expired pariod and claims (User Id, Job, …). Then send error message if not success or send success message with token. Endpoint code.

DECLARE
V_TOKEN VARCHAR2 (3000);
V_IS_USER_EXIST NUMBER;
V_USER_NO NUMBER;
V_USER_JOB VARCHAR2 (3000);
BEGIN
SELECT COUNT (*)
INTO V_IS_USER_EXIST
FROM emp
WHERE LOWER(ENAME) = LOWER(:userName);
IF V_IS_USER_EXIST <= 0
THEN
APEX_JSON.open_object;
APEX_JSON.write ('message', 'Error');
APEX_JSON.close_object;
RETURN;
END IF;
SELECT EMPNO, JOB
INTO V_USER_NO, V_USER_JOB
FROM emp
WHERE LOWER(ename) = LOWER(:userName);
V_Token :=
apex_jwt.encode (
p_iss => 'Example Issuer',
p_sub => 'Example User',
p_aud => 'Example JWT Recipient',
p_nbf_ts => NULL,
p_iat_ts => SYSDATE,
p_exp_sec => 60 * 5,
p_jti => NULL,
p_other_claims =>
'"id": '
|| apex_json.stringify (V_USER_NO)
|| ',"job": '
|| apex_json.stringify (V_USER_JOB),
p_signature_key =>
sys.UTL_RAW.cast_to_raw (
'ikjsdjv89j9j23hoakdjHGHg788*^%^75sdhsddgfhghfgh'));
APEX_JSON.open_object;
APEX_JSON.write ('data', V_Token);
APEX_JSON.write ('message', 'Success');
APEX_JSON.close_object;
END;

login-endpoint
login-endpoint

Create Employees Endpoint

This endpoint should be secured so Authentication header did not sent while calling the enpoint it should return Unauthorized (401). We will change the status code to be 401 by set header parametar X-APEX-STATUS-CODE.
Name | Bind Variable | Access Method | Source Type | Data Type --- | --- | --- | --- | --- Authorization | Authorization | IN | HTTP Header | String X-APEX-STATUS-CODE | status | OUT | HTTP Header | String

DECLARE
employees_cursor SYS_REFCURSOR;
l_token apex_jwt.t_token;
V_IS_USER_EXIST NUMBER;
V_ID NUMBER;
USER_OBJECT apex_json.t_values;
BEGIN
IF :Authorization IS NULL
THEN
APEX_JSON.open_object;
APEX_JSON.write ('message', 'Error');
APEX_JSON.close_object;
:status := 401;
RETURN;
END IF;
l_token :=
apex_jwt.DECODE (
p_value => :Authorization,
p_signature_key =>
sys.UTL_RAW.cast_to_raw (
'ikjsdjv89j9j23hoakdjHGHg788*^%^75sdhsddgfhghfgh'));
apex_jwt.VALIDATE (p_token => l_token,
p_iss => 'Example Issuer',
p_aud => 'Example JWT Recipient',
p_leeway_seconds => 0);
apex_json.parse (USER_OBJECT, l_token.payload);
IF NOT apex_json.does_exist (p_path => 'id', p_values => USER_OBJECT)
THEN
APEX_JSON.open_object;
APEX_JSON.write ('message', 'Error');
APEX_JSON.close_object;
:status := 401;
RETURN;
END IF;
V_ID := apex_json.GET_NUMBER (p_path => 'id', p_values => USER_OBJECT);
SELECT COUNT (*)
INTO V_IS_USER_EXIST
FROM emp
WHERE EMPNO = V_ID;
IF V_IS_USER_EXIST <= 0
THEN
APEX_JSON.open_object;
APEX_JSON.write ('message', 'Error');
APEX_JSON.close_object;
:status := 401;
RETURN;
END IF;
OPEN employees_cursor FOR
SELECT empno "$uri",
rn,
empno,
ename,
job,
hiredate,
mgr,
sal,
comm,
deptno
FROM (SELECT emp.*, ROW_NUMBER () OVER (ORDER BY empno) rn FROM emp)
tmp;
APEX_JSON.open_object;
APEX_JSON.write ('data', employees_cursor);
APEX_JSON.write ('message', 'Success');
APEX_JSON.close_object;
EXCEPTION
WHEN OTHERS
THEN
APEX_JSON.open_object;
APEX_JSON.write ('message', 'Error');
APEX_JSON.close_object;
:status := 401;
END;

employees-endpoint
employees-endpoint
This code will return success message if the user authenticated and the token not expired. We will decode the id from the token and check if the user exist or not. You will find source code of angular application here to check the endpoints.
angular-1
angular-1
angular-2
angular-2
angular-3
angular-3
angular-4
angular-4

That is all, now you can make login and protected endpoints as you want.


Tags

#Oracle#Apex#Oracle Apex#sql#API#restful#angular

Share


Previous Article
Angular Cannot Create New Project
Shady Nagy

Shady Nagy

Software Innovation Architect

Topics

AI
Angular
dotnet
GatsbyJS
Github
Linux
MS SQL
Oracle

Related Posts

Updating Oracle Database Configuration After Changing the Server IP Address
Updating Oracle Database Configuration After Changing the Server IP Address
May 16, 2024
2 min

Quick Links

Contact Us

Social Media