Handy OBIEE Tricks – RR_4035 SQL Error / ORA-00911: invalid character

On my current project I am implementing OBIEE 11.1.1.5 with OBI Applications 7.9.6.3. I am running the Data Warehouse Administration Console 10.1.3.4.1 and Informatica 9.0.1 HF2 on Microsoft Windows Server 2008 R2 Standard Edition. Once I followed all of the installation and configuration steps I was able to run my first full execution plan to load my data warehouse. HUZZAH!

My spirits dimmed considerably when I ran my first incremental load and three mappings failed with the following error.

READER_1_1_1> RR_4035 SQL Error [
ORA-00911: invalid character

When looking at the SQL sent by Informatica to the database I saw this.

READER_1_1_1> RR_4011 Informatica inner/outer join syntax found, SQL query converted to [SELECT
(list of columns)
FROM
(list of tables)
WHERE
(list of conditions)
$$Hint_Tera_Post_Cast=]

It is this last line that causes the problem. At first glance it appears that Informatica has failed to properly replace a parameter with its value, but when I looked at the top of the log file the problem became clearer.

DIRECTOR> VAR_27028 Use override value [$$Hint_Tera_Post_Cast=] for mapping parameter:[$$Hint2].

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Following the trail I next looked to see if the parameter file created by the DAC was setting this improperly, but it was not. I even broke out my favorite hex editor to confirm that each carriage return and line feed was intact and that this was not some Chr(10) vs. Chr(13) + Chr(10) issue. Each parameter mentioned was on its own line and there was a full carriage return and line feed between them.

$$Hint2=
$$Hint_Tera_Post_Cast=

Having unsuccessfully researched the problem on Oracle Support and Google I broke down and opened a service request. A mere two days later I had the solution. Oracle BI Applications 7.9.6.x requires the Informatica PowerCenter Services and DAC Server to be co-located on the same machine. It seems, however, that the DAC Server is still configured to pass parameter files to Informatica workflows using a method that enables the DAC and Informatica Servers to be on different machines.

The <DAC_HOME>/conf/infa_commands.xml file contains the Informatica commands the DAC uses to invoke workflows and pass along the parameter file.

• “START_WORKFLOW_8” starts workflows with the -lpf option which allows DAC and Informatica Servers to be on different machines.
  

  This is known to fail when there is a very long parameter in the parameter file. If you experience Informatica failures because of missing parameters use alternative option below.

• “START_WORKFLOW_8_ALTERNATIVE” starts workflows with the -paramfile option which requires the DAC and Informatica Servers to be running on the same machine.
  

  If you prefer this approach, move _ALTERNATIVE suffix from this name to previous.

Swapping these two entries as described within the comments in the file, followed by restarting the DAC Server, solved the problem. In the end the file should look like the one below.