Oracle Database & PL/SQL: The Foundation of Callidus Commissions

TL;DR

PL/SQL extends SQL with procedural constructs — variables, loops, conditions, and exception handling — allowing complex business logic inside the Oracle Database.

Callidus Commissions uses Oracle Database PL/SQL heavily for calculation procedures, data pipelines, and reporting extracts — understanding this is essential for legacy support and migration prep.

Cursors and bulk operations (BULK COLLECT / FORALL) are the two PL/SQL patterns you'll encounter most in Callidus Commissions performance work.

Callidus Commissions was built on Oracle Database. Its calculation engine, pipeline processing, and data extraction procedures are all written in Oracle PL/SQL. If you're maintaining a legacy Callidus Commissions implementation, writing migration scripts, or validating data before a move to SAP SuccessFactors Incentive Management — you need to be comfortable in PL/SQL.

What PL/SQL Adds to SQL

SQL describes what data you want. PL/SQL adds the procedural constructs to act on that data: variables, conditional logic, loops, error handling, and the ability to package related code into reusable units. Every PL/SQL program has the same fundamental block structure:

DECLARE
  -- Variables, constants, cursors declared here
  v_participant_name  VARCHAR2(200);
  v_total_earned      NUMBER(18,2) := 0;
  v_period_id         VARCHAR2(20) := '2026-Q1';

BEGIN
  -- Executable statements here
  SELECT p.NAME, SUM(r.RESULT_AMOUNT)
  INTO   v_participant_name, v_total_earned
  FROM   CS_PARTICIPANT p
  JOIN   CS_RESULTS r ON r.PARTICIPANT_ID = p.ID
  WHERE  r.PERIOD_ID = v_period_id
    AND  p.ID = 12345
  GROUP BY p.NAME;

  DBMS_OUTPUT.PUT_LINE('Earned: ' || v_total_earned);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No results for this participant/period');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Variables and Data Types

PL/SQL variables can be declared with explicit types or anchored to database column types using %TYPE. Anchoring is the professional approach — if the column type changes in the database, your variable automatically adapts.

DECLARE
  -- Anchored types (preferred): type follows the column
  v_name      CS_PARTICIPANT.NAME%TYPE;
  v_amount    CS_RESULTS.RESULT_AMOUNT%TYPE;

  -- Anchored to a full row
  rec_participant  CS_PARTICIPANT%ROWTYPE;

  -- Explicit types (use when no matching column)
  v_counter   PLS_INTEGER := 0;
  v_flag      BOOLEAN     := FALSE;
  v_msg       VARCHAR2(500);
BEGIN
  NULL; -- placeholder
END;
/

Cursors: Processing Row by Row

A cursor is a pointer to a result set. You use cursors when you need to process rows one by one — which in Callidus Commissions typically means iterating over participants, applying rules, or building calculation outputs.

DECLARE
  -- Cursor definition
  CURSOR cur_participants IS
    SELECT ID, NAME, PLAN_ID
    FROM   CS_PARTICIPANT
    WHERE  STATUS = 'ACTIVE';

BEGIN
  -- Cursor FOR loop: open/fetch/close handled automatically
  FOR rec IN cur_participants LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Processing: ' || rec.NAME ||
      ' on plan '  || rec.PLAN_ID
    );
    -- Your logic per participant here
  END LOOP;
END;
/

⚠️Row-by-row cursor processing is slow on large datasets. If you're processing 10,000+ Callidus Commissions participants, use BULK COLLECT with FORALL instead. The performance difference is 10x–100x on typical Callidus data volumes.

Bulk Operations: BULK COLLECT and FORALL

BULK COLLECT fetches multiple rows into a collection in a single round-trip. FORALL performs DML operations in bulk. These are the patterns in every performance-critical Callidus Commissions procedure.

DECLARE
  TYPE t_participant_ids IS TABLE OF
    CS_PARTICIPANT.ID%TYPE;
  TYPE t_amounts IS TABLE OF
    CS_RESULTS.RESULT_AMOUNT%TYPE;

  l_ids     t_participant_ids;
  l_amounts t_amounts;

BEGIN
  -- Fetch all at once into collections
  SELECT PARTICIPANT_ID, SUM(RESULT_AMOUNT)
  BULK COLLECT INTO l_ids, l_amounts
  FROM   CS_RESULTS
  WHERE  PERIOD_ID = '2026-Q1'
  GROUP BY PARTICIPANT_ID;

  -- Update all at once (single round-trip)
  FORALL i IN 1..l_ids.COUNT
    UPDATE CS_COMP_SUMMARY
    SET    TOTAL_AMOUNT = l_amounts(i),
           UPDATED_AT   = SYSDATE
    WHERE  PARTICIPANT_ID = l_ids(i)
      AND  PERIOD_ID = '2026-Q1';

  DBMS_OUTPUT.PUT_LINE(
    'Updated ' || l_ids.COUNT || ' participants'
  );
END;
/

Stored Procedures and Functions

Procedures are the workhorses of Callidus Commissions — calculation pipelines, data extracts, and migration scripts are all procedures. Functions return a value and can be used directly in SQL queries.

-- PROCEDURE: no return value, used for DML/processing
CREATE OR REPLACE PROCEDURE
  calc_period_earnings (
    p_period_id   IN  CS_RESULTS.PERIOD_ID%TYPE,
    p_status_msg  OUT VARCHAR2
  )
AS
  v_count PLS_INTEGER;
BEGIN
  -- Calculation logic here
  SELECT COUNT(*) INTO v_count
  FROM   CS_RESULTS
  WHERE  PERIOD_ID = p_period_id;

  p_status_msg := 'Processed ' || v_count || ' rows';
EXCEPTION
  WHEN OTHERS THEN
    p_status_msg := 'ERROR: ' || SQLERRM;
END calc_period_earnings;
/

-- FUNCTION: returns a value, usable in SQL
CREATE OR REPLACE FUNCTION
  get_attainment_pct (
    p_participant_id IN NUMBER,
    p_period_id      IN VARCHAR2
  ) RETURN NUMBER
AS
  v_earned NUMBER; v_quota NUMBER;
BEGIN
  SELECT NVL(SUM(RESULT_AMOUNT),0)
  INTO   v_earned
  FROM   CS_RESULTS
  WHERE  PARTICIPANT_ID = p_participant_id
    AND  PERIOD_ID = p_period_id;

  SELECT NVL(SUM(QUOTA_AMOUNT),0)
  INTO   v_quota
  FROM   CS_QUOTA
  WHERE  PARTICIPANT_ID = p_participant_id
    AND  PERIOD_ID = p_period_id;

  IF v_quota = 0 THEN RETURN NULL; END IF;
  RETURN ROUND(v_earned / v_quota * 100, 2);
END;
/

Packages: Organising Callidus PL/SQL Code

Packages group related procedures, functions, and variables into a single named unit. Callidus Commissions uses packages to organise calculation logic — you'll commonly see package names like CS_CALC_PKG, CS_PIPELINE_PKG, or similar.

-- Package spec (public interface)
CREATE OR REPLACE PACKAGE cs_calc_utils AS
  FUNCTION  get_attainment(...) RETURN NUMBER;
  PROCEDURE run_period_calc(...);
END cs_calc_utils;
/

-- Package body (implementation)
CREATE OR REPLACE PACKAGE BODY cs_calc_utils AS
  -- Private variable (only accessible within package)
  g_run_date DATE := SYSDATE;

  FUNCTION get_attainment(...) RETURN NUMBER AS
  BEGIN
    -- Implementation
  END;

  PROCEDURE run_period_calc(...) AS
  BEGIN
    -- Implementation
  END;
END cs_calc_utils;
/

ℹ️For migration teams: When inventorying your Callidus Commissions Oracle Database PL/SQL before migrating to SAP HANA Database, start with packages. Run SELECT object_name, object_type FROM user_objects WHERE object_type IN ('PACKAGE','PROCEDURE','FUNCTION') ORDER BY 1; to get the full list. This inventory is your migration scope definition.

Next in the learning path →

SAP HANA Database: architecture, SQL dialect, and how columnar storage changes everything.

Article 03: SAP HANA Database →