Jumpstarting ORDS REST API Development: A Comprehensive Guide for Beginners - Part 1
During my INAOUG Explore webinar on Mastering REST API using Oracle APEX, I discussed the creation of a secure REST API with OAuth2. One attendee asked a question about consuming a secured REST API in Oracle APEX, which was beyond the scope of that session. However, I will address this topic in this blog post. I will begin by creating a REST API and securing it with OAuth2, and then demonstrate how to invoke it using PL/SQL API.
Part 1: Create REST API
Part2: Secure REST API using Oauth2
Part3: Invoke REST API using PL/SQL
Let's get started.
I have used the standard "Employees" table from the HR schema. If you have not yet installed this table in your schema, you can do so by following these steps:
- Go to SQL Workshop in your APEX environment.
- Navigate to Utilities.
- Select Sample Datasets.
- Look for the "HR Data" dataset and choose to install it.
By installing the "HR Data" dataset, you will have the necessary table available in your schema. I have made alterations to this table by adding three new columns. The purpose of these additions is to demonstrate how to create a REST API that can handle BLOB (Binary Large Object) data.
ALTER TABLE OEHR_EMPLOYEES
ADD (MIME_TYPE VARCHAR2(256),
FILE_NAME VARCHAR2(256),
CONTENT BLOB);
As mentioned during the webinar, it is considered good practice to design your REST API by determining the module name, defining the template URI pattern, and specifying the appropriate HTTP methods for each template. Taking these factors into account helps create a well-structured and standardized REST API design.
As presented below, I have designed a REST API for the "Employees" table:
Module Base Path: hr/v2/
Template URI Pattern: /employees/
HTTP Methods:
GET: to retrieve all employees
POST: to create a new employee
Template URI Pattern: /employees/:id
HTTP Methods:
GET: to retrieve an employee record
PUT: to update an employee record
DELETE: to delete an employee record
Template URI Pattern: /employees/:id/photo/
HTTP Methods:
GET: to retrieve photo of an employee
PUT: to update a photo of an employee
I have also created a package called "HR_API_V2" to support the API functionality. I prefer to avoid writing all the logic directly into the Source section of the Handler Definition. Instead, I advocate for creating a dedicated package to handle APIs. By utilizing a separate package, the API logic can be encapsulated within well-defined procedures and functions and it allows better code organization and promotes reusability and maintainability.
You can find the code for both the RESTful webservice module and the package at the very bottom section of this blog.
Once you have executed the code, the module will take the following structure:
Let's take a deep dive to understand this module and package.
Template : employees/
Method: GET
To retrieve all the employee records from the table, we have implemented a GET method within the employee/ template. As depicted in the image below, we have specified the Source Type as Collection Query and in the source section, we have specified simple query to select all the records from the OEHR_EMP_DETAILS_VIEW view.
Template : employees/
Method: POST
To create a new employee record, we have implemented a POST method within the employee/ template. As shown in the image below, we have specified the Source Type as PL/SQL and in the source section, we are calling CREATE_EMP procedure of the HR_API_V2 package.
About CREATE_EMP procedure:
The CREATE_EMP procedure accepts a JSON body as an input parameter. It parses the input JSON body, extracts each element, and validates them. If all the elements are valid, it proceeds to insert a new employee record into the OEHR_EMPLOYEES table. The procedure returns three parameters: the resource location, status code, and status message.
Resource Location is a URI of the newly created record and we want to display it as a link in the JSON response.
About Parameters
HANDLER parameters provide us with the capability to interact with both HTTP requests and responses. They enable us to read information from the request header and/or body, as well as construct the response header and/or body.
ORDS incorporates two types of parameters: implicit parameters and explicit parameters.
Implicit Parameters are predefined by ORDS. Among them, ":body_text" is an implicit parameter specifically designed for handling the HTTP request body as a CLOB variable.
The "status_msg" is linked to the "status_msg" bind variable, with its Source Type set as RESPONSE and its Access Method set as OUT. This configuration signifies that the "status_msg" parameter will be included in the response body.
The "$self" parameter is associated with the "res_location" and configured with Source Type as RESPONSE and Access Method as OUT, which indicates its presence in the response body. But remember, we want to display resource location as a link and that is why it is important to note that the parameter name is prefixed with "$" to inform ORDS to generate a hyperlink for the corresponding JSON attribute. When ORDS encounters a parameter name starting with "$", it generates a link using the string following the "$" as the hypermedia link label, followed by the actual link itself.
"X-ORDS-STATUS-CODE" refers to a custom HTTP response header that is set by ORDS. It is used to provide information about the status code of the HTTP response. It is linked to the "status" bind variable, with its Source Type set as HTTP HEADER and its Access Methos set as OUT.
Template : employees/:id
Method: DELETE
To delete a single employee record, we have created a DELETE method in the employee/:id template. As shown in the below image, the Source Type is set as PL/SQL. Within the source section, we invoke the DELETE_EMP procedure from the HR_API_V2 package.
The DELETE_EMP procedure accepts the employee_id as an input parameter and returns the status code and status message as out parameters. Since we have already defined ":id" as a bind parameter in the template URI, there is no need to explicitly declare this parameter in the parameter section.
 |
Method: GET
To retrieve a single employee record, we have implemented a GET method in the employees/:id template. As depicted in the image below, the Source Type is set as Collection Query. In this case, we execute a query on the OEHR_EMP_DETAILS_VIEW to select a single record where the employee_id matches the value provided in the :id parameter.
Template : employees/:id
Method: PUT
To update a single employee record, we have created a PUT method, as shown in the image below. The Source Type is set as PL/SQL, and in the Source Section, we invoke the UPDATE_EMP procedure from the HR_API_V2.
The UPDATE_EMP procedure requires two input parameters: Employee Id and Request body. It also returns three out parameters: resource location, status code, and status message.
The one thing to note here is X-ORDS-FORWARD parameter. This parameter is mapped with res_location bind variable. Its Access Methos is set to OUT and Source Type is set to HTTP Header.
As depicted in the image below, the salary of employee 999 has been updated, and we can observe the updated record in the response.
Method: GET
To retrieve the photo of an employee, we have implemented a GET method in the employees/:id/photo/ template. As illustrated in the image below, the :id bind variable is specified within the template URI. The Source Type is set to Media Resource, and within the Source section, we select the mime_type and content (BLOB) columns from the OEHR_EMPLOYEES table.
Template : employees/:id/photo/
Method: PUT
To update an employee's photo, we have implemented a PUT method within the employees/:id/photo/ template. As depicted in the image below, the Source Type is set to PL/SQL. Within the Source section, we execute the UPDATE_PHOTO procedure from the HR_API_V2 package.
The UPDATE_PHOTO procedure accepts four input parameters: employee_id, BLOB content, content type, and file name. It also returns three out parameters: resource location, status, and status message.
:body is an implicit parameter which specifies the body of the request as a temporary BLOB.
We have defined five parameters, including mime_type and file_name. The Access Method for both mime_type and file_name is set to IN, indicating they are input parameters. Additionally, the Source Type for both parameters is set to HTTP Header, specifying that their values are extracted from the HTTP headers of the request.
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'WKSP_WS1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'apidemo',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'hr/v2/',
p_base_path => '/hr/v2/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'BEGIN
HR_API_V2.CREATE_EMP(
P_REQUEST_BODY => :body_text,
P_RES_LOC => :res_location,
P_STATUS_CD => :status,
P_STATUS_MSG => :status_msg
);
END;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_method => 'POST',
p_name => 'X-ORDS-STATUS-CODE',
p_bind_variable_name => 'status',
p_source_type => 'HEADER',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_method => 'POST',
p_name => 'status_msg',
p_bind_variable_name => 'status_msg',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_method => 'POST',
p_name => '$self',
p_bind_variable_name => 'res_location',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'SELECT *
FROM OEHR_EMP_DETAILS_VIEW');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'GET',
p_source_type => 'resource/lob',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'SELECT mime_type,
content
FROM oehr_employees
WHERE employee_id = :id');
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => 'Update Photo',
p_source =>
'BEGIN
HR_API_V2.UPDATE_PHOTO(
P_EMPLOYEE_ID => :id,
P_CONTENT => :body,
P_CONTENT_TYPE => :mime_type,
P_FILE_NAME => :file_name,
P_RES_LOC => :res_location,
P_STATUS_CD => :status,
P_STATUS_MSG => :status_msg
);
END;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_name => 'status_msg',
p_bind_variable_name => 'status_msg',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_name => 'X-ORDS-STATUS-CODE',
p_bind_variable_name => 'status',
p_source_type => 'HEADER',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_name => '$self',
p_bind_variable_name => 'res_location',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_name => 'mime_type',
p_bind_variable_name => 'mime_type',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id/photo/',
p_method => 'PUT',
p_name => 'file_name',
p_bind_variable_name => 'file_name',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => 'Get a single record',
p_source =>
'SELECT *
FROM OEHR_EMP_DETAILS_VIEW e
WHERE e.employee_id = :id');
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'PUT',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'BEGIN
HR_API_V2.UPDATE_EMP(
P_EMPLOYEE_ID => :id,
P_REQUEST_BODY => :body_text,
P_RES_LOC => :res_location,
P_STATUS_CD => :status,
P_STATUS_MSG => :status_msg
);
END;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'PUT',
p_name => 'X-ORDS-STATUS-CODE',
p_bind_variable_name => 'status',
p_source_type => 'HEADER',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'PUT',
p_name => 'status_msg',
p_bind_variable_name => 'status_msg',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'PUT',
p_name => 'X-ORDS-FORWARD',
p_bind_variable_name => 'res_location',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'DELETE',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'BEGIN
HR_API_V1.DELETE_EMP(
P_EMPLOYEE_ID => :id,
P_STATUS_CD => :status,
P_STATUS_MSG => :status_msg
);
END;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'DELETE',
p_name => 'X-ORDS-STATUS-CODE',
p_bind_variable_name => 'status',
p_source_type => 'HEADER',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'hr/v2/',
p_pattern => 'employees/:id',
p_method => 'DELETE',
p_name => 'status_msg',
p_bind_variable_name => 'status_msg',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
COMMIT;
END;
CREATE OR REPLACE PACKAGE HR_API_V2 IS
--------------------------------------------------------------
-- create procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure create_emp (
P_REQUEST_BODY in clob,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
);
--------------------------------------------------------------
-- update procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure update_emp (
P_EMPLOYEE_ID in number,
P_REQUEST_BODY in clob,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
);
--------------------------------------------------------------
-- delete procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure delete_emp (
P_EMPLOYEE_ID in number,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
);
--------------------------------------------------------------
-- procedure to update employee photo
--------------------------------------------------------------
procedure update_photo(
P_EMPLOYEE_ID in number,
P_CONTENT in blob,
P_CONTENT_TYPE in varchar2,
P_FILE_NAME in varchar2,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
);
END HR_API_V2;
/
CREATE OR REPLACE PACKAGE BODY HR_API_V2 IS
--------------------------------------------------------------
-- create procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure create_emp (
P_REQUEST_BODY in clob,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
) IS
v_body CLOB;
json_content APEX_JSON.t_values;
v_employee_id VARCHAR2(20);
v_first_name VARCHAR2(20);
v_last_name VARCHAR2(20);
v_email VARCHAR2(20);
v_phone_number VARCHAR2(20);
v_hire_date VARCHAR2(20);
v_job_id VARCHAR2(20);
v_salary VARCHAR2(20);
v_commission_pct VARCHAR2(20);
v_manager_id VARCHAR2(20);
v_department_id VARCHAR2(20);
v_error_msg VARCHAR2(500);
v_error_cd NUMBER;
E_bad_request EXCEPTION;
BEGIN
v_body := P_REQUEST_BODY;
--
-- Parse json body
--
APEX_JSON.parse(json_content, v_body);
v_employee_id := APEX_JSON.get_varchar2(p_path => 'employee_id', p_values => json_content);
v_first_name := APEX_JSON.get_varchar2(p_path => 'first_name', p_values => json_content);
v_last_name := APEX_JSON.get_varchar2(p_path => 'last_name', p_values => json_content);
v_email := APEX_JSON.get_varchar2(p_path => 'email', p_values => json_content);
v_phone_number := APEX_JSON.get_varchar2(p_path => 'phone_number', p_values => json_content);
v_hire_date := APEX_JSON.get_varchar2(p_path => 'hire_date', p_values => json_content);
v_job_id := APEX_JSON.get_varchar2(p_path => 'job_id', p_values => json_content);
v_salary := APEX_JSON.get_varchar2(p_path => 'salary', p_values => json_content);
v_commission_pct := APEX_JSON.get_varchar2(p_path => 'commission_pct', p_values => json_content);
v_manager_id := APEX_JSON.get_varchar2(p_path => 'manager_id', p_values => json_content);
v_department_id := APEX_JSON.get_varchar2(p_path => 'department_id', p_values => json_content);
/**
** Validate Input Parameters
**/
IF v_first_name IS NULL THEN
v_error_msg := 'first_name cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
IF v_last_name IS NULL THEN
v_error_msg := 'last_name cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
IF v_email IS NULL THEN
v_error_msg := 'email cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
/**
** Insert into oher_employees table
**/
insert into OEHR_EMPLOYEES (
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID
) values (
v_employee_id ,
v_first_name,
v_last_name,
v_email,
v_phone_number,
TO_DATE(v_hire_date,'YYYY/MM/DD'),
v_job_id ,
v_salary,
v_commission_pct,
v_manager_id,
v_department_id
) ;
P_res_loc := './'||v_employee_id ;
P_STATUS_CD := 201;
P_STATUS_MSG := 'record successfully created';
EXCEPTION
WHEN E_bad_request THEN
P_STATUS_CD := v_error_cd;
P_STATUS_MSG := v_error_msg;
WHEN OTHERS THEN
P_STATUS_CD := 400;
P_STATUS_MSG := sqlerrm;
END create_emp;
--------------------------------------------------------------
-- update procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure update_emp (
P_EMPLOYEE_ID in number,
P_REQUEST_BODY in clob,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
) IS
v_body CLOB;
json_content APEX_JSON.t_values;
v_employee_id VARCHAR2(20);
v_first_name VARCHAR2(20);
v_last_name VARCHAR2(20);
v_email VARCHAR2(20);
v_phone_number VARCHAR2(20);
v_hire_date VARCHAR2(20);
v_job_id VARCHAR2(20);
v_salary VARCHAR2(20);
v_commission_pct VARCHAR2(20);
v_manager_id VARCHAR2(20);
v_department_id VARCHAR2(20);
v_error_msg VARCHAR2(500);
v_error_cd NUMBER;
E_bad_request EXCEPTION;
BEGIN
v_body := P_REQUEST_BODY;
--
-- Parse json body
--
APEX_JSON.parse(json_content, v_body);
v_employee_id := P_EMPLOYEE_ID;
v_first_name := APEX_JSON.get_varchar2(p_path => 'first_name', p_values => json_content);
v_last_name := APEX_JSON.get_varchar2(p_path => 'last_name', p_values => json_content);
v_email := APEX_JSON.get_varchar2(p_path => 'email', p_values => json_content);
v_phone_number := APEX_JSON.get_varchar2(p_path => 'phone_number', p_values => json_content);
v_hire_date := APEX_JSON.get_varchar2(p_path => 'hire_date', p_values => json_content);
v_job_id := APEX_JSON.get_varchar2(p_path => 'job_id', p_values => json_content);
v_salary := APEX_JSON.get_varchar2(p_path => 'salary', p_values => json_content);
v_commission_pct := APEX_JSON.get_varchar2(p_path => 'commission_pct', p_values => json_content);
v_manager_id := APEX_JSON.get_varchar2(p_path => 'manager_id', p_values => json_content);
v_department_id := APEX_JSON.get_varchar2(p_path => 'department_id', p_values => json_content);
/**
** Validate Input Parameters
**/
IF v_employee_id IS NULL THEN
v_error_msg := 'employee_id cannot be null';
v_error_cd := 400;
RAISE E_bad_request;
END IF;
IF v_first_name IS NULL THEN
v_error_msg := 'first_name cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
IF v_last_name IS NULL THEN
v_error_msg := 'last_name cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
IF v_email IS NULL THEN
v_error_msg := 'email cannot be null';
v_error_cd := 400; --Bad Request
RAISE E_bad_request;
END IF;
--
-- Update record
--
FOR i IN (SELECT * FROM oehr_employees e WHERE e.employee_id = P_EMPLOYEE_ID)
LOOP
UPDATE oehr_employees
SET first_name = NVL(v_first_name, i.first_name),
last_name = NVL(v_last_name, i.last_name),
email = NVL(v_email, i.email),
phone_number = NVL(v_phone_number, i.phone_number),
hire_date = NVL(TO_DATE(v_hire_date,'YYYY/MM/DD'),i.hire_date),
job_id = NVL(v_job_id,i.job_id),
salary = NVL(v_salary,i.salary),
commission_pct = NVL(v_commission_pct, i.commission_pct),
manager_id = NVL(v_manager_id,i.manager_id),
department_id = NVL(v_department_id,department_id)
WHERE employee_id = P_EMPLOYEE_ID;
END LOOP;
IF SQL%FOUND THEN
P_res_loc := './'||P_EMPLOYEE_ID;
P_STATUS_CD := 200;
P_STATUS_MSG := 'record successfully updated';
ELSE
P_STATUS_CD := 400;
P_STATUS_MSG := 'employee not found';
END IF;
EXCEPTION
WHEN E_bad_request THEN
P_STATUS_CD := v_error_cd;
P_STATUS_MSG := v_error_msg;
WHEN OTHERS THEN
P_STATUS_CD := 400;
P_STATUS_MSG := sqlerrm;
END update_emp;
--------------------------------------------------------------
-- delete procedure for table "OEHR_EMPLOYEES"
--------------------------------------------------------------
procedure delete_emp (
P_EMPLOYEE_ID in number,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
) IS
E_bad_request EXCEPTION;
v_error_msg VARCHAR2(500);
v_error_cd NUMBER;
BEGIN
/*
*** Check if P_employee_id is null or not
**/
IF P_EMPLOYEE_ID IS NULL THEN
v_error_msg := 'id cannot be null';
v_error_cd := 400;
RAISE E_bad_request;
END IF;
DELETE FROM oehr_employees e
WHERE e.employee_id = P_EMPLOYEE_ID;
IF SQL%FOUND THEN
P_STATUS_CD := 200;
P_STATUS_MSG := 'employee '||P_EMPLOYEE_ID||' successfully deleted';
ELSE
P_STATUS_CD := 400;
P_STATUS_MSG := 'employee not found';
END IF;
EXCEPTION
WHEN E_bad_request THEN
P_STATUS_CD := v_error_cd;
P_STATUS_MSG := v_error_msg;
WHEN OTHERS THEN
P_STATUS_CD := 400;
P_STATUS_MSG := sqlerrm;
END delete_emp;
--------------------------------------------------------------
-- procedure to update employee photo
--------------------------------------------------------------
procedure update_photo(
P_EMPLOYEE_ID in number,
P_CONTENT in blob,
P_CONTENT_TYPE in varchar2,
P_FILE_NAME in varchar2,
P_RES_LOC OUT varchar2,
P_STATUS_CD OUT number,
P_STATUS_MSG OUT varchar2
) IS
v_content BLOB;
E_bad_request EXCEPTION;
v_error_msg VARCHAR2(500);
v_error_cd NUMBER;
BEGIN
v_content := P_CONTENT;
UPDATE oehr_employees e
SET e.mime_type = P_CONTENT_TYPE,
e.file_name = P_FILE_NAME,
e.content = v_content
WHERE e.employee_id = P_EMPLOYEE_ID;
IF SQL%FOUND THEN
P_res_loc := './';
P_STATUS_CD := 200;
P_STATUS_MSG := 'content successfully updated';
ELSE
P_STATUS_CD := 400;
P_STATUS_MSG := 'employee not found';
END IF;
EXCEPTION
WHEN E_bad_request THEN
P_STATUS_CD := v_error_cd;
P_STATUS_MSG := v_error_msg;
WHEN OTHERS THEN
P_STATUS_CD := 400;
P_STATUS_MSG := sqlerrm;
END update_photo;
END HR_API_V2;
In the next part of this series, we will dive into the process of securing our REST API using OAuth2. OAuth2 provides a robust and standardized framework for authentication and authorization, allowing us to control access to our API resources effectively. We will explore OAuth2 Client Credentials flow, and understand how to integrate them into our API.
Comments
Post a Comment