📋

Description

The ORA-29266 error indicates that the HTTP response body ended prematurely during an HTTP request within Oracle Database. This commonly occurs when using packages like `UTL_HTTP` to interact with web services or APIs.

💬

Error Message

ORA-29266: end-of-body reached

🔍

Known Causes

4 known causes

⚠️

Incomplete HTTP Response

The server sending the HTTP response may have terminated the connection or sent an incomplete response due to errors or timeouts.

⚠️

Network Interruption

A network issue could have interrupted the data transfer, causing the client (Oracle Database) to receive an incomplete response.

⚠️

Server-Side Error

The remote server encountered an internal error and failed to send the complete HTTP response body.

⚠️

UTL_HTTP Timeout

The `UTL_HTTP` call may have timed out before receiving the full response, particularly if the server is slow to respond.

🛠️

Solutions

4 solutions available

1. Verify Network Connectivity and Firewall Rules easy

Ensure the Oracle database server can reach the target HTTP server and that no firewalls are blocking the connection.

1

From the Oracle database server, attempt to connect to the target HTTP server using a tool like `curl` or `wget`. This will help identify basic network reachability issues.

curl -I <target_http_server_url>:<port>

2

Check firewall rules on the Oracle database server, the target HTTP server, and any intermediate network devices. Ensure that outbound connections from the Oracle server to the target HTTP server on the specified port are allowed.

3

If using Oracle Database 12c or later, verify the `UTL_HTTP` `CONNECT_TIMEOUT` and `SEND_TIMEOUT` parameters. These might be too short, causing premature connection termination.

SELECT parameter, value FROM v$parameter WHERE name LIKE 'utl_http%' AND name LIKE '%timeout%';

4

If timeouts are too short, consider increasing them using `ALTER SYSTEM SET` (requires DBA privileges).

ALTER SYSTEM SET "utl_http.connect_timeout" = 60; -- Set to 60 seconds (adjust as needed)
ALTER SYSTEM SET "utl_http.send_timeout" = 60; -- Set to 60 seconds (adjust as needed)

2. Examine HTTP Server Response and Content Length medium

The HTTP server might be closing the connection prematurely or not sending the expected response body.

1

On the HTTP server hosting the target endpoint, review its logs for any errors or unusual activity that might precede the premature closure of the connection. Look for messages related to request processing, resource exhaustion, or unexpected errors.

2

Ensure the HTTP server is correctly sending a `Content-Length` header if it's expecting the Oracle client to read a specific amount of data. If the `Content-Length` is present but the actual body is shorter, or if it's absent when it should be, `ORA-29266` can occur.

3

If the HTTP server is using chunked transfer encoding, ensure it's correctly formatted. Oracle's `UTL_HTTP` might have issues with malformed chunked responses.

4

Test the HTTP endpoint independently using tools like `curl` or Postman from a machine that can access it. Observe the response headers and body. If these tools also encounter issues, the problem lies with the HTTP server.

curl -v <target_http_server_url>:<port>/<your_endpoint>

3. Review and Debug Oracle UTL_HTTP Code medium

Inspect the PL/SQL code that uses `UTL_HTTP` for potential issues in how it handles responses.

1

Add detailed logging within your PL/SQL code to capture the status codes, headers, and the amount of data read from the HTTP response. This can help pinpoint where the premature end is occurring.

DECLARE
  req   UTL_HTTP.req;
  resp  UTL_HTTP.resp;
  buffer VARCHAR2(32767);
  bytes_read NUMBER := 0;
BEGIN
  req := UTL_HTTP.begin_request('http://your.target.server:port/endpoint', 'GET');
  resp := UTL_HTTP.get_response(req);

  DBMS_OUTPUT.PUT_LINE('Status Code: ' || resp.status_code);
  FOR i IN 1 .. UTL_HTTP.get_header_count(resp) LOOP
    DBMS_OUTPUT.PUT_LINE('Header: ' || UTL_HTTP.get_header_name(resp, i) || ': ' || UTL_HTTP.get_header_value(resp, i));
  END LOOP;

  BEGIN
    LOOP
      UTL_HTTP.read_text(resp, buffer, 32767);
      bytes_read := bytes_read + LENGTH(buffer);
      DBMS_OUTPUT.PUT_LINE('Read ' || LENGTH(buffer) || ' bytes. Total read: ' || bytes_read);
      -- Process buffer content here
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      DBMS_OUTPUT.PUT_LINE('End of body reached. Total bytes read: ' || bytes_read);
  END;

  UTL_HTTP.end_response(resp);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    IF resp IS NOT NULL THEN
      UTL_HTTP.end_response(resp);
    END IF;
    RAISE;
END;
/

2

Ensure that `UTL_HTTP.read_text` (or `read_raw`) is called repeatedly until the `UTL_HTTP.end_of_body` exception is caught. Missing this loop or not handling the exception correctly can lead to this error.

LOOP
  UTL_HTTP.read_text(resp, buffer, 32767);
  -- Process buffer
END LOOP;
EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    NULL; -- Expected exception, indicates end of data
END;

3

Check if you are explicitly closing the response or connection prematurely in your code. The `UTL_HTTP.end_response(resp);` call should be made only after all data has been read.

4. Configure Oracle Wallet for HTTPS medium

If the target is an HTTPS endpoint, ensure the Oracle Wallet is properly configured and contains the necessary certificates.

1

If you are making HTTPS requests, ensure that an Oracle Wallet is created and configured on the database server. This wallet should contain the trusted root certificates of the Certificate Authority (CA) that issued the server's SSL certificate.

2

Set the `ssl_server_cert_dn` parameter in the `sqlnet.ora` file to point to the location of your Oracle Wallet.

SSL_SERVER_CERT_DN = "CN=your_wallet_alias, OU=..., O=..., L=..., ST=..., C=..."

3

Use `ORAPKI` utility to import the necessary certificates into the wallet.

orapki wallet create -wallet /path/to/your/wallet -auto_login
orapki wallet add_trusted_cert -wallet /path/to/your/wallet -cert /path/to/your/certificate.cer

4

Ensure the PL/SQL code correctly specifies the HTTPS URL and that the wallet is accessible to the database process.

UTL_HTTP.begin_request('https://your.secure.server:port/endpoint', 'GET');

Oracle Error ORA-29266: Premature HTTP End