1 min read

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

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 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-2 angular-3 angular-4

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

About the author

For the last decade, Shady Nagy has worked with a variety of web technologies. He is currently focused on code quality. On his day to day job, he is working as a team leader engineer. As a digital nomad, he is living where the WiFi and sun are 😎
Do you want to know more? Visit my website!