TL;DR
Oracle Database (row-based) and SAP HANA Database (column-based) have fundamentally different architectures — this affects query design, indexing strategy, and procedural code.
Most standard SQL translates directly. The three critical rewrites: CONNECT BY → recursive CTE, ROWNUM → LIMIT/ROW_NUMBER, NVL/DECODE → IFNULL/CASE.
PL/SQL packages → HANA SQL Script procedures: the biggest migration effort is rewriting row-by-row cursor logic as set-based table variable operations.
You're migrating from Callidus Commissions (Oracle Database) to SAP SuccessFactors Incentive Management (SAP HANA Database). Your team needs to understand not just that these databases are different, but precisely how they differ — so you can estimate effort, plan rewrites, and validate that migrated logic produces identical results. This article is that reference.
Architecture: The Fundamental Difference
| Dimension | Oracle Database | SAP HANA Database |
|---|---|---|
| Storage model | Row-based (all columns of row 1, then row 2...) | Column-based (all values of col 1, then col 2...) |
| Primary location | Disk (with buffer cache) | In-memory primary, with disk persistence |
| Compression | Optional, per-table | Automatic, per-column (dictionary encoding) |
| Best for | OLTP: many individual row inserts/updates | OLAP: aggregations over millions of rows |
| Indexes | B-tree indexes critical for performance | Columnar structure largely replaces traditional indexes |
| Callidus/SFIM context | Callidus Commissions backend | SAP SuccessFactors Incentive Management backend |
SQL Syntax Comparison
| Operation | Oracle Database PL/SQL | SAP HANA Database SQL |
|---|---|---|
| Top N rows | WHERE ROWNUM <= 10 | LIMIT 10 |
| Hierarchical query | CONNECT BY PRIOR id = parent_id | Recursive CTE: WITH RECURSIVE ... UNION ALL |
| Null replacement | NVL(col, 0) | IFNULL(col, 0) or COALESCE(col, 0) |
| Conditional value | DECODE(col, v1, r1, v2, r2, default) | CASE WHEN col=v1 THEN r1 WHEN col=v2 THEN r2 ELSE default END |
| Current date | SYSDATE | CURRENT_DATE |
| Days between dates | date1 - date2 | DAYS_BETWEEN(date1, date2) |
| String to date | TO_DATE('2026-01-01','YYYY-MM-DD') | TO_DATE('2026-01-01','YYYY-MM-DD') (same) |
| Dummy table | FROM DUAL | FROM DUMMY |
| Outer join (old style) | WHERE a.id = b.id(+) | Not supported — use standard LEFT JOIN |
| String aggregation | LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) | STRING_AGG(col, ',' ORDER BY col) |
The CONNECT BY → Recursive CTE Rewrite
This is the rewrite every Callidus Commissions migration team hits. The Oracle Database CONNECT BY PRIOR syntax for hierarchical queries has no direct equivalent in SAP HANA Database — you rewrite it as a recursive CTE.
SELECT ID, PARENT_ID, NAME, LEVEL AS DEPTH, SYS_CONNECT_BY_PATH(NAME, '/') AS FULL_PATH FROM CS_PARTICIPANT START WITH PARENT_ID IS NULL CONNECT BY PRIOR ID = PARENT_ID ORDER BY LEVEL, NAME;
WITH RECURSIVE hier ( ID, PARENT_ID, NAME, DEPTH, FULL_PATH ) AS ( -- Anchor: root nodes (replaces START WITH) SELECT ID, PARENT_ID, NAME, 1 AS DEPTH, TO_NVARCHAR(NAME) AS FULL_PATH FROM CSC_PARTICIPANT WHERE PARENT_ID IS NULL UNION ALL -- Recursive: children (replaces CONNECT BY PRIOR) SELECT p.ID, p.PARENT_ID, p.NAME, h.DEPTH + 1, h.FULL_PATH || '/' || p.NAME FROM CSC_PARTICIPANT p JOIN hier h ON h.ID = p.PARENT_ID WHERE h.DEPTH < 20 ) SELECT ID, PARENT_ID, NAME, DEPTH, FULL_PATH FROM hier ORDER BY DEPTH, NAME;
Procedural Code: PL/SQL vs HANA SQL Script
| Feature | Oracle Database PL/SQL | SAP HANA Database SQL Script |
|---|---|---|
| Block delimiter | DECLARE ... BEGIN ... EXCEPTION ... END; | BEGIN ... END; (no DECLARE section) |
| Variable declaration | In DECLARE block | Inline with DECLARE statement inside BEGIN |
| Row iteration | Cursor + LOOP | Table variables (set-based preferred) |
| Bulk fetch | BULK COLLECT INTO collection | Direct table variable assignment |
| Output parameter | OUT param_name TYPE | OUT param_name TABLE(...) for sets |
| Exception handling | EXCEPTION WHEN ... THEN | DECLARE EXIT HANDLER FOR ... |
| Packages | Supported (spec + body) | Not supported — use individual procedures |
| String type | VARCHAR2 | NVARCHAR (Unicode by default) |
Data Types Mapping for Migration
| Oracle Database Type | SAP HANA Database Equivalent | Notes |
|---|---|---|
VARCHAR2(n) | NVARCHAR(n) | HANA defaults to Unicode |
NUMBER(p,s) | DECIMAL(p,s) | Direct equivalent |
DATE | DATE or TIMESTAMP | Oracle DATE includes time; split in HANA |
CLOB | NCLOB | Large character objects |
BLOB | BLOB | Same |
PLS_INTEGER | INTEGER | HANA SQL Script equivalent |
BOOLEAN | TINYINT (0/1) | No native BOOLEAN in HANA |
⚠️Oracle DATE contains time. SAP HANA DATE does not. In Callidus Commissions, many date columns store datetime values using Oracle's DATE type. When migrating to SAP HANA Database, you need to decide: map to
DATE (loses time) or TIMESTAMP (preserves it). Check your data before deciding — this mistake causes silent data loss.Migration Checklist: SQL Objects
- Inventory first: Run the user_objects query in Oracle to list all procedures, functions, packages, views, and triggers before writing a single line of HANA code.
- CONNECT BY queries: Find all with
grep -i "connect by" *.sql. Each needs a recursive CTE rewrite. - ROWNUM usage: Replace with
LIMITorROW_NUMBER() OVER (...)depending on context. - NVL / DECODE: Replace with
IFNULL/CASE WHEN. COALESCE works on both and is the safest choice. - PL/SQL packages: Decompose into individual HANA SQL Script procedures. There is no package equivalent in HANA.
- Oracle sequences: Replace with HANA sequences (syntax differs slightly but concept is the same).
- Outer join (+) syntax: Replace with standard ANSI
LEFT JOIN.