To call an external API through Oracle 19c database using the POST method with JSON
data in the body, you can make use of Oracle's PL/SQL language and the UTL_HTTP
package. Here's how you can do it:
1. **Enable UTL_HTTP Package:**
Make sure the `UTL_HTTP` package is enabled in your Oracle database. You can
check its status using the following SQL query:
```sql
SELECT value FROM v$parameter WHERE name = 'utl_http.request_max_size';
```
If the value is `0`, then the package is disabled. You can enable it by
executing the following SQL command as a DBA user:
```sql
ALTER SYSTEM SET utl_http.request_max_size = 1024;
```
2. **Write a PL/SQL Procedure:**
Create a PL/SQL procedure that uses the `UTL_HTTP` package to send the POST
request with JSON data to the external API. Here's an example procedure:
```sql
CREATE OR REPLACE PROCEDURE CALL_EXTERNAL_API IS
req UTL_HTTP.req;
res UTL_HTTP.resp;
url VARCHAR2(4000) := 'YOUR_API_URL_HERE';
json_data VARCHAR2(4000) := '{"key": "value", "key2": "value2"}';
response CLOB;
BEGIN
-- Open the request
req := UTL_HTTP.begin_request(url, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
-- Set request headers
UTL_HTTP.set_header(req, 'Content-Type', 'application/json');
-- Write JSON data to the request
UTL_HTTP.write_text(req, json_data);
-- Get the response
res := UTL_HTTP.get_response(req);
-- Read the response
LOOP
UTL_HTTP.read_text(res, response);
DBMS_OUTPUT.put_line(response);
END LOOP;
-- Close the response
UTL_HTTP.end_response(res);
EXCEPTION
WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
-- Handle the exception
END CALL_EXTERNAL_API;
/
```
Replace `'YOUR_API_URL_HERE'` with the actual URL of the external API and modify
the `json_data` accordingly.
3. **Execute the Procedure:**
After creating the procedure, you can execute it using SQL Developer or any
other Oracle client. Make sure you have necessary privileges to execute the
procedure and access the UTL_HTTP package.
