Process Flow Variables – To Quote or Not To Quote
By David Allan-Oracle on Mar 20, 2009
Here is some information on the process flow variable literal quote or not quote query. Its not exactly intuitive, so hopefully this helps! The flow designer has to know what is a PLSQL activity and what is not. Process flow variables are added in the same manner as process flow parameters (see the post here for details)
Literal = FALSE
When Literal = FALSE is set then the value entered must be a valid PL/SQL expression which is evaluated at the Control Center e.g.
22 / 7
Literal = TRUE
When Literal = TRUE then the value is dependent on the the type of Activity. If the activity is a PL/SQL object i.e. Mapping or Transformation, then the value is PL/SQL snippet. The critical difference here is that the value is macro substituted into the call for the object. The format of the value is identical to that entered as default value is the Mapping editor. e.g.
If the activity type is not a PL/SQL object then the value is language independent. e.g.
So what happens when you get it wrong? The following variable definitions and default value
will give the following errors when the flow is executed:
RPE-01003: An infrastructure condition prevented the request from completing.
RPE-01038: Failed to evaluate expression declare "$LOOP_DETECT$" NUMBER := 0;function "PFLOWCON" return VARCHAR2 is "WARNING" NUMBER := 2;"RETURN_RESULT_NUMBER" NUMBER := NULL;"NUMBER_OF_ERRORS" NUMBER := 0;"SUCCESS" NUMBER := 1;"ITEM_TYPE" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12137));"ERROR" NUMBER := 3;"EVAL_LOCATION" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12140));"OK" NUMBER := 1;"FAILURE" NUMBER := 3;"AUDIT_ID" NUMBER := 12136;"RETURN_CODE" NUMBER := NULL;"NUMBER_OF_WARNINGS" NUMBER := 0;"ITEM_KEY" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12139));"RETURN_RESULT" VARCHAR(64) := NULL;"PARENT_AUDIT_ID" NUMBER := NULL;"OK_WITH_WARNINGS" NUMBER := 2;begin "$LOOP_DETECT$" := "$LOOP_DETECT$" + 1;if "$LOOP_DETECT$" > 2 then raise_application_error(-20001, 'Loop detected calling "PFLOWCON"');end if;return to_char(defaultvalue);end;begin :result := "PFLOWCON";end;. Please modify the expression, redeploy and retry again.
ORA-06550: line 1, column 831:
PLS-00201: identifier 'DEFAULTVALUE' must be declared
ORA-06550: line 1, column 816:
PL/SQL: Statement ignored
This error happens when you have a variable defined as a STRING, with literal defined as false, and the value of the variable is not quoted.
So the following two variable definitions are equivalent and valid:
So watch out for the RPE-01038 exception and check the values and literal definitions.