How can I set a workflow variable the value of an SQL variable?

[Gerald Lewis]

Good day.   I have a workflow that executes several jobs.   The first job in the workflow calls an Oracle stored function that returns 4 values; 3 values are returned as OUT parameters, and 1 value is the return result.    I would like to know how I can assign the 4 Oracle values to workflow variables so that other jobs in the workflow may use them.    Below is a simple example, but when I tried to assign the Oracle variable values to workflow variables I don't get the right values.

Example:   SQL JOB,  I set Process content to be:
SQL_SET_STATEMENT_TERMINATOR TERM=@;
DECLARE
     ret_error_level           NUMBER;
     ret_status                   BOOLEAN;
     ret_date                      DATE;
     ret_error_message     VARCHAR2(100);

     -- Function signature:     
     --      call_func(ret_error_level OUT NUMBER, ret_error_message OUT VARCHAR2, ret_date OUT DATE) RETURN BOOLEAN

      ret_status = call_func(ret_error_level, ret_error_message, ret_date);

      IF ret_status = FALSE THEN
           -- Log messages in JOBS Report
           dbms_output.put_line('error_level = ' || ret_error_level);
           dbms_output.put_line('error_message = ' || ret_error_message);
           dbms_output.put_line('Date error occurred on = ' || ret_date);

           -- I want to declare and set Workflow variables.   This does not assign the values correctly 
           -- How to assign values?
:PSET &func_ret_status=ret_status
:PSET &func_ret_error_level= ret_error_level       
:PSET &func_ret_error_messagel=ret_error_message
:PSET &func_ret_error_date=ret_error_date     
       END IF;
END;

------------------------------
Newbie
------------------------------

[Dan Darling]

You would most likely need to set the downstream jobs in your workflow to "generate at runtime" in the attributes tab of the job object. 

Then in the postprocess tab of your oracle job read the report output of the job itself with a prep_process_report command. 

Once you've built the datasequence from the prep_process_report you can use the process/endprocess to look at the datasequence contents and PUBLISH or PSET to set the values to the workflow level and your down stream jobs would be able to see those values.

PREP_PROCESS_REPORT KB
PUBLISH KB
Process Loop KB

I hope this little bit of info points you in the right direction.