Using a Master-Detail design with RESTful APIs, you can expose endpoints like /invoices/ to retrieve or manage master records and /invoices/{invoice_id}/lines/ to handle related details. This setup enables dynamic and modular data retrieval on the frontend—for example, showing invoice headers in a grid, and upon selection, loading the corresponding line items. This model promotes efficient data handling, reusability, and a user-friendly interface in enterprise-level applications.
Title : Invoice API - ORDS PL/SQL Module
Author : ERPstuff.com
Contact : info@erpstuff.com
Purpose : Demonstration of building a RESTful API using Oracle ORDS and PL/SQL.
DISCLAIMER:-----------------------------------------------------------------------------------------------
This API is provided for **training and educational purposes only**.
It is **not intended or supported for production use**. No warranties, guarantees,
or security assurances are provided with this code. Use at your own risk.
FEATURES:
-----------------------------------------------------------------------------------------------
1. Template for invoice header and lines creation in a single POST request (`invoices/`)
2. Supports individual invoice header creation, listing, and deletion (`invoiceheader/`)
3. Supports individual invoice line creation and retrieval (`invoicelines/`)
4. JSON-based payload processing using APEX_JSON
5. Error handling with appropriate HTTP status codes and messages
6. Self-contained tables with foreign key constraints and cascading deletes
7. Fully compatible with Oracle ORDS RESTful services
Tables Involved:
- JS_INVOICE_HEADER
- JS_INVOICE_LINES
- JS_DEBUG_LOG
REST Module Name:
- ERPstuff_invoice_api_v1
Base Path:
- /invoiceapi/
Demo Tables Scripts
Code: Select all
-- Drop tables if they exist
-- DROP TABLE JS_INVOICE_LINES CASCADE CONSTRAINTS;
-- DROP TABLE JS_INVOICE_HEADER CASCADE CONSTRAINTS;
-- DROP TABLE JS_DEBUG_LOG;
-- Create Invoice Header Table
CREATE TABLE JS_INVOICE_HEADER (
INVOICE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
INVOICE_NO VARCHAR2(50) UNIQUE,
CUSTOMER_NAME VARCHAR2(100),
INVOICE_DATE DATE
);
-- Create Invoice Lines Table
CREATE TABLE JS_INVOICE_LINES (
LINE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
INVOICE_ID NUMBER,
LINE_NUM NUMBER,
ITEM_DESC VARCHAR2(200),
QUANTITY NUMBER,
PRICE NUMBER(10, 2),
FOREIGN KEY (INVOICE_ID) REFERENCES JS_INVOICE_HEADER(INVOICE_ID) ON DELETE CASCADE
);
-- Create log table
CREATE TABLE "JS_DEBUG_LOG"
( "ID" NUMBER NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"LOG_MESSAGE" VARCHAR2(200),
"LOG_DATA" CLOB,
"LOG_TIME" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP
) ;
Code: Select all
-- Delete existing module if exists
-- BEGIN
-- ORDS.delete_module(p_module_name => 'ERPstuff_invoice_api_v1');
-- EXCEPTION
-- WHEN OTHERS THEN NULL;
-- END;
-- /
-- Define the module
BEGIN
ORDS.define_module(
p_module_name => 'ERPstuff_invoice_api_v1',
p_base_path => '/invoiceapi/',
p_items_per_page => 25,
p_comments => ' Title : Invoice API - ORDS PL/SQL Module
Author : ERPstuff.com
Contact : info@erpstuff.com
Purpose : Demonstration of building a RESTful API using Oracle ORDS and PL/SQL.
⚠️ DISCLAIMER:
-----------------------------------------------------------------------------------------------
This API is provided for **training and educational purposes only**.
It is **not intended or supported for production use**. No warranties, guarantees,
or security assurances are provided with this code. Use at your own risk.
FEATURES:
-----------------------------------------------------------------------------------------------
1. Template for invoice header and lines creation in a single POST request (`invoices/`)
2. Supports individual invoice header creation, listing, and deletion (`invoiceheader/`)
3. Supports individual invoice line creation and retrieval (`invoicelines/`)
4. JSON-based payload processing using APEX_JSON
5. Error handling with appropriate HTTP status codes and messages
6. Self-contained tables with foreign key constraints and cascading deletes
7. Fully compatible with Oracle ORDS RESTful services
Tables Involved:
- JS_INVOICE_HEADER
- JS_INVOICE_LINES
REST Module Name:
- ERPstuff_invoice_api_v1
Base Path:
- /invoiceapi/'
);
-- Invoices: Bulk POST and GET
ORDS.define_template(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoices/',
p_comments => 'Template for invoice header and lines creation in a single call and retrieval'
);
-- POST bulk invoice with lines
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoices/',
p_method => 'POST',
p_mimes_allowed => 'application/json',
p_source_type => ORDS.source_type_plsql,
p_source => q'[
DECLARE
l_invoice_id NUMBER;
l_body CLOB := :body_text;
l_customer VARCHAR2(100);
l_inv_date DATE;
l_invoice_no VARCHAR2(50);
l_line_count NUMBER := 0;
BEGIN
apex_json.parse(l_body);
l_invoice_no := apex_json.get_varchar2('invoice_no');
l_customer := apex_json.get_varchar2('customer_name');
l_inv_date := TO_TIMESTAMP_TZ(apex_json.get_varchar2('invoice_date'), 'YYYY-MM-DD"T"HH24:MI:SS.FF9"Z"');
INSERT INTO JS_INVOICE_HEADER (INVOICE_NO, CUSTOMER_NAME, INVOICE_DATE)
VALUES (l_invoice_no, l_customer, l_inv_date)
RETURNING INVOICE_ID INTO l_invoice_id;
FOR i IN 1 .. apex_json.get_count('lines') LOOP
INSERT INTO JS_INVOICE_LINES (
INVOICE_ID, LINE_NUM, ITEM_DESC, QUANTITY, PRICE
) VALUES (
l_invoice_id,
i,
apex_json.get_varchar2('lines[%d].item_desc', i),
apex_json.get_number('lines[%d].quantity', i),
apex_json.get_number('lines[%d].price', i)
);
l_line_count := l_line_count + 1;
END LOOP;
COMMIT;
:status_code := 201;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('invoice_id', l_invoice_id);
apex_json.write('lines_inserted', l_line_count);
apex_json.write('message', 'Invoice and lines created');
apex_json.close_object;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
IF SQLCODE = -1 THEN
:status_code := 409;
apex_json.write('error', 'Duplicate invoice number not allowed');
ELSIF SQLCODE = -1400 THEN
:status_code := 400;
apex_json.write('error', 'Required field is missing');
ELSIF SQLCODE = -1722 THEN
:status_code := 400;
apex_json.write('error', 'Invalid date format');
ELSE
apex_json.write('error', 'Internal server error');
END IF;
apex_json.close_object;
END;
]'
);
-- GET all invoices
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoices/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT invoice_id, invoice_no, customer_name, invoice_date FROM JS_INVOICE_HEADER'
);
-- Invoice Header: POST, GET, DELETE
ORDS.define_template(p_module_name => 'ERPstuff_invoice_api_v1', p_pattern => 'invoiceheader/',
p_comments => 'Template for invoice header creation in a single call and retrieval');
-- POST header only
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoiceheader/',
p_method => 'POST',
p_mimes_allowed => 'application/json',
p_source_type => ORDS.source_type_plsql,
p_source => q'[
DECLARE
l_body CLOB := :body_text;
l_invoice_id NUMBER;
l_invoice_no VARCHAR2(50);
l_customer VARCHAR2(100);
l_inv_date DATE;
l_SQLERRM VARCHAR2(4000);
BEGIN
apex_json.parse(l_body);
l_invoice_no := apex_json.get_varchar2('invoice_no');
l_customer := apex_json.get_varchar2('customer_name');
l_inv_date := TO_TIMESTAMP_TZ(apex_json.get_varchar2('invoice_date'), 'YYYY-MM-DD"T"HH24:MI:SS.FF9"Z"');
INSERT INTO JS_INVOICE_HEADER (INVOICE_NO, CUSTOMER_NAME, INVOICE_DATE)
VALUES (l_invoice_no, l_customer, l_inv_date)
RETURNING INVOICE_ID INTO l_invoice_id;
COMMIT;
:status_code := 201;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('invoice_id', l_invoice_id);
apex_json.write('message', 'Invoice header created');
apex_json.close_object;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
-- Uncomment if any error and need to debug
-- v_SQLERRM := SQLERRM;
-- INSERT INTO JS_DEBUG_LOG (log_message, log_data, log_time)
-- VALUES
-- ('Body Data', l_body, SYSDATE);
-- INSERT INTO JS_DEBUG_LOG (log_message, log_data, log_time)
-- VALUES
-- ('Exception Raised', v_SQLERRM, SYSDATE);
-- COMMIT;
:status_code := 500;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('error', 'Failed to create invoice header');
apex_json.close_object;
END;
]'
);
-- GET all invoice headers
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoiceheader/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT invoice_id, invoice_no, customer_name, invoice_date FROM JS_INVOICE_HEADER'
);
-- DELETE header (by invoice_no in JSON body)
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoiceheader/',
p_method => 'DELETE',
p_mimes_allowed => 'application/json',
p_source_type => ORDS.source_type_plsql,
p_source => q'[
DECLARE
l_body CLOB := :body_text;
l_invoice_no VARCHAR2(50);
l_invoice_id NUMBER;
BEGIN
apex_json.parse(l_body);
l_invoice_no := apex_json.get_varchar2('invoice_no');
SELECT invoice_id INTO l_invoice_id
FROM js_invoice_header
WHERE invoice_no = l_invoice_no;
DELETE FROM js_invoice_lines WHERE invoice_id = l_invoice_id;
DELETE FROM js_invoice_header WHERE invoice_id = l_invoice_id;
COMMIT;
:status_code := 200;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('message', 'Invoice and lines deleted successfully');
apex_json.close_object;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
:status_code := 404;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('error', 'Invoice not found');
apex_json.close_object;
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('error', 'Failed to delete invoice');
apex_json.close_object;
END;
]'
);
-- Invoice Lines: GET and POST
ORDS.define_template(p_module_name => 'ERPstuff_invoice_api_v1', p_pattern => 'invoicelines/',
p_comments => 'Template for invoice lines creation in a single call and retrieval');
-- GET lines
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoicelines/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => '
SELECT line_id, invoice_id, line_num, item_desc, quantity, price
FROM JS_INVOICE_LINES
'
);
-- POST line
ORDS.define_handler(
p_module_name => 'ERPstuff_invoice_api_v1',
p_pattern => 'invoicelines/',
p_method => 'POST',
p_mimes_allowed => 'application/json',
p_source_type => ORDS.source_type_plsql,
p_source => q'[
DECLARE
l_body CLOB := :body_text;
l_invoice_id NUMBER;
l_line_num NUMBER;
l_item_desc VARCHAR2(200);
l_quantity NUMBER;
l_price NUMBER(10,2);
l_line_id NUMBER;
BEGIN
apex_json.parse(l_body);
l_invoice_id := apex_json.get_number('invoice_id');
l_line_num := apex_json.get_number('line_num');
l_item_desc := apex_json.get_varchar2('item_desc');
l_quantity := apex_json.get_number('quantity');
l_price := apex_json.get_number('price');
INSERT INTO JS_INVOICE_LINES (INVOICE_ID, LINE_NUM, ITEM_DESC, QUANTITY, PRICE)
VALUES (l_invoice_id, l_line_num, l_item_desc, l_quantity, l_price)
RETURNING LINE_ID INTO l_line_id;
COMMIT;
:status_code := 201;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('line_id', l_line_id);
apex_json.write('message', 'Invoice line created');
apex_json.close_object;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
owa_util.mime_header('application/json', FALSE);
htp.p('');
apex_json.initialize_output;
apex_json.open_object;
apex_json.write('error', 'Failed to create invoice line');
apex_json.close_object;
END;
]'
);
COMMIT;
END;
/
Using apex_web_service
Code: Select all
DECLARE
l_url VARCHAR2(4000) := 'https://gfd19a0655d8326-jsss.adb.ap-singapore-1.oraclecloudapps.com/ords/jsss/invoiceapi/invoices/';
l_json_payload CLOB;
l_response CLOB;
BEGIN
-- Build JSON payload
l_json_payload := '{
"invoice_no": "INV-1007",
"customer_name": "Acme Corporation",
"invoice_date": "2025-07-19",
"lines": [
{
"item_desc": "Widget A",
"quantity": 10,
"price": 15.5
},
{
"item_desc": "Widget B",
"quantity": 5,
"price": 25.0
}
]
}';
-- Clear previous headers
apex_web_service.g_request_headers.DELETE;
-- Set required headers
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'Accept-Language';
apex_web_service.g_request_headers(2).value := 'en';
apex_web_service.g_request_headers(3).name := 'Accept-Version';
apex_web_service.g_request_headers(3).value := 'V2';
-- Make the REST call
l_response := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => 'POST',
p_body => l_json_payload
);
-- Output response
DBMS_OUTPUT.put_line('Response:');
DBMS_OUTPUT.put_line(l_response);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
END;
Code: Select all
DECLARE
l_http_req UTL_HTTP.req;
l_http_resp UTL_HTTP.resp;
l_url VARCHAR2(4000) := 'https://gfd19a0655d8326-jsss.adb.ap-singapore-1.oraclecloudapps.com/ords/jsss/invoiceapi/invoices/';
l_json_payload CLOB;
l_response CLOB;
BEGIN
-- Build JSON payload
l_json_payload := '{
"invoice_no": "INV-1007",
"customer_name": "Acme Corporation",
"invoice_date": "2025-07-19",
"lines": [
{
"item_desc": "Widget A",
"quantity": 10,
"price": 15.5
},
{
"item_desc": "Widget B",
"quantity": 5,
"price": 25.0
}
]
}';
l_http_req := UTL_HTTP.begin_request(
url => l_url,
method => 'POST',
http_version => 'HTTP/1.1'
);
-- Set headers
UTL_HTTP.set_header(l_http_req, 'Content-Type', 'application/json');
UTL_HTTP.set_header(l_http_req, 'Content-Length', LENGTH(l_json_payload));
-- Write JSON body
UTL_HTTP.write_text(l_http_req, l_json_payload);
-- Get response
l_http_resp := UTL_HTTP.get_response(l_http_req);
-- Read the response
BEGIN
LOOP
UTL_HTTP.read_text(l_http_resp, l_response);
DBMS_OUTPUT.put_line(l_response);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_resp);
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
UTL_HTTP.end_response(l_http_resp);
END;
Application exported fileOracle APEX Version 24.2.6
ORDS_VERSION 25.2.0.r1651520
RESTful services Definition Exported
RESTful services Definition SQL Script Share your feedback at info@erpstuff.com