Implementing Oracle Autonomous Transactions in Azure SQL for Seamless Logging

In Oracle PL/SQL, the directive PRAGMA AUTONOMOUS_TRANSACTION allows a block of code such as a procedure, function, or trigger to run in its own independent transaction. This means:

• It can commit or rollback changes without affecting the main transaction.
• It is fully isolated from the calling transactions, no shared locks or dependencies.
• It is ideal for logging, auditing, and error tracking, where you want to preserve data even if the main transaction fails.

Example in Oracle PL/SQL:

CREATE OR REPLACE PROCEDURE log_error(p_message VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (log_time, message) VALUES (SYSDATE, p_message);
  COMMIT;
END;

This ensures that the error log is saved even if the main transaction rolls back.

How to Implement Autonomous Transactions in Azure SQL

If you need logs that persist through rollbacks in Azure SQL, you can recreate the behavior of Oracle’s PRAGMA AUTONOMOUS_TRANSACTION using external logging via Azure Function 

• External logging via sp_invoke_external_rest_endpoint to an Azure Function 

This blog post shows concrete code for Oracle PL/SQL vs. T‑SQL, and provides a secure, sample Azure Function example.

• There’s no direct PRAGMA AUTONOMOUS_TRANSACTION in Azure SQL as on the date when this blog is written.
• To persist logs even if the current transaction rolls back, consider calling an external logger (e.g., Azure Function) using sp_invoke_external_rest_endpoint.

Oracle vs. Azure SQL at a glance

External logging via Azure Function and sp_invoke_external_rest_endpoint

In SQL use this when the application might wrap your stored procedure in its own transaction. If the app rolls back after your proc finishes, any in-database transactions (including your logs) also roll back. An external logger runs outside the database transaction and remains durable.

Prerequisites and notes

• sp_invoke_external_rest_endpoint is available in Azure SQL Database and Azure SQL Managed Instance. Ensure outbound network access to your Function endpoint.
• Prefer Azure AD (Managed Identity) auth for your Function endpoint. If you must use function keys, pass them via header (x-functions-key), not query strings.
• Set reasonable timeouts and add retry/backoff for transient failures.

Minimal SQL prerequisites for Function App Managed Identity

Grant your Function App’s managed identity access to the database and tables:

-- In your Azure SQL Database (connected as an Entra admin) 
CREATE USER [<FUNCTION_APP_MI_NAME>] FROM EXTERNAL PROVIDER; 
ALTER ROLE db_datawriter ADD MEMBER [<FUNCTION_APP_MI_NAME>]; 
-- Or grant explicit permissions 
GRANT INSERT ON dbo.ErrorLogs TO [<FUNCTION_APP_MI_NAME>];

T‑SQL: emit a log via REST

CREATE OR ALTER PROCEDURE dbo.add_numbers
    @x INT,
    @y INT,
     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Log process start
        INSERT INTO dbo.ProcessLogs (LogMessage, ProcedureName)
        VALUES (CONCAT('Started adding numbers: ', @x, ' + ', @y), 'add_numbers');

        -- Add numbers
        SET  = @x + @y;

        -- Log process success
        INSERT INTO dbo.ProcessLogs (LogMessage, ProcedureName)
        VALUES (CONCAT('Successfully calculated sum = ', ), 'add_numbers');
    END TRY

    BEGIN CATCH
        -- Log error details locally
        INSERT INTO dbo.ErrorLogs (ErrorMessage, ErrorProcedure, ErrorLine)
        VALUES (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE());

        -- Prepare JSON payload
        DECLARE @jsonPayload NVARCHAR(MAX) = N'{
            "LogMessage": "Some error occurred",
            "ProcedureName": "add_numbers",
            "ErrorMessage": "' + ERROR_MESSAGE() + N'"
        }';

        -- Call external REST endpoint (Azure Function)
        EXEC sp_invoke_external_rest_endpoint
            @method = 'POST',
            @url = 'https://yourfunctionapp.azurewebsites.net/api/WriteLog',
            @headers = '{"Content-Type": "application/json", "x-functions-key": "<FUNCTION_KEY>"}',
             = @jsonPayload;

        -- Re-throw original error to caller
        THROW;
    END CATCH;
END;
GO

Azure Function: HTTP-triggered logger (C#)

• Auth: use AuthorizationLevel.Function (or Azure AD), avoid Anonymous in production.
• DB access: use Managed Identity User to request an access token for Azure SQL. Once the request token is generated, next step is to insert logs/messages into a permanent log/error table.

Below is an end-to-end Function App code in C# which demonstrates above.

using Azure.Core;
using Azure.Identity;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using System;
using System.IO;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;

namespace FunctionApp_MI_HTTPRequest
{
    public static class ManagedInstance_Http_Request
    {
        private static readonly string SqlConnectionString = Environment.GetEnvironmentVariable("SqlConnectionString");

        public class ProcedureLog
        {
            public string ProcedureName { get; set; }
            public string LogMessage { get; set; }
        }

        [FunctionName("WriteLog")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "post")] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("WriteLog invoked.");

            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            log.LogInformation($"Received Log Request: {requestBody}");

            ProcedureLog logData;

            try
            {
                logData = JsonSerializer.Deserialize<ProcedureLog>(requestBody, new JsonSerializerOptions
                {
                    PropertyNameCaseInsensitive = true
                });

                if (logData == null || string.IsNullOrWhiteSpace(logData.ProcedureName))
                {
                    return new BadRequestObjectResult(new
                    {
                        status = "Error",
                        message = "Invalid log data."
                    });
                }
            }
            catch (JsonException)
            {
                return new BadRequestObjectResult(new
                {
                    status = "Error",
                    message = "Invalid JSON format."
                });
            }

            try
            {
                // Acquire access token for Azure SQL using Managed Identity
                var tokenCredential = new DefaultAzureCredential();
                var accessToken = await tokenCredential.GetTokenAsync(
                    new TokenRequestContext(new[] { "https://database.windows.net/.default" }),
                    CancellationToken.None);

                using var conn = new SqlConnection(SqlConnectionString)
                {
                    AccessToken = accessToken.Token
                };

                await conn.OpenAsync();
                using var transaction = conn.BeginTransaction();

                var query = @"
                    INSERT INTO dbo.ErrorLogs (ErrorMessage, ErrorProcedure)
                    VALUES (@LogMessage, @ProcedureName);";

                try
                {
                    using var cmd = new SqlCommand(query, conn, transaction);
                    cmd.Parameters.AddWithValue("@LogMessage", (object?)logData.LogMessage ?? string.Empty);
                    cmd.Parameters.AddWithValue("@ProcedureName", logData.ProcedureName);

                    await cmd.ExecuteNonQueryAsync();
                    transaction.Commit();

                    log.LogInformation($"Log inserted: {logData.ProcedureName} | {logData.LogMessage}");

                    return new OkObjectResult(new
                    {
                        status = "Success",
                        message = "Log inserted successfully."
                    });
                }
                catch (SqlException ex)
                {
                    transaction.Rollback();
                    log.LogError(ex, "Database transaction failed.");

                    return new ObjectResult(new
                    {
                        status = "Error",
                        message = "Database transaction failed."
                    })
                    { StatusCode = 500 };
                }
            }
            catch (Exception ex)
            {
                log.LogError(ex, "Internal Server Error");

                return new ObjectResult(new
                {
                    status = "Error",
                    message = "Internal Server Error"
                })
                { StatusCode = 500 };
            }
        }
    }
}

Calling the Function from T‑SQL

Header-based key (preferred):

DECLARE @jsonPayload NVARCHAR(MAX) = N'{
    "LogMessage": "Some error occurred",
    "ProcedureName": "ProcessData"
}';

EXEC sp_invoke_external_rest_endpoint
    @method  = 'POST',
    @url     = 'https://yourfunctionapp.azurewebsites.net/api/WriteLog',
    @headers = '{"Content-Type": "application/json", "x-functions-key": "<FUNCTION_KEY>"}',
     = @jsonPayload;

CREATE TABLE dbo.ErrorLogs
(
    Id             INT IDENTITY(1,1) PRIMARY KEY,
    ErrorMessage   NVARCHAR(MAX),
    ErrorProcedure NVARCHAR(255),
    ErrorLine      INT NULL,
    ErrorDateTime  DATETIME2 DEFAULT SYSDATETIME()
);
GO

CREATE TABLE dbo.ProcessLogs
(
    Id             INT IDENTITY(1,1) PRIMARY KEY,
    LogMessage     NVARCHAR(MAX),
    ProcedureName  NVARCHAR(255),
    LogDateTime    DATETIME2 DEFAULT SYSDATETIME()
);
GO

Architecture diagram

PL/SQL reference (Oracle)

For comparison, here’s the autonomous transaction pattern in Oracle. Note the autonomous pragma on the logger procedure.

-- =========================================
-- Table: ERRORLOGS
-- =========================================
CREATE TABLE errorlogs (
    log_id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    procedure_nm VARCHAR2(100),
    error_msg    VARCHAR2(4000),
    log_time     TIMESTAMP DEFAULT SYSTIMESTAMP
);
/

-- =========================================
-- Procedure: LOG_ERROR (Autonomous Transaction Logger)
-- =========================================
CREATE OR REPLACE PROCEDURE log_error (
    p_procedure VARCHAR2,
    p_error     VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO errorlogs (procedure_nm, error_msg)
    VALUES (p_procedure, p_error);

    COMMIT;
END;
/
-- =========================================
-- Procedure: ADD_NUMBERS (Sample Procedure)
-- =========================================
CREATE OR REPLACE PROCEDURE add_numbers (
    p_x   NUMBER,
    p_y   NUMBER,
    p_sum OUT NUMBER
) IS
BEGIN
    p_sum := p_x + p_y;
EXCEPTION
    WHEN OTHERS THEN
        log_error('add_numbers', SQLERRM);
        RAISE;
END;
/

Troubleshooting and FAQ - 

Why aren’t my logs saved after a rollback in Azure SQL? 

• Inserts performed inside the same transaction are rolled back together. Use external logging via 'sp_invoke_external_rest_endpoint' to guarantee persistence regardless of the caller’s transaction.

Can I use CLR or linked servers for autonomous transactions in Azure SQL?

• No. CLR integration and linked servers for DML aren’t supported in Azure SQL Database. Prefer external REST endpoints.

What are the security considerations for external logging?

• Avoid 'Anonymous' Functions. Prefer Azure AD; second-best is 'AuthorizationLevel.Function' with keys in headers. Validate payloads and avoid logging sensitive data unless encrypted. Store secrets in Key Vault.

How do I monitor and validate the logging pipeline?

• Use Application Insights in your Function. Emit structured logs for both success and failure. Configure alerts on failure rates and latency.

What performance impact should I expect?

•  External calls add network latency. For high-volume scenarios, batch logs, use async fire-and-forget patterns on the app side, and set sensible timeouts.

Summary

In summary, external logging offers durable visibility across services, making it ideal for distributed systems. Be sure to implement retries and validation, set up monitoring and alerts, and document the behavior clearly for your team to ensure everyone understands the trade-offs involved.

If you have any feedback or suggestion, please reach out to us at datasqlninja@microsoft.com