Process Flow Variables – To Quote or Not To Quote

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.
'Hello World!'
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.
'Hello World!'
sysdate()

If the activity type is not a PL/SQL object then the value is language independent. e.g.
Hello World
3.1427571

Some illustrations

So what happens when you get it wrong? The following variable definitions and default value

variables0

will give the following errors when the flow is executed:

Error
RPE-01003: An infrastructure condition prevented the request from completing.
PFLOWCON
Error
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.
PFLOWCON
Error
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:

variables1

So watch out for the RPE-01038 exception and check the values and literal definitions.

Comments:

David, thanks for the article, useful but not quite applicable to my problem - I'll try to explain it! We are running OWB 11.1.0.7 on Windows Server 2003 R2.

I have a UDF in a process flow which takes in a text value, and returns a numeric (called Batch_ID). At the start of the process flow, I have declared a variable (Batch_ID - naturally enough). So the process flow starts with an assignment operator, whose Value attribute is not set, but the Variable attribute is bound to the variable Batch_ID declared in the Variables section of the Project Explorer.

The next operator in the process is the UDF, whose input properties I set to Literal - False, and enclose the text I am submitting to the UDF in single quotes. So far so good. The output parameter of the UDF is set with the following object details set - Literal = true, value = null (both of these values must be default as they are greyed out anyway), and Binding set to Batch_ID (through a drop down selection box - quotes should be inapplicable).

Next in the process flow is a mapping which takes the Batch_ID as an input parameter, so the Object details for this mapping are set as follows - Literal = true, Value - empty (again both are greyed out anyway) and the Binding = Batch_ID (selected from a drop-down box - so quotes should not be an issue).

Validation and deployment of the process flow is textbook perfect - no problems. Until we attempt to execute the mapping - when the RPE-01038 error is displayed in the Job Details box - and the process flow runs perfectly.

My question is really seeking confirmation, or otherwise, that the order and object detail settings are what you may expect to set for the simplistic process flow described above?

Martin

Posted by guest on December 12, 2011 at 07:20 PM PST #

Hi Martin

Yes this is the general flow, you have to use variables to chain values from the output of a function into a mapping. Do you know which activity is throwing the error?

Cheers
David

Posted by David on December 13, 2011 at 08:42 AM PST #

At last, I have figured out the problem - or at least I think I have (it seems to work anyway!)

Each variable declared in a process flow has two attributes in the Project Explorer (Selected objects tab). There is a Value attribute, and a variable attribute. I hadn't noticed before (the sections were greyed out and my eyesight must be going!), but the Value is an IN parameter and the Variable is an OUT parameter.

So really this is just like an assignment operation in PLSQL (a := b); Set the variable a equal to the value of b - so the Value attribute, being the IN attribute can be bound to the output of a preceding object, or be set to an explicit value. The Variable attribute defines the process flow variable which will be set to the value of the Value attribute.

Sorry David, re-reading this I find that I started out reasonably well in trying to explain this to myself, but that I ended up gushing out words in an effort not to lose the train of thought. I think I know what I mean, my question is whether my logic, or presumptions, are correct!?

Posted by guest on December 13, 2011 at 10:01 AM PST #

Hi Martin

No worries, yes they are correct. Sometimes just asking the question is what knocks the door down.

This was an extract of a rant from a few years ago..... One of the usability issues with process flow is the constant change of context to build a simple (in other languages) statement. For example if I want to increment a variable I have to switch between 2 parameters in a tree (the more parameters, the more annoying it gets;). First I click on VARIABLE to see where the result is stored. Then I click on VALUE to see the actual value... all a bit tedious. For an activity if you could have seen all the parameters as columns in a table and the properties as rows at least you could have seen it all at once.

Anyway...glad you are getting it now.
Cheers
David

Posted by guest on December 13, 2011 at 10:52 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today