PL/SQL Function Example

In this example, you create a PL/SQL function, and a SQL statement that returns a single row with two values. You create an advanced rule with SQL-default logic to set a new value in any fields in the request, based on the SQL statement.

  1. Create a function with a parameter that can be passed.

    CREATE OR REPLACE FUNCTION sample_param (p_num IN NUMBER)
    RETURN NUMBER IS
    tmpVar NUMBER;
    BEGIN
    tmpVar := p_num;
    RETURN tmpVar;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END sample_param;
    /
  2. Create a new Request Type and add two new fields.

    1. On the PPM Workbench shortcut bar, select Demand Mgmt > Request Types. The Request Type Workbench opens.

    2. Click New Request Type. Provide values as necessary for the new request type.

    3. On the Fields tab, add two new fields.

    4. Go to the Rules tab, create an advanced rule for the request type that uses the PL/SQL function.

    5. Save the changes.

  3. In PPM, create a request using the Request Type you just created and submit it.

  4. Open the request you just created, set a value for the first field based on the SQL Statement to trigger the PL/SQL function. The second field is populated automatically.