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.