Unable to connect to Oracle database from Azure Function in VS Code

Pattar, Mounesh SOMIBM-IBM 0 Reputation points
2024-12-24T10:56:22.7733333+00:00

I am trying to connect to an on-premises Oracle database from an Azure Function using Visual Studio Code. I have installed the necessary packages (Oracle.ManagedDataAccess, System.Configuration.ConfigurationManager, System.Security.Permissions) and configured my connection string as follows:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XYZ)(PORT=1648))(CONNECT_DATA=(SERVICE_NAME=XYZ)));User ID=xyz;Password=XYZ;

However, I am encountering a connection timeout error (ORA-50000: Connection request timed out). I have verified that the host is up and running, and I can connect to the database using the Oracle SQL Developer extension in VS Code.

Code :

using Microsoft.AspNetCore.Http;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Azure.Functions.Worker;

using Microsoft.Extensions.Logging;

using Oracle.ManagedDataAccess.Client;

using System.Data;

namespace Company.Function

{

public class HttpTrigger1

{

    private readonly ILogger<HttpTrigger1> _logger;

    public HttpTrigger1(ILogger<HttpTrigger1> logger)

    {

        _logger = logger;

    }

    [Function("HttpTrigger1")]

    public IActionResult Run([HttpTrigger(AuthorizationLevel.Function, "get", "post")] HttpRequest req)

    {

        _logger.LogInformation("C# HTTP trigger function processed a request.");

        string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XYZ)(PORT=1648))(CONNECT_DATA=(SERVICE_NAME=XYZ)));User Id=XYZ;Password=XYZ;Validate Connection=true;";

        using (var conn = new OracleConnection(connectionString))

        {

            try

            {

                conn.Open();

                _logger.LogInformation("Connected to Oracle database.");

                using (var cmd = new OracleCommand("SELECT * FROM dual", conn))

                {

                    using (var reader = cmd.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            string result = reader.GetString(0);

                            _logger.LogInformation($"Query Result: {result}");

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                _logger.LogError($"Error connecting to Oracle database: {ex.Message}");

                return new StatusCodeResult(StatusCodes.Status500InternalServerError);

            }

        }

        return new OkObjectResult("Oracle database query executed successfully.");

    }

}

}

Could you please help me troubleshoot this issue?

Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
5,253 questions
{count} votes

Accepted answer
  1. Pinaki Ghatak 5,310 Reputation points Microsoft Employee
    2024-12-25T09:50:52.6733333+00:00

    Hello @Pattar, Mounesh SOMIBM-IBM

    The error message ORA-50000: Connection request timed out indicates that the connection request to the database has timed out. This could be due to a number of reasons such as network issues, firewall settings, or incorrect connection string parameters.

    Here are a 5-steps you can try to troubleshoot the issue:

    1. Check the network connectivity between the Azure Function and the on-premises Oracle database. Ensure that there are no network issues or firewall settings that are blocking the connection.
    2. Verify that the connection string parameters are correct. Ensure that the hostname, port number, and service name are correct and match the values in the tnsnames.ora file on the machine where the Oracle database is installed.
    3. Try increasing the connection timeout value in the connection string. You can do this by adding the Connection Timeout parameter to the connection string and setting its value to a higher value (in seconds).
    4. Check the Oracle database logs for any errors or warnings related to the connection request.
    5. Try connecting to the Oracle database using a different tool or client to verify that the connection works.

    I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.