TL;DR
PL/SQL is procedural (loops, cursor iterations, exception blocks). HANA SQL Script is set-based (bulk operations, table variables). The rewrite requires a mindset shift, not just syntax changes.
The biggest rewrite: cursor loops (row-by-row processing) become table variable operations (bulk insert/update). This is where most migration time is spent.
PL/SQL packages don't exist in HANA. Decompose packages into individual procedures and organize them by schema/namespace.

Why Procedural Code Matters

Callidus Commissions relies on PL/SQL for complex calculations: commission aggregation, tier assignments, payout processing. These are business logic engines, written as packages with interdependent procedures. Moving them to HANA requires understanding not just syntax, but the architectural shift from row-oriented to set-based operations.

Structure Comparison: Blocks and Declarations

AspectOracle Database PL/SQLSAP HANA Database SQL Script
Block structureDECLARE ... BEGIN ... EXCEPTION ... END;BEGIN ... END; (no DECLARE section)
Variable declarationIn DECLARE block before BEGINDECLARE inside BEGIN...END
Variable type%TYPE, %ROWTYPE attributesMust specify inline (no attributes)
Local proceduresNested procedures (PL/SQL blocks)Not supported — use separate procedures
PackagePackage spec + body (grouping, globals)Not supported — use individual procedures

Block Structure Example

Oracle PL/SQL
DECLARE
  v_total      NUMBER(10,2);
  v_count      INTEGER;
  v_start_date DATE;
BEGIN
  v_start_date := TRUNC(SYSDATE);
  SELECT SUM(amount), COUNT(*)
  INTO v_total, v_count
  FROM cs_commissions
  WHERE created_date >= v_start_date;

  DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data');
END;
/
SAP HANA SQL Script
BEGIN
  DECLARE v_total DECIMAL(10,2) DEFAULT 0;
  DECLARE v_count INTEGER DEFAULT 0;
  DECLARE v_start_date DATE;

  SET v_start_date = CURRENT_DATE;
  SELECT SUM(amount), COUNT(*)
  INTO v_total, v_count
  FROM cs_commissions
  WHERE created_date >= v_start_date;

  -- No DBMS_OUTPUT equivalent; use CALL to procedure or return output via OUT parameter

  -- Exception handling is different (see below)
END;
/

The Big Rewrite: Cursor Loops → Table Variables

This is where 80% of migration effort goes. Cursor loops are row-by-row processing (slow in HANA). Table variables are bulk operations (fast).

Oracle Pattern: Cursor Loop

Oracle PL/SQL — Row-by-row cursor loop
DECLARE
  CURSOR c_trans IS
    SELECT id, salesperson_id, commission_amount
    FROM cs_transactions
    WHERE status = 'APPROVED';

  v_trans c_trans%ROWTYPE;
BEGIN
  OPEN c_trans;
  LOOP
    FETCH c_trans INTO v_trans;
    EXIT WHEN c_trans%NOTFOUND;

    -- Process each row
    INSERT INTO payout_schedule
    VALUES (v_trans.id, v_trans.salesperson_id, v_trans.commission_amount);
  END LOOP;
  CLOSE c_trans;
END;
/

HANA Pattern: Table Variable (Bulk Operation)

SAP HANA SQL Script — Set-based table variable
BEGIN
  DECLARE TABLE v_trans (
    id INTEGER,
    salesperson_id INTEGER,
    commission_amount DECIMAL(10,2)
  );

  -- Bulk fetch into table variable
  INSERT INTO v_trans
  SELECT id, salesperson_id, commission_amount
  FROM cs_transactions
  WHERE status = 'APPROVED';

  -- Bulk insert (one operation, no loop)
  INSERT INTO payout_schedule
  SELECT id, salesperson_id, commission_amount
  FROM v_trans;
END;
/
⚠️Performance cliff: The Oracle cursor loop processes 100k rows in ~10 seconds. The HANA table variable bulk operation does it in ~100 milliseconds. This is the primary reason to refactor — it's not about syntax, it's about speed.

Variable Type Declarations

Oracle's %TYPE and %ROWTYPE attributes are shorthand for "use the type of this column/row". HANA has no equivalent. You must declare types explicitly.

Oracle PatternHANA EquivalentNotes
v_id cs_transactions.id%TYPE;DECLARE v_id INTEGER;Look up the column type; use it directly.
v_row cs_transactions%ROWTYPE;DECLARE TABLE t (id INT, name NVARCHAR, ...)Define a table structure with each column.
v_list t_list_type;DECLARE TABLE t (value ...)Collections → table variables.

Exception Handling: EXCEPTION WHEN → DECLARE EXIT HANDLER

Oracle PL/SQL — Exception handling
DECLARE
  e_custom EXCEPTION;
BEGIN
  IF v_amount < 0 THEN
    RAISE e_custom;
  END IF;
EXCEPTION
  WHEN e_custom THEN
    DBMS_OUTPUT.PUT_LINE('Invalid amount');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error');
END;
/
SAP HANA SQL Script — Exception handling
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Handle any SQL exception
  END;

  IF v_amount < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount';
  END IF;
END;
/
📋Exception handling gotcha: Oracle has named exceptions (NO_DATA_FOUND, TOO_MANY_ROWS, etc.). HANA uses SQLSTATE codes. During migration, map Oracle exceptions to HANA equivalents or use generic SQLEXCEPTION handlers.

Packages vs Procedures: Decomposition Strategy

PL/SQL packages are containers for related procedures and functions, with shared package-level variables. HANA has no equivalent.

Oracle Package Structure

Oracle Package Spec
CREATE OR REPLACE PACKAGE cs_commission_pkg IS
  -- Package-level variable (shared state)
  g_batch_id NUMBER;

  -- Public procedures
  PROCEDURE calculate_commissions(p_period_id NUMBER);
  PROCEDURE assign_tiers(p_period_id NUMBER);
  PROCEDURE process_payouts(p_period_id NUMBER);
END cs_commission_pkg;
/

HANA Decomposition Strategy

No packages. Create individual procedures organized by schema or naming convention:

SAP HANA — Individual procedures (no package)
-- Option 1: Use schema (namespace)
CREATE SCHEMA cs_commission;
CREATE PROCEDURE cs_commission.calculate_commissions(p_period_id INTEGER) ...
CREATE PROCEDURE cs_commission.assign_tiers(p_period_id INTEGER) ...

-- Option 2: Naming convention
CREATE PROCEDURE cs_calc_commissions(p_period_id INTEGER) ...
CREATE PROCEDURE cs_assign_tiers(p_period_id INTEGER) ...

-- Option 3: Pass shared state as parameters
-- If Oracle package shared g_batch_id, pass it as a parameter instead

BULK COLLECT → Direct INSERT/SELECT

Oracle — BULK COLLECT pattern
DECLARE
  TYPE t_commission_list IS TABLE OF cs_commissions%ROWTYPE;
  v_list t_commission_list;
BEGIN
  -- Fetch up to 10000 rows at once (better than row-by-row)
  BULK COLLECT INTO v_list
  SELECT * FROM cs_transactions WHERE status = 'APPROVED';

  -- Process the collection (usually a loop)
  FORALL i IN 1 .. v_list.COUNT
    INSERT INTO payout_schedule VALUES v_list(i);
END;
/
HANA — Direct bulk operation (no intermediate collection)
BEGIN
  -- No BULK COLLECT needed. Direct INSERT...SELECT is bulk by default.
  INSERT INTO payout_schedule
  SELECT * FROM cs_transactions
  WHERE status = 'APPROVED';
END;
/

AUTONOMOUS_TRANSACTION: Workarounds

Oracle's AUTONOMOUS_TRANSACTION pragma allows a procedure to commit independently of the caller. HANA has no equivalent.

ScenarioOracle SolutionHANA Workaround
Logging that survives rollbackPRAGMA AUTONOMOUS_TRANSACTION; COMMIT;Call a separate procedure with its own transaction (commit after it returns)
Audit trailAutonomous transaction log procUse triggers (which auto-commit) or call a separate logging procedure
Nested procedures with independent commitsMark inner proc as autonomousRefactor to avoid nesting; use separate called procedures

Migration Checklist: Procedural Code

  1. Inventory procedures and packages: List all PL/SQL packages, procedures, and functions in Callidus.
  2. Identify cursor loops: Find all CURSOR ... OPEN ... FETCH ... LOOP patterns. These need rewriting as table variable bulk operations.
  3. Map packages to schemas: Each package becomes a schema (or use naming convention). Create the schema in HANA.
  4. Rewrite cursor loops: Replace with INSERT INTO ... SELECT or UPDATE ... FROM patterns.
  5. Convert BULK COLLECT: Use direct INSERT INTO ... SELECT (no intermediate collection needed).
  6. Handle exceptions: Map Oracle exceptions to HANA DECLARE EXIT HANDLER ... FOR SQLEXCEPTION.
  7. Remove AUTONOMOUS_TRANSACTION: Refactor to separate called procedures or use triggers.
  8. Test logic equivalence: For each procedure, compare Oracle output (test case) vs HANA output. Validate row counts, calculated values, and side effects.

Next: Data Types →

The final lesson: data type mapping. Including the DATE gotcha that causes silent data loss.

Lesson 05: Data Types →