Managing Database Functions in SAP Agent Lifecycle Management

[KameshwarNukala]

Introduction

SAP Agent Lifecycle Management (ALM) is the central platform for managing all agent and producer-related configurations within the system. Beyond contract and licensing workflows, ALM provides a built-in Database Function feature that allows teams to author, manage, and synchronise SAP HANA SQLScript functions directly from the application — without needing access to the database layer.

This post walks through the end-to-end process of creating a Database Function in ALM, explains the required SQL format, and highlights the key advantages over maintaining functions directly in the database.

Why Manage Database Functions in ALM?

The traditional approach stores HANA functions as separate scripts in source repositories or directly in the database, with no visibility from the application layer. ALM changes this by treating Database Functions as first-class application objects.

Single Point of Code Maintenance

Every function lives inside ALM alongside the business configuration that uses it. There is one place to open, edit, and version the logic — no jumping between HANA Studio, DBeaver, and a Git repository to trace a change.

• No duplicate scripts across environments — ALM is the source of truth.
• Changes are visible to the full team through the ALM audit trail.
• System Status (Active / Inactive) controls which version is live.
• In Sync? flag immediately shows whether the DB reflects the latest ALM definition.

Security

ALM-managed functions run under SQL SECURITY INVOKER, meaning the function executes with the privileges of the calling user rather than the function owner. This enforces least-privilege access and prevents privilege escalation.

• Access control is inherited from the caller — no hidden elevated privileges.
• Reduces the blast radius of misconfigured schema-level grants.
• Consistent with SAP security best practices for HANA SQLScript.

Operational Visibility

• In Sync? status instantly highlights any drift between ALM and the live database.
• Synchronize Database applies the change in one click — no manual DDL scripts.
• Drop action removes the function cleanly from both ALM and the database together.

The ALM SQL Text Format

ALM stores only the function body in the SQL Text field — it constructs and applies the full CREATE OR REPLACE FUNCTION statement automatically using the Name field as the function identifier. This is the key structural difference from a standard HANA function script.

Element	Standard HANA Script	ALM SQL Text Field
Function header	CREATE OR REPLACE FUNCTION schema.name (...)	Omitted — ALM adds it using the Name field
Parameters	IN param_name TYPE	param_name TYPE (no IN keyword)
Return type	RETURNS ret_val INTEGER	RETURNS TABLE (col_name TYPE)
Security	SQL SECURITY DEFINER (typical)	SQL SECURITY INVOKER (mandatory)
Return statement	ret_val := value	RETURN SELECT :var AS col FROM DUMMY
Trailing semicolon	END;	END (no semicolon)

SQL Text Template

(param_one INTEGER, param_two DATE)
RETURNS TABLE (ret_val INTEGER)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE v_var    NVARCHAR(50);
    DECLARE v_return INTEGER := -1;   -- safe default

    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
        SELECT col INTO v_var
          FROM schema.table
         WHERE pk_col = :param_one;
        v_return := 0;                -- success path
    END;

    RETURN SELECT :v_return AS ret_val FROM DUMMY;
END

Important: Because RETURNS TABLE is required by ALM, the function must be called with SELECT * FROM schema.function_name(params) — not SELECT func() FROM DUMMY. See the Calling the Function section below.

Exception Handling in HANA SQLScript

A common migration issue from Oracle PL/SQL to HANA SQLScript is handling no-data-found conditions. In Oracle, WHEN NO_DATA_FOUND catches a SELECT INTO that returns zero rows. In HANA, the equivalent is error code 1299, which must be caught using a scoped inner block.

Wrapping each vulnerable SELECT INTO in its own BEGIN...END block with a scoped handler prevents the 1299 from reaching the outer EXIT HANDLER and being re-raised as an unintended hard error.

Handler	Scope	Catches	Action
EXIT HANDLER FOR SQLEXCEPTION	Outer block	Any unexpected SQL error	Resignal as application error 10002
CONTINUE HANDLER FOR SQL_ERROR_CODE 1299	Inner block	No row returned (no data found)	Set safe default, continue execution

BEGIN                                           -- outer block
    DECLARE v_return INTEGER := -1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        RESIGNAL SQL_ERROR_CODE 10002
            SET MESSAGE_TEXT = 'Error: ' || ::SQL_ERROR_MESSAGE;
    END;

    BEGIN                                       -- inner block (scoped handler)
        DECLARE CONTINUE HANDLER FOR SQL_ERROR_CODE 1299
        BEGIN
            v_return := -1;                     -- no row found: keep default
        END;
        SELECT col INTO v_var
          FROM schema.table
         WHERE pk_col = :in_param;
        v_return := 0;                          -- only reached on success
    END;

    RETURN SELECT :v_return AS ret_val FROM DUMMY;
END

Real-World Example — SAMPLE_DB_FUNCTION

The function below checks whether a given ID has an active, valid status on a specific date. It returns 0 on success and -1 on any error or no-data-found condition.

(in_id BIGINT, in_date DATE)
RETURNS TABLE (ret_val INTEGER)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE v_type   NVARCHAR(50);
    DECLARE v_return INTEGER := -1;

    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
        SELECT T.STATUS INTO v_type
          FROM SCHEMA.MY_TABLE T
         WHERE T.STATUS IN ('TypeA', 'TypeB', 'TypeC')
           AND TO_INTEGER(TO_VARCHAR(:in_date, 'YYYYMMDD'))
               BETWEEN T.DATEEFF AND T.DATEEXP
           AND T.STATUS_FLAG = 1
           AND T.ID          = :in_id
         LIMIT 1;
        v_return := 0;
    END;

    RETURN SELECT :v_return AS ret_val FROM DUMMY;
END

Step-by-Step: Creating a DB Function in ALM

1. Navigate to Database Function — Open ALM and select the Database Function module from the left navigation menu.
2. Create a new record — Click the + (Add) button in the list toolbar to open a new Database Function form.
3. Fill in the Name field — Enter the function name exactly as it should appear in the database schema (e.g. SAMPLE_DB_FUNCTION). ALM uses this as the function identifier when it runs CREATE OR REPLACE FUNCTION.
4. Paste the SQL Text — Enter the function body in ALM format — starting with (parameters), followed by RETURNS TABLE, LANGUAGE SQLSCRIPT, SQL SECURITY INVOKER, AS, BEGIN...END. Do not include CREATE FUNCTION or a schema prefix.
5. Set System Status to Active — An Inactive function will not be deployed to the database. Set the status to Active before saving.
6. Save the record — The In Sync? field will show Not In Sync — the function definition exists in ALM but has not yet been applied to the database.
7. Run Synchronize Database — Select the record in the list view and click the Synchronize Database button in the toolbar. ALM generates and executes the full CREATE OR REPLACE FUNCTION DDL against the HANA database. In Sync? changes to In Sync once completed successfully.

Synchronize Database — What It Does

The Synchronize Database action is the bridge between the ALM definition and the live HANA database. It must be run every time a function is created or modified in ALM.

Scenario	Before Sync	After Sync
New function created in ALM	Not In Sync — function does not exist in DB	In Sync — function created in DB
Existing function edited in ALM	Not In Sync — DB has old version	In Sync — DB updated to latest version
Function activated after reopen	Not In Sync — status changed in ALM only	In Sync — DB reflects active definition
Drop action executed	N/A	Function removed from both ALM and DB

Tip: Always run Synchronize Database immediately after saving any change. The In Sync? column in the list view makes it easy to spot any function that has drifted from the database.

Calling the Function

Because ALM functions use RETURNS TABLE, they are table-valued functions in HANA. Table-valued functions must be referenced in the FROM clause — they cannot appear in the SELECT column list like scalar functions.

Syntax	Works?	Reason
SELECT schema.func(params) FROM DUMMY	No	Table function cannot appear in SELECT column list
SELECT * FROM schema.func(params)	Yes	Correct syntax for table-valued functions
SELECT col FROM schema.func(params)	Yes	Returns only the named column
Number without quotes: func(123, ...)	Yes	BIGINT parameters must be numeric literals, not strings
Date as string: func(..., '20261110')	Yes	HANA implicitly casts YYYYMMDD string to DATE

-- Correct: number literal + HANA implicit date cast
SELECT * FROM SCHEMA.SAMPLE_DB_FUNCTION(100000001, '20261110');

-- Returns:
-- | ret_val |
-- |    0    |   producer found and valid
-- |   -1    |   not found or error

Summary

ALM Database Functions deliver three core advantages over standalone HANA scripts:

• Single point of maintenance — all logic lives in ALM, versioned and visible to the team, with no separate script files to manage across environments.
• Security — SQL SECURITY INVOKER ensures least-privilege execution on every call, with no hidden elevated permissions.
• Operational visibility — the In Sync? flag and Synchronize Database action close the gap between definition and deployment in one click.

By adopting ALM as the single source of truth for Database Functions, teams reduce deployment risk, improve code traceability, and enforce consistent security standards across all environments.