I want to consume an external API using APEX.
From what i understand, there are two approaches for this. One, create a REST data source for this using ORDS. Two, write embedded PL/SQL code using APEX_WEB_SERVICES or some other package.
I started with the ORDS approach. To test it, I tried linking it to an interactive grid on my app but the REST data source i made was not available in the list.
So I decide to troubleshoot it by executing a call using the REST data source in SQL Commands using APEX_EXEC. However, the APEX_EXEC package functions was not recognized in my schema. The package itself was not listed in the packages under that schema too.
The next step was to try making a simple WEB_SERVICES call to the API endpoint in general. It is working properly with Postman so it's not an endpoint issue.
This is the sandbox API endpoint: https://demo-ipg.ctdev.comtrust.ae:2443/
This is the sample request with valid values:
Headers: Content-Type:application/json
Accept:application/json
Request Body: {
"Registration”:
{ "Currency": "AED",
"ReturnPath": "https://localhost/callbackURL",
"TransactionHint": "CPT:Y;VCC:Y;",
"OrderID": "7210055701315195",
"Store": "0000",
"Terminal": "0000",
"Channel": "Web",
"Amount": "2.00",
"Customer": "Demo Merchant",
"OrderName": "Paybill",
"UserName":"Demo_fY9c",
"Password":"Comtrust@20182018"
} }
This is the sample expected response:
{
"Transaction":{
"PaymentPortal":"https://demo/ ipg.comtrust.ae/PaymentEx/Paymentpartner/Payment?lang=en&layout=C0STCBVLEI",
"PaymentPage":"https://demo/ ipg.comtrust.ae/PaymentEx/Paymentpartner/Payment?lang=en&layout=C0STCBVLEI",
"ResponseCode":"0",
"ResponseClass":"0",
"ResponseDescription":"Request Processed Successfully", "ResponseClassDescription":"Success",
"TransactionID":"847718745846",
"Balance":{"Value":"0" },
"Amount":{"Value":"0" },
"Fees":{"Value":"0" },
"Payer":null,
"UniqueID":"a25ea7da-a212-406a-967b-94953191aad7" } }
This is the code I used for trying to reach the API endpoint.
DECLARE
l_url VARCHAR2(4000) := 'https://demo-ipg.ctdev.comtrust.ae:2443/'; l_request_body CLOB;
l_response CLOB;
l_status_code NUMBER;
BEGIN
l_request_body := '{ "Registration": { "Currency": "AED", "ReturnPath": "https://localhost/callbackURL", "TransactionHint": "CPT:Y;VCC:Y;", "OrderID": "7210055701315195", "Store": "0000", "Terminal": "0000", "Channel": "Web", "Amount": "2.00", "Customer": "Demo Merchant", "OrderName": "Paybill", "UserName": "Demo_fY9c", "Password": "Comtrust@20182018" } }';
l_response := apex_exec.make_rest_request( p_url => l_url, p_http_method => 'POST', p_body => l_request_body, p_content_type => 'application/json', p_accept => 'application/json' );
l_status_code := apex_exec.g_http_status_code;
DBMS_OUTPUT.put_line('HTTP Status Code: ' || l_status_code); DBMS_OUTPUT.put_line('Response Body: ' || l_response);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
END;
/
But this was not working either.
This implied two things to my beginner understanding: either it's an access issue because i am using an Always Free tier APEX instance on OCI. Or I missed some key and basic configuration step to map privileges from INTERNAL to my newly created workspace.
To check the first possibility, I tried it on a Developer tier instance and paid instance. No luck. Also I tried whitelisting the hostname of my API endpoint in the ACL but the ACL package itself was not available. When I try using the URL_HTTP package to make these calls it shows insufficient privileges.
Which implies there is some key configuration step I missed probably while creating a new workspace. Essentially I would just create these instances, open a new workspace and run these commands directly. Maybe i missed something basic? RESTful Services and Database Actions, etc. are obviously checked under tool configuration while creating the instance. But something else maybe?
This problem has been bugging me for a significant amount of time now so any and all help is appreciated. Thank you for reading.