"Cannot Function Ship the Following Expression" With Evaluate Function When Running Campaign Load With Segment (Doc ID 1982475.1)

Last updated on JULY 28, 2016

Applies to:

Siebel Marketing - Version 8.1.1.10 [23021] and later
Information in this document applies to any platform.

Symptoms

On : 8.1.1.10 [23021] version, Siebel Marketing
OBIEE 11.1.1.7.140415

When attempting to run a Campaign Load for a Campaign associated with a Segment (any) the load fails with a SOAP error and the OBI logs show the following error:

ERROR
-----------------------
sawsoap:message>State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: Evaluate( fpok3(%1,%2),cast(rcount(0 at_distinct [ D1.c1, D1.c2, D1.c3, D1.c4, D1.c5, D1.c6] order by [0 1 2 3 4 5 ]) as VARCHAR ( 10 )) , ''1:1-74CPCQJ:100'') . (HY000)

This fails in the Development environment but works fine in Production.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a Campaign, Offer, Treatment, Segment and Direct Database Campaign Load Format
2. Associate the Campaign, Offer, Treatment and Segment
3. Associate the Direct Database Campaign Load Format to the Segment
4. Allocate the Segment to the Treatment
5. Run the Campaign Load

Examining the logs the following was seen:

PROD:
[2015-01-21T17:41:18.000+01:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: 45638d15824b98c1:-55271263:14af844bcd0:-8000-000000000010a63a,0:1:1:5] [tid: 90cee940] [requestid: b8300004] [sessionid: b8300000] [username: weblogic] -------------------- Execution plan: [[

RqPopulate <<25774522>> [for database 0:0,1] create  table
   RqList <<25774140>> [for database 3023:66172:Oracle Data Warehouse,57] distinct
       D1.c1 as c1 [for database 3023:66172,57]
   Child Nodes (RqJoinSpec): <<25774149>> [for database 3023:66172:Oracle Data Warehouse,57]
       RqJoinNode <<25774148>> []
...
============================================================================================================================================================================
DEV:
[2015-01-21T17:30:35.000+01:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: d39e8ef212ad0d94:-141d27a1:14b074d8db3:-8000-0000000000013af0,0:1:1:5] [tid: bc9c940] [requestid: fb4d0005] [sessionid: fb4d0000] [username: weblogic] -------------------- Execution plan: [[

RqList <<730156>> [for database 3023:66172:Oracle Data Warehouse,57] distinct
   D1.c1 as c1 [for database 3023:66172,57]
Child Nodes (RqJoinSpec): <<730165>> [for database 3023:66172:Oracle Data Warehouse,57]
   RqJoinNode <<730164>> []
...
============================================================================================================================================================================
Notice that PROD has "RqPopulate <<25774522>> [for database 0:0,1] create  table" bit appended.

In PROD we then see a temporary table (TT) being created (which isn't created in DEV):

[2015-01-21T17:41:19.000+01:00] [OracleBIServerComponent] [TRACE:5] [USER-18] [] [ecid: 45638d15824b98c1:-55271263:14af844bcd0:-8000-000000000010a63a,0:1:1:5:2] [tid: b678f940] [requestid: b8300004] [sessionid: b8300000] [username: weblogic] -------------------- Sending query to database named Oracle Data Warehouse (id: CreateTable TransGateway), connection pool named Oracle Data Warehouse Connection Pool, logical request hash 5e43ec74, physical request hash 113b8e73: [[
CREATE TABLE TTAE020SBKHB2A4H0F000030S023 ( column1 VARCHAR2(80 CHAR) )

We can then see further differences:
============================================================================================================================================================================
PROD:
[2015-01-21T17:41:19.000+01:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: 45638d15824b98c1:-55271263:14af844bcd0:-8000-000000000010a63a,0:1:1:5] [tid: 90cee940] [requestid: b8300004] [sessionid: b8300000] [username: weblogic] -------------------- Execution plan: [[

RqList <<25773608>> [for database 3023:66172:Oracle Data Warehouse,57] distinct
   D1.c1 as c1 [for database 3023:66172,57],
   D1.c2 as c2 [for database 3023:66172,57],
   D1.c3 as c3 [for database 3023:66172,57],
   D1.c4 as c4 [for database 3023:66172,57],
   D1.c5 as c5 [for database 3023:66172,57],
   D1.c6 as c6 [for database 3023:66172,57],
   D1.c7 as c7 [for database 3023:66172,57],
   D1.c8 as c8 [for database 3023:66172,57],
   D1.c9 as c9 [for database 3023:66172,57],
   D1.c10 as c10 [for database 3023:66172,57],
   D1.c11 as c11 [for database 3023:66172,57],
   D1.c12 as c12 [for database 3023:66172,57],
   D1.c13 as c13 [for database 3023:66172,57],
   D1.c14 as c14 [for database 3023:66172,57],
   D1.c15 as c15 [for database 3023:66172,57],
   D1.c16 as c16 [for database 3023:66172,57],
   D1.c17 as c17 [for database 3023:66172,57],
   D1.c18 as c18 [for database 3023:66172,57],
   D1.c19 as c19 [for database 3023:66172,57],
   D1.c20 as c20 [for database 3023:66172,57],
   D1.c21 as c21 [for database 3023:66172,57],
   D1.c22 as c22 [for database 3023:66172,57],
   D1.c23 as c23 [for database 3023:66172,57],
   D1.c24 as c24 [for database 3023:66172,57],
   D1.c25 as c25 [for database 3023:66172,57],
   D1.c26 as c26 [for database 3023:66172,57],
   D1.c27 as c27 [for database 3023:66172,57]
Child Nodes (RqJoinSpec): <<25773666>> [for database 3023:66172:Oracle Data Warehouse,57]
   RqJoinNode <<25773607>> []
...
...
============================================================================================================================================================================
DEV:
[2015-01-21T17:30:44.000+01:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: d39e8ef212ad0d94:-141d27a1:14b074d8db3:-8000-0000000000013af0,0:1:1:5] [tid: bc9c940] [requestid: fb4d0005] [sessionid: fb4d0000] [username: weblogic] -------------------- Execution plan: [[

RqList <<729624>> [for database 0:0,0] distinct
   D1.c1 as c1 [for database 3023:66172,57],
   D1.c2 as c2 [for database 3023:66172,57],
   D1.c3 as c3 [for database 3023:66172,57],
   D1.c4 as c4 [for database 0:0,0],
   D1.c5 as c5 [for database 0:0,0],
   D1.c6 as c6 [for database 0:0,0],
   D1.c7 as c7 [for database 0:0,0],
   D1.c8 as c8 [for database 0:0,0],
   D1.c9 as c9 [for database 0:0,0],
   D1.c10 as c10 [for database 3023:66172,57],
   D1.c11 as c11 [for database 3023:66172,57],
   D1.c12 as c12 [for database 3023:66172,57],
   D1.c13 as c13 [for database 0:0,0],
   D1.c14 as c14 [for database 0:0,0],
   D1.c15 as c15 [for database 3023:66172,57],
   D1.c16 as c16 [for database 0:0,0],
   D1.c17 as c17 [for database 0:0,0],
   D1.c18 as c18 [for database 0:0,0],
   D1.c19 as c19 [for database 0:0,0],
   D1.c20 as c20 [for database 0:0,0],
   D1.c21 as c21 [for database 0:0,0],
   D1.c22 as c22 [for database 0:0,0],
   D1.c23 as c23 [for database 0:0,0],
   D1.c24 as c24 [for database 0:0,0],
   D1.c25 as c25 [for database 3023:66172,57],
   D1.c26 as c26 [for database 0:0,0],
   D1.c27 as c27 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<729682>> [for database 0:0,0]
   RqJoinNode <<729623>> []
...
...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms