Introduction to Oracle PL/SQL for Callidus Commissions

What Is PL/SQL?

PL/SQL stands for Procedural Language/SQL. It's Oracle's extension to SQL that adds procedural constructs: variables, control flow (IF/ELSE, loops), exception handling, and the ability to package code into reusable procedures and functions.

SQL is declarative — you say "give me all participants with status = ACTIVE" and the database returns a result set. PL/SQL is procedural — you say "for each active participant, calculate their commission, check if it exceeds the threshold, and if it does, insert a review record." You're describing not just what data you want, but the steps to process it.

Callidus Commissions runs on Oracle Database. The calculation engine, compensation pipelines, and data extraction logic are all written in PL/SQL. Every procedure in the CS_ schema (CS_CALC_PKG, CS_EXTRACT_PKG, and so on) is PL/SQL. Understanding PL/SQL is non-negotiable for supporting legacy Callidus implementations.

The PL/SQL Block Structure

Every PL/SQL program — whether an anonymous block, a stored procedure, or a function — follows the same structure:

DECLARE
  -- Section 1: Declare variables, constants, cursors, and types
  -- Optional for anonymous blocks (can be omitted if no variables needed)

BEGIN
  -- Section 2: Executable statements (required)
  -- SQL and PL/SQL commands: SELECT, INSERT, UPDATE, DELETE, loops, conditionals, etc.

EXCEPTION
  -- Section 3: Exception handling (optional)
  -- Handle errors that occur during execution

END;
-- Terminate with semicolon
/
-- Forward slash on its own line executes the block

Let's break this down:

DECLARE Section

This is where you declare variables, constants, cursors, and custom types. It's optional — if your block doesn't need any variables, you can skip it and go straight to BEGIN. But in practice, almost every PL/SQL block declares at least a few variables to store intermediate values.

BEGIN Section

This is the executable part. All your SQL statements (SELECT, INSERT, UPDATE, DELETE) and control flow logic go here. BEGIN is mandatory in every block. The BEGIN section ends when you hit the EXCEPTION keyword (if you have error handling) or END (if you don't).

EXCEPTION Section

This is where you catch and handle errors. It's optional. If your block might fail (e.g., no rows returned from a SELECT, a constraint violation), you catch the exception here and handle it gracefully. If you don't include EXCEPTION, any error will bubble up and terminate the entire block.

END

Every block ends with END followed by a semicolon. Optionally, you can label the END with the block name (e.g., END my_block;) for clarity, but it's not required.

Forward Slash

The forward slash / on a line by itself tells Oracle to execute the block. It's not part of the PL/SQL syntax — it's a SQL*Plus/SQL Developer command. Without the slash, your code sits in the buffer but doesn't run.

Your First PL/SQL Block

Let's write a simple anonymous block that queries the CS_PARTICIPANT table and outputs the count of active participants. This is the "Hello World" of Callidus PL/SQL.

DECLARE
  -- Variables to hold the result
  v_participant_count NUMBER;
  v_total_results     NUMBER;

BEGIN
  -- Count active participants
  SELECT COUNT(*)
  INTO   v_participant_count
  FROM   CS_PARTICIPANT
  WHERE  STATUS = 'ACTIVE';

  -- Count all results in the current quarter
  SELECT COUNT(*)
  INTO   v_total_results
  FROM   CS_RESULTS
  WHERE  PERIOD_ID = '2026-Q1';

  -- Output the results
  DBMS_OUTPUT.PUT_LINE('Active Participants: ' || v_participant_count);
  DBMS_OUTPUT.PUT_LINE('Q1 2026 Results:    ' || v_total_results);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

Let's walk through what this block does:

Step 1: Declare Variables

We declare two NUMBER variables: v_participant_count and v_total_results. The v_ prefix is a convention (not a requirement) to indicate "variable". NUMBER is a data type that can hold integers or decimals.

Step 2: Query CS_PARTICIPANT

We use SELECT ... INTO to fetch a single value into a variable. The INTO clause is critical — it tells PL/SQL where to put the result. Without INTO, you're just running a SQL query; with INTO, you're storing the result in a PL/SQL variable.

Step 3: Query CS_RESULTS

Same pattern: fetch the count into v_total_results.

Step 4: Output

DBMS_OUTPUT.PUT_LINE is the PL/SQL print statement. It outputs text to the console. The || operator concatenates strings. So 'Active: ' || v_participant_count produces something like "Active: 250".

Step 5: Error Handling

WHEN OTHERS THEN catches any error that occurs. SQLERRM is the error message. So if the SELECT fails (e.g., CS_PARTICIPANT doesn't exist), instead of crashing, we print the error message.

Running PL/SQL in Oracle SQL Developer

Oracle SQL Developer is the standard IDE for Oracle development. Here's how to run the block above:

1. Open SQL Developer. If you don't have it, download from Oracle's website (it's free).
2. Connect to your Callidus database. Create a new connection with your Oracle hostname, port, SID, username, and password.
3. Open a SQL worksheet. Click File → New → SQL File, or right-click your connection and click "Open SQL Worksheet".
4. Enable output. Go to View → Dbms Output, or press the Dbms Output tab at the bottom. This shows output from DBMS_OUTPUT.PUT_LINE.
5. Paste the block. Copy the PL/SQL code above and paste it into the worksheet.
6. Run it. Press Ctrl+Enter to execute, or click the Run button (the green arrow). The output appears in the Dbms Output tab.

If you get an error like "TABLE OR VIEW DOES NOT EXIST", make sure your CS_PARTICIPANT table exists. Run SELECT COUNT(*) FROM CS_PARTICIPANT; first to verify. If it doesn't exist, adjust the table name to match your schema.

Variables and Data Types

Variables store values that change during block execution. Every variable has a data type that defines what kind of data it can hold.

Declaring Variables: The := Operator

The := operator assigns an initial value (or default) to a variable. It's optional — if you don't initialise a variable, it defaults to NULL.

DECLARE
  -- No initial value (defaults to NULL)
  v_participant_name VARCHAR2(200);

  -- Initial value set with :=
  v_commission_amount NUMBER(18,2) := 0;
  v_period_id         VARCHAR2(20) := '2026-Q1';
  v_is_calculated     BOOLEAN            := FALSE;

  -- Constants: CONSTANT keyword prevents changes
  c_max_commission    NUMBER(18,2) CONSTANT := 500000;
  c_status_active     VARCHAR2(20) CONSTANT := 'ACTIVE';

BEGIN
  -- Your logic here
  NULL;

END;
/

SELECT ... INTO: The Core PL/SQL Pattern

SELECT ... INTO is the main way to fetch data from the database into PL/SQL variables. It's almost identical to a regular SQL SELECT, except for the INTO clause.

DECLARE
  v_participant_id   NUMBER;
  v_participant_name VARCHAR2(200);
  v_total_earned     NUMBER(18,2);

BEGIN
  -- Single column INTO single variable
  SELECT COUNT(*)
  INTO   v_participant_id
  FROM   CS_PARTICIPANT
  WHERE  STATUS = 'ACTIVE';

  -- Multiple columns INTO multiple variables (order matters)
  SELECT p.ID, p.NAME, SUM(r.RESULT_AMOUNT)
  INTO   v_participant_id, v_participant_name, v_total_earned
  FROM   CS_PARTICIPANT p
  JOIN   CS_RESULTS r ON r.PARTICIPANT_ID = p.ID
  WHERE  p.ID = 12345
    AND  r.PERIOD_ID = '2026-Q1'
  GROUP BY p.ID, p.NAME;

  DBMS_OUTPUT.PUT_LINE(
    'Participant: ' || v_participant_name ||
    ', Earned: ' || v_total_earned
  );

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

END;
/

Key Points About SELECT ... INTO

It returns exactly one row. If your SELECT returns zero rows, you get a NO_DATA_FOUND exception. If it returns multiple rows, you get a TOO_MANY_ROWS exception. For multiple rows, you use cursors (covered in lesson 3).

The number of columns must match the number of INTO variables. If your SELECT has 3 columns, you need 3 variables in the INTO clause, in the same order.

NULL handling. If any column value is NULL, the corresponding variable becomes NULL. Use NVL() in your SELECT if you want a default value instead: SELECT NVL(commission, 0) INTO v_amount ...

The DBMS_OUTPUT Package

DBMS_OUTPUT is Oracle's built-in package for printing output during PL/SQL execution. It has one main procedure you'll use:

BEGIN
  -- Simple output
  DBMS_OUTPUT.PUT_LINE('Hello, Callidus');

  -- String concatenation with ||
  DBMS_OUTPUT.PUT_LINE('Count: ' || 42);

  -- Multiple outputs
  DBMS_OUTPUT.PUT_LINE('------- Results -------');
  DBMS_OUTPUT.PUT_LINE('Participant 1: $' || 50000);
  DBMS_OUTPUT.PUT_LINE('Participant 2: $' || 75000);
  DBMS_OUTPUT.PUT_LINE('------------------------');

END;
/

Exception Handling: WHEN and OTHERS

Exceptions are errors that occur during execution. PL/SQL defines specific exceptions for common errors:

DECLARE
  v_participant_name VARCHAR2(200);
  v_earned_amount    NUMBER(18,2);

BEGIN
  SELECT NAME, SUM(RESULT_AMOUNT)
  INTO   v_participant_name, v_earned_amount
  FROM   CS_PARTICIPANT p
  LEFT JOIN CS_RESULTS r ON r.PARTICIPANT_ID = p.ID
  WHERE  p.ID = 99999  -- May not exist
  GROUP BY NAME;

  DBMS_OUTPUT.PUT_LINE('Participant: ' || v_participant_name);
  DBMS_OUTPUT.PUT_LINE('Earned: $' || v_earned_amount);

EXCEPTION
  -- Specific exception: no rows returned
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Participant not found');

  -- Specific exception: multiple rows (unexpected)
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Query returned multiple rows (expected 1)');

  -- Catch-all for any other error
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);

END;
/

SQLERRM returns the error message as a string. SQLCODE returns the error number. Combining them with || gives you a full error description to log or display.

Key Takeaways

• Every PL/SQL block has the same structure: DECLARE (optional), BEGIN (required), EXCEPTION (optional), END.
• Variables hold values; declare them with a data type and optional initial value.
• SELECT ... INTO fetches data from the database into variables. It must return exactly one row.
• Use DBMS_OUTPUT.PUT_LINE to print output during execution.
• Always include exception handling (WHEN ... THEN) for errors that might occur.
• Run blocks in SQL Developer by pasting code and pressing Ctrl+Enter, or in SQL*Plus with a forward slash.