Every tutorial I found did the same thing. They showed you how to build the form, how to generate the payment URL, how to redirect the user to the gateway. And then they stopped.
The implicit assumption was: the user pays, the gateway redirects them back, you're done. But that is exactly where things fell apart for me.
The gateway called back to my server, my APEX app received the redirect, and the user landed on a broken, sessionless page. No context. No confirmation. Just a dead URL that APEX didn't know what to do with. I had successfully taken a payment and completely failed to handle it.
This article is about that part. The part nobody covers.
What We're Actually Building
We're building an end-to-end payment flow inside Oracle APEX, with a focus on the callback and redirect handling. Specifically:
A PL/SQL package that initiates the payment and processes the callback
An ORDS REST handler that acts as the gateway's callback endpoint
A secure redirect back into APEX after payment preserving session, handling failures, and blocking replay attacks
I'll use a credit top-up flow as the concrete example throughout, since that's the real implementation this is drawn from. The patterns apply to any payment use case.
The gateway used here is BML Connect. I'll keep credential details out of it, here what matters is the pattern, not the provider.
The Problem With "Just Redirect"
When you initiate a payment, you send the user off to the gateway's hosted page. At that point, your APEX session is still alive but the user has left your app.
When the payment completes, the gateway needs to tell your system two things:
Server-to-server: a backend callback (sometimes called a webhook or IPN) that posts the payment result to your server regardless of what the browser does.
Browser redirect: the user's browser is sent back to a URL you specified at initiation time.
The naive approach is to make both of these the same URL and to just point them at an APEX page. The problem is that by the time the browser redirect fires, your original APEX session may be gone, invalid, or simply unknown to the incoming request. APEX has no idea who this user is. It generates a new session. Your confirmation page has no data. The user is confused.
There's also a another issue: if you redirect straight to an APEX page URL with the result in query parameters, a user can bookmark that URL and replay it. Or worse, tamper with the parameters before the page loads. You need validation to happen before the user ever sees the confirmation page.
The solution is to put an ORDS REST handler between the gateway and APEX. The handler validates everything server-side, then constructs a controlled redirect back into your app.
Architecture Overview
The ORDS handler does two jobs: it processes the callback (validation, DB update) and then emits an HTML meta-refresh that sends the browser to the right APEX page. Your APEX page only ever loads after the work is done.
Step 1: Setting Up the ORDS REST Handler
In APEX, navigate to SQL Workshop → RESTful Services (or use the ORDS module definitions directly).
Create a module, a template, and a GET handler. The structure you need:
| Setting | Value |
|---|
| Module Base Path | /api/v1/topup/ |
| Template | callback/bml |
| Method | GET |
| Source Type | PL/SQL |
BML Connect sends the callback as a GET request with query parameters. Other gateways use POST check your gateway's documentation and create the handler accordingly. If your gateway sends POST, set the method to POST and read parameters from the request body instead.
The full ORDS URL your handler will be reachable at:
https://<your-ords-base-url>/ords/<your_schema>/api/v1/topup/callback/bml
This is the URL you register with your gateway as the redirect/callback URL at payment initiation time.
For each parameter the gateway will send back (BML sends transactionId, state, signature, localId), you don't need to declare them explicitly in ORDS as they're available as bind variables in your PL/SQL source using the colon prefix: :transactionId, :state, etc.
Step 2: The Payment Package
Before the handler, we need the PL/SQL that does the real work. Keep the handler thin and all logic lives in a package.
Package Spec
CREATE OR REPLACE PACKAGE PKG_PAYMENT AS
FUNCTION GENERATE_LOCAL_REF (
p_entity_code IN VARCHAR2
) RETURN VARCHAR2;
PROCEDURE INITIATE_PAYMENT (
p_entity_id IN NUMBER,
p_amount IN NUMBER,
p_performed_by IN NUMBER,
p_payment_id OUT NUMBER,
p_local_ref OUT VARCHAR2,
p_payment_url OUT VARCHAR2
);
PROCEDURE PROCESS_CALLBACK (
p_transaction_id IN VARCHAR2,
p_state IN VARCHAR2,
p_signature IN VARCHAR2,
p_local_id IN VARCHAR2,
p_result_code OUT NUMBER,
p_result_msg OUT VARCHAR2,
p_session_id OUT VARCHAR2
);
PROCEDURE RECONCILE_PAYMENT (
p_payment_id IN NUMBER,
p_performed_by IN NUMBER,
p_result_code OUT NUMBER,
p_result_msg OUT VARCHAR2
);
END PKG_PAYMENT;
/
Signature Verification
This is the first thing that happens when the gateway calls back. The gateway signs its response using HMAC-SHA1 with a secret key you both share. You recompute the signature on your side and compare. If they don't match, you reject the callback immediately so no DB updates, nothing.
FUNCTION VERIFY_SIGNATURE (
p_transaction_id IN VARCHAR2,
p_state IN VARCHAR2,
p_signature IN VARCHAR2,
p_secret_key IN VARCHAR2
) RETURN BOOLEAN AS
v_raw VARCHAR2(4000);
v_hash_raw RAW(8000);
v_hash_b64 VARCHAR2(512);
BEGIN
-- Concatenate the fields the gateway signed
v_raw := p_transaction_id || p_state;
-- Recompute HMAC-SHA1
v_hash_raw := DBMS_CRYPTO.MAC(
src => UTL_RAW.CAST_TO_RAW(v_raw),
typ => DBMS_CRYPTO.HMAC_SH1,
key => UTL_RAW.CAST_TO_RAW(p_secret_key)
);
-- Base64-encode and compare
v_hash_b64 := UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(v_hash_raw)
);
RETURN (v_hash_b64 = p_signature);
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END VERIFY_SIGNATURE;
A few things worth noting here:
The exact fields that go into the signature string depend on your gateway. BML uses transactionId || state. Check your gateway's documentation carefully.
For UAT/sandbox environments, gateways sometimes send dummy or null signatures. You'll want a UAT bypass in your code so check your config table for whether you're in test mode, and skip verification if the incoming signature matches a known test value.
Generating a Local Reference
Before calling the gateway, generate a unique local reference that belongs to your system. This is crucial beacause you'll use it to look up the payment record when the callback arrives, rather than trusting the gateway's transaction ID alone.
FUNCTION GENERATE_LOCAL_REF (
p_entity_code IN VARCHAR2
) RETURN VARCHAR2 AS
v_seq NUMBER;
BEGIN
SELECT YOUR_SEQUENCE.NEXTVAL INTO v_seq FROM DUAL;
RETURN 'PAY-'
|| UPPER(TRIM(p_entity_code))
|| '-'
|| TO_CHAR(SYSDATE, 'YYYYMMDDHHMMSS')
|| '-'
|| LPAD(TO_CHAR(v_seq), 4, '0');
END GENERATE_LOCAL_REF;
This gives you something like PAY-AGENCY001-20240615143022-0047. Timestamp plus sequence means it's unique even under concurrent load.
Initiating the Payment
This procedure does three things: validates the request, creates a PENDING record in your database, then calls the gateway API to get a payment URL.
The PENDING record is created before you call the gateway. This is intentional. If the API call fails, you still have a record of the attempt. If the gateway somehow calls back before your code finishes (rare, but possible), the record exists to receive it.
PROCEDURE INITIATE_PAYMENT (
p_entity_id IN NUMBER,
p_amount IN NUMBER,
p_performed_by IN NUMBER,
p_payment_id OUT NUMBER,
p_local_ref OUT VARCHAR2,
p_payment_url OUT VARCHAR2
) AS
v_local_ref VARCHAR2(128);
v_payment_id NUMBER;
v_api_key VARCHAR2(512);
v_api_url VARCHAR2(500);
v_return_url VARCHAR2(500);
v_amount_minor NUMBER; -- amount in minor units (e.g. laari, cents)
v_req VARCHAR2(32767);
v_resp VARCHAR2(32767);
v_http_req UTL_HTTP.REQ;
v_http_resp UTL_HTTP.RESP;
v_raw_chunk RAW(2000);
v_gateway_txn_id VARCHAR2(255);
v_pay_url VARCHAR2(1000);
v_err_msg VARCHAR2(4000);
BEGIN
-- Validate your business rules here
-- (entity status, account type, amount > 0, etc.)
IF p_amount <= 0 THEN
RAISE_APPLICATION_ERROR(-20063, 'Amount must be greater than zero.');
END IF;
-- Load gateway config from a config table (never hardcode credentials)
SELECT API_KEY, API_BASE_URL, RETURN_URL
INTO v_api_key, v_api_url, v_return_url
FROM PAYMENT_GATEWAY_CONFIG
WHERE GATEWAY = 'BML' AND IS_ACTIVE = 'Y';
-- Generate your local reference
v_local_ref := GENERATE_LOCAL_REF('YOUR_ENTITY_CODE');
v_amount_minor := ROUND(p_amount * 100); -- convert to minor currency units
-- Create PENDING record and capture the ID via RETURNING
INSERT INTO PAYMENT_TRANSACTION (
ENTITY_ID, AMOUNT, PAYMENT_REFERENCE,
STATUS, GATEWAY, PROCESSED_BY,
INITIATED_AT, APEX_SESSION_ID
) VALUES (
p_entity_id, p_amount, v_local_ref,
'PENDING', 'BML', p_performed_by,
SYSTIMESTAMP,
SYS_CONTEXT('APEX$SESSION', 'APP_SESSION') -- store session now
) RETURNING ID INTO v_payment_id;
COMMIT; -- commit the PENDING record before calling the gateway
-- Build and send the API request
v_req := '{'
|| '"localId":"' || v_local_ref || '",'
|| '"amount":' || TO_CHAR(v_amount_minor) || ','
|| '"currency":"MVR",'
|| '"redirectUrl":"' || v_return_url || '"'
|| '}';
BEGIN
v_http_req := UTL_HTTP.BEGIN_REQUEST(
url => v_api_url || '/public/transactions',
method => 'POST'
);
UTL_HTTP.SET_HEADER(v_http_req, 'Authorization', v_api_key);
UTL_HTTP.SET_HEADER(v_http_req, 'Content-Type', 'application/json');
UTL_HTTP.SET_HEADER(v_http_req, 'Content-Length', TO_CHAR(LENGTHB(v_req)));
UTL_HTTP.WRITE_TEXT(v_http_req, v_req);
v_http_resp := UTL_HTTP.GET_RESPONSE(v_http_req);
v_resp := '';
BEGIN
LOOP
UTL_HTTP.READ_RAW(v_http_resp, v_raw_chunk, 2000);
v_resp := v_resp || UTL_RAW.CAST_TO_VARCHAR2(v_raw_chunk);
END LOOP;
EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(v_http_resp);
END;
-- Parse the gateway response
APEX_JSON.PARSE(v_resp);
v_gateway_txn_id := APEX_JSON.GET_VARCHAR2(p_path => 'id');
v_pay_url := APEX_JSON.GET_VARCHAR2(p_path => 'url');
EXCEPTION WHEN OTHERS THEN
BEGIN UTL_HTTP.END_RESPONSE(v_http_resp);
EXCEPTION WHEN OTHERS THEN NULL;
END;
-- Log the error against the PENDING record
v_err_msg := SUBSTR('Gateway API error: ' || SQLERRM
|| ' | Response: ' || SUBSTR(v_resp, 1, 500), 1, 4000);
UPDATE PAYMENT_TRANSACTION
SET GATEWAY_RAW_RESPONSE = v_err_msg
WHERE ID = v_payment_id;
COMMIT;
RAISE_APPLICATION_ERROR(-20064,
'Could not connect to payment gateway. Your reference is '
|| v_local_ref);
END;
-- Store the gateway's transaction ID
UPDATE PAYMENT_TRANSACTION
SET GATEWAY_TXN_ID = v_gateway_txn_id
WHERE ID = v_payment_id;
COMMIT;
-- Return to the caller (your APEX page process)
p_payment_id := v_payment_id;
p_local_ref := v_local_ref;
p_payment_url := v_pay_url;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END INITIATE_PAYMENT;
Notice SYS_CONTEXT('APEX$SESSION', 'APP_SESSION') stored at insert time. The APEX session ID is captured at the moment the user initiates payment while they're still logged in. This is what we'll use later to redirect them back into the correct session.
On your APEX page, the process that calls this looks like:
DECLARE
v_payment_id NUMBER;
v_local_ref VARCHAR2(128);
v_payment_url VARCHAR2(1000);
BEGIN
PKG_PAYMENT.INITIATE_PAYMENT(
p_entity_id => :P_ENTITY_ID,
p_amount => :P_AMOUNT,
p_performed_by => :APP_USER_ID,
p_payment_id => v_payment_id,
p_local_ref => v_local_ref,
p_payment_url => v_payment_url
);
-- Store the URL in session state, then redirect via a branch
:P_PAYMENT_URL := v_payment_url;
END;
Then use an APEX branch with a redirect to &P_PAYMENT_URL. to send the user to the gateway.
Step 3: Processing the Callback
This is the core of what everyone gets wrong. When the payment is done, the gateway hits your ORDS handler URL. That request has no APEX session. It's a plain HTTP request landing on an ORDS endpoint. You have to validate it, update your database, and then figure out where to send the browser.
The PROCESS_CALLBACK Procedure
PROCEDURE PROCESS_CALLBACK (
p_transaction_id IN VARCHAR2,
p_state IN VARCHAR2,
p_signature IN VARCHAR2,
p_local_id IN VARCHAR2,
p_result_code OUT NUMBER,
p_result_msg OUT VARCHAR2,
p_session_id OUT VARCHAR2
) AS
v_secret_key VARCHAR2(512);
v_payment_id NUMBER;
v_status PAYMENT_TRANSACTION.STATUS%TYPE;
v_session_id PAYMENT_TRANSACTION.APEX_SESSION_ID%TYPE;
v_raw_resp VARCHAR2(4000);
v_sig_valid BOOLEAN;
v_is_uat VARCHAR2(1);
BEGIN
-- Load config
SELECT SECRET_KEY,
CASE WHEN API_BASE_URL LIKE '%uat%' THEN 'Y' ELSE 'N' END
INTO v_secret_key, v_is_uat
FROM PAYMENT_GATEWAY_CONFIG
WHERE GATEWAY = 'BML';
-- -------------------------------------------------------
-- Step 1: Verify the signature
-- -------------------------------------------------------
IF v_is_uat = 'Y' AND (
p_signature IS NULL OR p_signature = 'null'
) THEN
v_sig_valid := TRUE; -- UAT bypass for test environments
ELSE
v_sig_valid := VERIFY_SIGNATURE(
p_transaction_id, p_state, p_signature, v_secret_key);
END IF;
IF NOT v_sig_valid THEN
p_result_code := 1;
p_result_msg := 'Signature verification failed';
-- Log the attempt — a sig failure in production is worth investigating
INSERT INTO AUDIT_LOG (
ENTITY_TYPE, ACTION, PERFORMED_AT, NOTES
) VALUES (
'PAYMENT', 'CALLBACK_SIG_FAIL', SYSTIMESTAMP,
'Sig fail | txnId:' || p_transaction_id
|| ' | state:' || p_state
);
COMMIT;
RETURN;
END IF;
-- -------------------------------------------------------
-- Step 2: Look up the payment record
-- -------------------------------------------------------
-- Primary lookup: your local reference (more reliable)
-- Fallback: gateway transaction ID
BEGIN
IF p_local_id IS NOT NULL AND p_local_id != 'null' THEN
BEGIN
SELECT ID, STATUS, APEX_SESSION_ID
INTO v_payment_id, v_status, v_session_id
FROM PAYMENT_TRANSACTION
WHERE PAYMENT_REFERENCE = p_local_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT ID, STATUS, APEX_SESSION_ID
INTO v_payment_id, v_status, v_session_id
FROM PAYMENT_TRANSACTION
WHERE GATEWAY_TXN_ID = p_transaction_id;
END;
ELSE
SELECT ID, STATUS, APEX_SESSION_ID
INTO v_payment_id, v_status, v_session_id
FROM PAYMENT_TRANSACTION
WHERE GATEWAY_TXN_ID = p_transaction_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_result_code := 2;
p_result_msg := 'Payment record not found';
RETURN;
END;
-- Always return the session ID, even if we bail out below
p_session_id := v_session_id;
-- -------------------------------------------------------
-- Step 3: Idempotency guard
-- -------------------------------------------------------
-- Gateways can and do fire callbacks more than once.
-- If this record is already processed, return silently.
IF v_status != 'PENDING' THEN
p_result_code := 3;
p_result_msg := 'Already processed: ' || v_status;
RETURN;
END IF;
-- -------------------------------------------------------
-- Step 4: Apply the result
-- -------------------------------------------------------
v_raw_resp := 'transactionId=' || p_transaction_id
|| '&state=' || p_state
|| '&localId=' || NVL(p_local_id, 'NULL')
|| '&signature=' || NVL(p_signature, 'null');
IF p_state = 'CONFIRMED' THEN
-- Your business logic: credit the account, issue the policy,
-- record the transaction, write the audit log.
-- In my implementation (top-up flow):
APPLY_TOPUP_SUCCESS(
p_payment_id => v_payment_id,
p_gateway_txn_id => p_transaction_id,
p_signature => NVL(p_signature, 'null'),
p_raw_response => v_raw_resp
);
COMMIT;
p_result_code := 0;
p_result_msg := 'OK';
ELSE
-- Any non-CONFIRMED state: mark as cancelled
UPDATE PAYMENT_TRANSACTION
SET STATUS = 'CANCELLED',
GATEWAY_TXN_ID = p_transaction_id,
GATEWAY_SIGNATURE = p_signature,
GATEWAY_RAW_RESPONSE = v_raw_resp,
COMPLETED_AT = SYSTIMESTAMP
WHERE ID = v_payment_id;
COMMIT;
p_result_code := 0;
p_result_msg := 'CANCELLED';
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_result_code := 4;
p_result_msg := SQLERRM;
END PROCESS_CALLBACK;
The "Apply Success" Procedure
This is where your actual business logic goes. The callback procedure doesn't care what the business logic is, it just calls this after validation passes. I'm showing my full top-up implementation so you can see what a complete version looks like:
PROCEDURE APPLY_TOPUP_SUCCESS (
p_payment_id IN NUMBER,
p_gateway_txn_id IN VARCHAR2,
p_signature IN VARCHAR2,
p_raw_response IN VARCHAR2
) AS
v_payment PAYMENT_TRANSACTION%ROWTYPE;
v_account_id CREDIT_ACCOUNT.ID%TYPE;
v_balance_before CREDIT_ACCOUNT.CURRENT_BALANCE%TYPE;
v_balance_after CREDIT_ACCOUNT.CURRENT_BALANCE%TYPE;
BEGIN
-- Lock the payment record first
SELECT * INTO v_payment
FROM PAYMENT_TRANSACTION
WHERE ID = p_payment_id
FOR UPDATE;
-- Guard: if somehow called twice, bail out
IF v_payment.STATUS != 'PENDING' THEN
RETURN;
END IF;
-- Lock and read the credit account
SELECT ID, CURRENT_BALANCE INTO v_account_id, v_balance_before
FROM CREDIT_ACCOUNT
WHERE ID = v_payment.CREDIT_ACCOUNT_ID
FOR UPDATE;
v_balance_after := v_balance_before + v_payment.AMOUNT;
-- Credit the account
UPDATE CREDIT_ACCOUNT
SET CURRENT_BALANCE = v_balance_after,
UPDATED_AT = SYSTIMESTAMP
WHERE ID = v_account_id;
-- Mark payment as completed
UPDATE PAYMENT_TRANSACTION
SET STATUS = 'COMPLETED',
GATEWAY_TXN_ID = p_gateway_txn_id,
GATEWAY_SIGNATURE = p_signature,
GATEWAY_RAW_RESPONSE = p_raw_response,
COMPLETED_AT = SYSTIMESTAMP
WHERE ID = p_payment_id;
-- Write the ledger entry
INSERT INTO CREDIT_LEDGER (
ACCOUNT_ID, TRANSACTION_TYPE, AMOUNT,
BALANCE_BEFORE, BALANCE_AFTER,
REFERENCE_TYPE, REFERENCE_ID,
DESCRIPTION, TRANSACTION_DATE
) VALUES (
v_account_id, 'TOPUP', v_payment.AMOUNT,
v_balance_before, v_balance_after,
'PAYMENT', p_payment_id,
'Top-up via ' || v_payment.GATEWAY
|| ' | Ref: ' || v_payment.PAYMENT_REFERENCE
|| ' | GW Txn: ' || p_gateway_txn_id,
SYSTIMESTAMP
);
-- Write the audit entry
INSERT INTO AUDIT_LOG (
ENTITY_TYPE, ENTITY_ID, ACTION,
FIELD_NAME, OLD_VALUE, NEW_VALUE,
PERFORMED_AT, NOTES
) VALUES (
'CREDIT_ACCOUNT', v_account_id, 'TOPUP',
'CURRENT_BALANCE',
TO_CHAR(v_balance_before, 'FM999,999,990.00'),
TO_CHAR(v_balance_after, 'FM999,999,990.00'),
SYSTIMESTAMP,
'Payment ID: ' || p_payment_id
|| ' | Amount: ' || TO_CHAR(v_payment.AMOUNT, 'FM999,999,990.00')
);
END APPLY_TOPUP_SUCCESS;
Your version of this procedure should looks different based on what you're building. Policy issuance, order fulfillment, subscription activation or any business case you may have, the shape is the same though.
Step 4: The ORDS Handler - Where Everything Comes Together
This is the PL/SQL source you paste into your ORDS GET handler. It calls the package, then uses the result to construct and emit a redirect.
DECLARE
v_result_code NUMBER;
v_result_msg VARCHAR2(4000);
v_session_id VARCHAR2(128);
v_redirect_url VARCHAR2(2000);
v_base VARCHAR2(500) := 'https://<your-ords-base-url>/ords/f';
v_app_id VARCHAR2(10) := '<your_app_id>';
BEGIN
PKG_PAYMENT.PROCESS_CALLBACK(
p_transaction_id => :transactionId,
p_state => :state,
p_signature => :signature,
p_local_id => :localId,
p_result_code => v_result_code,
p_result_msg => v_result_msg,
p_session_id => v_session_id
);
-- Use the stored session ID — fall back to 0 if somehow null
v_session_id := NVL(v_session_id, '0');
IF v_result_code = 0 AND :state = 'CONFIRMED' THEN
-- Success: redirect to confirmation page, restoring the original session
v_redirect_url := v_base
|| '?p=' || v_app_id
|| ':' || '<your_success_page_number>'
|| ':' || v_session_id -- restored session
|| '::NO::P_REF:' || :localId;
ELSE
-- Failure or cancellation: redirect to error/cancelled page
v_redirect_url := v_base
|| '?p=' || v_app_id
|| ':' || '<your_failure_page_number>'
|| ':' || v_session_id
|| '::NO::P_REF:' || NVL(:localId, 'UNKNOWN')
|| ',P_ERR:' || SUBSTR(v_result_msg, 1, 200);
END IF;
HTP.p('<!DOCTYPE html>
<html>
<head>
<meta http-equiv="refresh" content="0; url=' || v_redirect_url || '">
</head>
<body><p>Redirecting...</p></body>
</html>');
EXCEPTION
WHEN OTHERS THEN
-- Even if everything explodes, send the user somewhere sensible
HTP.p('<!DOCTYPE html>
<html>
<head>
<meta http-equiv="refresh" content="0; url='
|| v_base || '?p=' || v_app_id
|| ':<your_failure_page_number>:0::NO::P_REF:UNKNOWN,P_ERR:HANDLER_ERROR">
</head>
<body><p>Redirecting...</p></body>
</html>');
END;
A few things to unpack here.
Why HTP.p instead of OWA_UTIL.REDIRECT_URL
You might expect to use OWA_UTIL.REDIRECT_URL to issue an HTTP 302 redirect. On a standard ORDS setup it works fine. On Oracle Autonomous Database (ADB), it doesn't. The redirect header gets dropped silently, no error, no warning, just nothing happens.
The fix is to emit an HTML page with a meta http-equiv="refresh" tag instead. Zero seconds means the browser redirects immediately. It's not the cleanest HTTP pattern, but it's the one that actually works reliably on ADB.
The Session Segment in the APEX URL
The APEX URL format is:
f?p=<app_id>:<page>:<session>:<request>:<debug>:<clear_cache>:<items>:<values>
The session segmentat position 3 is what APEX uses to tie the request to an existing session. When you store SYS_CONTEXT('APEX$SESSION', 'APP_SESSION') at initiation time and put it back in this segment at redirect time, APEX reconnects the user to their original session. Their authentication context, their application items, their navigation state is all of it is still there.
If you pass 0 here, APEX creates a new anonymous session. The user arrives on the page without any session context which is fine for a public-facing page, and completely broken for anything that requires authentication.
What Happens If the Session Has Expired
APEX sessions expire based on your session timeout setting. If the user took a very long time to complete payment, or the gateway took a long time to call back, the original session may be gone.
APEX handles this and it will redirect the user to your login page. For most applications that's acceptable. If your authentication is SSO or you need a smoother experience, you can set your success/failure pages to have public access and handle re-authentication there.
The EXCEPTION Block
The outer EXCEPTION WHEN OTHERS is not optional. If your package raises an unhandled exception like a DB connection issue, an unexpected null, anything without the exception block, ORDS returns a raw error response to the browser. The user sees a JSON error blob or a blank page. With the block, they at least land on your failure page and can try again or contact support.
Step 5: Reconciliation (For When Callbacks Don't Arrive)
Callbacks fail. Networks hiccup. Gateways have outages. If a callback never arrives, your record stays PENDING forever and the user's balance never gets credited.
The fix is a reconciliation procedure that polls the gateway's status API for any PENDING records older than a threshold.
PROCEDURE RECONCILE_PAYMENT (
p_payment_id IN NUMBER,
p_performed_by IN NUMBER,
p_result_code OUT NUMBER,
p_result_msg OUT VARCHAR2
) AS
v_payment PAYMENT_TRANSACTION%ROWTYPE;
v_api_key VARCHAR2(512);
v_api_url VARCHAR2(500);
v_secret_key VARCHAR2(512);
v_http_req UTL_HTTP.REQ;
v_http_resp UTL_HTTP.RESP;
v_buffer VARCHAR2(32767);
v_resp VARCHAR2(32767);
v_state VARCHAR2(64);
v_signature VARCHAR2(512);
BEGIN
SELECT * INTO v_payment
FROM PAYMENT_TRANSACTION
WHERE ID = p_payment_id;
IF v_payment.STATUS != 'PENDING' THEN
p_result_code := 3;
p_result_msg := 'Status is already: ' || v_payment.STATUS;
RETURN;
END IF;
IF v_payment.GATEWAY_TXN_ID IS NULL THEN
p_result_code := 2;
p_result_msg := 'No gateway transaction ID. Cannot reconcile.';
RETURN;
END IF;
SELECT API_KEY, API_BASE_URL, SECRET_KEY
INTO v_api_key, v_api_url, v_secret_key
FROM PAYMENT_GATEWAY_CONFIG
WHERE GATEWAY = 'BML';
BEGIN
v_http_req := UTL_HTTP.BEGIN_REQUEST(
url => v_api_url || '/public/transactions/' || v_payment.GATEWAY_TXN_ID,
method => 'GET'
);
UTL_HTTP.SET_HEADER(v_http_req, 'Authorization', v_api_key);
UTL_HTTP.SET_HEADER(v_http_req, 'Accept', 'application/json');
v_http_resp := UTL_HTTP.GET_RESPONSE(v_http_req);
v_resp := '';
BEGIN
LOOP
UTL_HTTP.READ_TEXT(v_http_resp, v_buffer, 32767);
v_resp := v_resp || v_buffer;
END LOOP;
EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(v_http_resp);
END;
APEX_JSON.PARSE(v_resp);
v_state := APEX_JSON.GET_VARCHAR2(p_path => 'state');
v_signature := APEX_JSON.GET_VARCHAR2(p_path => 'signature');
EXCEPTION WHEN OTHERS THEN
BEGIN UTL_HTTP.END_RESPONSE(v_http_resp);
EXCEPTION WHEN OTHERS THEN NULL;
END;
p_result_code := 4;
p_result_msg := 'Gateway status API error: ' || SQLERRM;
RETURN;
END;
IF v_state = 'CONFIRMED' THEN
APPLY_TOPUP_SUCCESS(
p_payment_id => p_payment_id,
p_gateway_txn_id => v_payment.GATEWAY_TXN_ID,
p_signature => v_signature,
p_raw_response => 'RECONCILED:' || SUBSTR(v_resp, 1, 3800)
);
INSERT INTO AUDIT_LOG (
ENTITY_TYPE, ENTITY_ID, ACTION,
PERFORMED_BY, PERFORMED_AT, NOTES
) VALUES (
'PAYMENT', p_payment_id, 'MANUAL_RECONCILE',
p_performed_by, SYSTIMESTAMP,
'Reconciled via gateway status API. State: ' || v_state
);
COMMIT;
p_result_code := 0;
p_result_msg := 'CONFIRMED — credited';
ELSIF v_state IN ('CANCELLED', 'REFUNDED') THEN
UPDATE PAYMENT_TRANSACTION
SET STATUS = 'CANCELLED',
COMPLETED_AT = SYSTIMESTAMP
WHERE ID = p_payment_id;
INSERT INTO AUDIT_LOG (
ENTITY_TYPE, ENTITY_ID, ACTION,
PERFORMED_BY, PERFORMED_AT, NOTES
) VALUES (
'PAYMENT', p_payment_id, 'MANUAL_RECONCILE',
p_performed_by, SYSTIMESTAMP,
'Reconciled. State: ' || v_state
);
COMMIT;
p_result_code := 0;
p_result_msg := v_state;
ELSE
p_result_code := 0;
p_result_msg := 'Still PENDING at gateway (state: ' || v_state || ')';
END IF;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
p_result_code := 4;
p_result_msg := SQLERRM;
END RECONCILE_PAYMENT;
Wire this up to a scheduled DBMS_SCHEDULER job that runs every 30 minutes, targeting any PENDING records older than 30 minutes with a gateway transaction ID:
BEGIN
FOR r IN (
SELECT ID FROM PAYMENT_TRANSACTION
WHERE STATUS = 'PENDING'
AND INITIATED_AT < SYSTIMESTAMP - NUMTODSINTERVAL(30, 'MINUTE')
AND GATEWAY_TXN_ID IS NOT NULL
) LOOP
BEGIN
RECONCILE_PAYMENT(
p_payment_id => r.ID,
p_performed_by => NULL,
p_result_code => v_result_code,
p_result_msg => v_result_msg
);
EXCEPTION WHEN OTHERS THEN
NULL; -- continue to the next record
END;
END LOOP;
END;
Things That Will Save You Time
Store everything raw. Log the raw response from the gateway against every payment record. When something goes wrong at 2am, you want the raw payload sitting in a column you can query. not a formatted message that lost half the detail.
Dual lookup is not optional. Some gateways send localId reliably, others drop it. Some send the gateway transaction ID first, others send localId first. Build the fallback lookup (local ref → gateway txn ID) from day one. You will need it.
Test your error page. Most developers test the happy path. Deliberately trigger a failed payment in UAT and make sure the user actually lands somewhere useful. A blank page on payment failure is almost as bad as a missing confirmation.
Your callback URL needs to be publicly accessible. If your ORDS instance is behind a firewall or VPN, the gateway can't reach it. Check this before you go to production test the URL with a tool like curl or Postman from outside your network.
Signature verification blocks replay attacks. An attacker who intercepts a callback URL can replay it with a modified state like changing CANCELLED to CONFIRMED, for example. The HMAC signature verification is what stops this. It's not optional for production.
Wrapping Up
The payment initiation is five lines and a redirect. The callback is where you earn your money. validation, session restoration, error handling, reconciliation. None of that appears in the tutorials.
The ORDS handler pattern gives you a controlled chokepoint: every callback from the gateway flows through your PL/SQL, gets validated, hits the database, and only then produces a redirect. The user's browser never sees the payment result directly! your code does, first.
If you're implementing this on ADB, remember: OWA_UTIL.REDIRECT_URL doesn't work (as per my understanding on the time of writing this) use HTP.p with a meta-refresh. If you're on a standard ORDS deployment, either approach works.
The reconciliation job is your safety net for when the callback simply never arrives. It won't happen often, but when it does, your users will not be the ones telling you their payment went through but their account didn't update.
Build the callback handling right once. It's not the exciting part of the feature, but it's the part that determines whether users trust your application with their money.