
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.
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.
DECLAREV_TOKEN VARCHAR2 (3000);V_IS_USER_EXIST NUMBER;V_USER_NO NUMBER;V_USER_JOB VARCHAR2 (3000);BEGINSELECT COUNT (*)INTO V_IS_USER_EXISTFROM empWHERE LOWER(ENAME) = LOWER(:userName);IF V_IS_USER_EXIST <= 0THENAPEX_JSON.open_object;APEX_JSON.write ('message', 'Error');APEX_JSON.close_object;RETURN;END IF;SELECT EMPNO, JOBINTO V_USER_NO, V_USER_JOBFROM empWHERE 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;
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
DECLAREemployees_cursor SYS_REFCURSOR;l_token apex_jwt.t_token;V_IS_USER_EXIST NUMBER;V_ID NUMBER;USER_OBJECT apex_json.t_values;BEGINIF :Authorization IS NULLTHENAPEX_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)THENAPEX_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_EXISTFROM empWHERE EMPNO = V_ID;IF V_IS_USER_EXIST <= 0THENAPEX_JSON.open_object;APEX_JSON.write ('message', 'Error');APEX_JSON.close_object;:status := 401;RETURN;END IF;OPEN employees_cursor FORSELECT empno "$uri",rn,empno,ename,job,hiredate,mgr,sal,comm,deptnoFROM (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;EXCEPTIONWHEN OTHERSTHENAPEX_JSON.open_object;APEX_JSON.write ('message', 'Error');APEX_JSON.close_object;:status := 401;END;
That is all, now you can make login and protected endpoints as you want.
Quick Links
Legal Stuff