Apr 11 2016

How to enable Examine (Diagnostics menu) in Oracle eBS

To be able to use the examine option in Oracle eBS you need to set the following Profile settings :

  • Profile option name :

Hide Diagnostics menu entry

Value :

No

  • Profile option name :

Utilities:Diagnostics

Value :

No

After that switch responsibility or logout and login and you will have a new menu item under the “Help” menu.

To also have the “About this page” link in the OAF Pages also set the following profiles options:

FND: Diagnostics = “Yes”

Personalize Self-Service Defn = “Yes”

FND: Personalization Region Link Enabled = “Yes”

Disable Self-Service Personal = “No” (Only at Site)

 

 

Mar 14 2016

ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

When you encounter the following issue when creating a trigger :

Error report –
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 – “ORACLE server session terminated by fatal error”
*Cause: An Oracle server session was in an unrecoverable state.
*Action: Log in to Oracle again so a new server session will be created
automatically. Examine the session trace file for more
information.

Try the following solutions :

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

Or in Oracle SQL Developer:

  1. Go to Tools | Preferences
  2. Select Database | PL/SQL Compiler
  3. Change the PLScope identifiers from All to None
  4. Click on Ok

May 28 2015

Informatica Powercenter : Default Target load type

You can set the default Target load type for your sessions to ‘Bulk’ or ‘Normal’. This way you do not have to edit them all when the default is set to the wrong value šŸ™‚

To do this open de Workflow manager and click on the Tools menu > Options Ā and then select Miscellaneous, you can select the default value there.

May 21 2015

Informatica Powercenter : Convert String to timestamp with milliseconds

When you want to convert a String to a timestamp with milliseconds and you are only getting zero’s for milliseconds in your database make sure that the following option is disabled in the session (Config Object tab) :

2015-05-21 12_35_18-Edit Tasks

If this is enabled the milliseconds will be truncated.

You can use the following formula to convert a string to a timestamp with milliseconds :

to_date(‘2013-09-01 12.14.16.627000’,‘YYYY-MM-DD HH24.MI.SS.US’ )

 

Aug 28 2014

Informatica Powercenter : SQL Hints in lookup SQL Override

By default you cannot use the following characters in an lookup sql override : ‘/*’

Because of this you cannot enter SQL Hints. You will get an error message when running your mapping. This is because the informatica parser doesn’t recognize these special characters.

To enable SQL hints in a lookup sql override you can set a custom property in the Integration Service in the Administrator Console.

  • Select theĀ Integration Service.
  • Under the Properties tab, click Edit in the Custom Properties section.
  • Enter a new property
  • Name :Ā LookupOverrideParsingSetting
  • Value :Ā 1.
  • Click OK.

In version 9.5 there is no need to restart the IS.

Do check if other existing overrides still function as normal because i came accros an override that was going into error when we added the property. There was a TO_CHAR funtion in that query and it got messed up by informatica.

Another solution is to use a Source Qualifier as the source of your lookup. A SQ does support ‘/*’ characters by default.

Aug 20 2014

Where to find Oracle Business Intelligence Applications (OBIA) Release 11.1.1.8.1 Documentation

You can find the most important OBIA documentation at the following locations :

Oracle Business Intelligence Applications (OBIA) Release 11.1.1.8.1 Documentation LibraryĀ 

Major topics :

  • Install Guide
  • Configuration Guide
  • ETL Guide
  • Administrator’s Guide
  • Security Guide
  • Release Notes

OBIA 11g: Data Lineage Documentation for Oracle Business Intelligence Applications 11.1.1.8.1

BI Applications 11.1.1.8.1 Data Model Reference Guide

Major Topics :

The information provided in this document includes data modeling concepts, nomenclature, table and column descriptions, and star schema diagrams. This information is useful for understanding the functional areas that are mapped in the Oracle Business Analytics Warehouse.

BI Applications 11.1.1.8.1 Content Guide

Content Guide includes following topics for BIApps 11.1.1.8.1:

Section 1, “Oracle Business Intelligence Applications – Licenses and Products
Provides a list of all Products within Oracle Fusion BI Applications

Section 2, “Oracle Business Intelligence Applications – Dashboard
Provides a list of dashboards for each application along with their paths

Section 3, “Oracle Business Intelligence Applications – Dashboards and Reports
Provides a list of dashboards and reports for each application along with their exact paths

Section 4, “Oracle Business Intelligence Applications – All Reports
Provides a complete list of reports including those not exposed in any dashboard page

Section 5, “Oracle Business Intelligence Applications – Subject Areas
Provides a high level overview of the subject areas included in each application

Section 6, “Oracle Business Intelligence Applications – Facts & Dims by Application
Provides a list of fact and dimension relationships for each presentation catalog. The user can filter on a particular dimension table and find all its fact tables across presentation catalogs and vice versa

Section 7, “Oracle Business Intelligence Applications – Webcatalog Function Security
Provides a list of report name, path, associated users, duty/roles and permissions

 

Aug 18 2014

Convert UTC datetime from SQL Server to local timezone (CET) in Oracle.

For a project at a customer I had to load data from a SQL SERVER database to a Oracle database. It turned out that the SQL SERVER database stored the date and time data in UTC. This became apparent when the data was reported via Business Objects and all the date time data was off by 1 or 2 hours depending on the daylight saving time. So how do we fix this ?

We cannot just add +2 hours (we are in +2 timezone) because this will fail when daylight saving is active.
It would be possible to keep track when daylight saving is active but that changes overtime and that would mean that you have to maintain the function.

So this is how i fixed it :

Step 1:

Create a connection in powercenter for your target database and set the connection attribute ‘Connection Environment SQL’ to :

ALTER SESSION SET TIME_ZONE=’UTC’

This will make sure that the data is written as UTC datetime stamp in Oracle and not as the local timezone.
If you need to convert from another timezone you need to enter that timezone instead of UTC.

Step 2:

Read the datetime column from SQL SERVER via powercenter and insert this data into a oracle column with datatype ‘Timestamp with local timezone’ using the connection defined above.

Step 3:

When you query the data from Oracle you need to make sure you set your session to (im my case) CET.
Because the datatype is ‘Timestamp with local timezone’ Oracle will internally convert the UTC datetime to CET. CET is also daylight saving time aware so the dates are converted correctly with or without daylight saving active.
When queried it will display the data in the local timezone (CET in this case).
If you are using Business Objects to read the data you can set your session in the universe and the report will show the correct data.

Example Daylight saving :

  • 18-08-2014 12:00:00 is entered in the applicationĀ (daylight saving active so +1)
  • This is stored in SQL SERVER as 18-08-2014 11:00:00 (UTC)
  • Set writing session to UTC.
  • Insert into oracle asĀ 18-08-2014 11:00:00 (UTC)
  • Set query session to CET
  • Query Oracle andĀ 18-08-2014 12:00:00 will be displayed.(daylight saving active so +1)

 

Example No Daylight saving :

  • 01-01-2014 12:00:00 is entered in the applicationĀ (Timezone +2)
  • This is stored in SQL SERVER as 01-01-2014 10:00:00 (UTC)
  • Set writing session to UTC.
  • Insert into oracle as 01-01-2014 10:00:00 (UTC)
  • Set query session to CET
  • Query Oracle and 01-01-2014 12:00:00 will be displayed.(daylight saving not active so +2)

 

May 22 2014

Extend Informatica Powercenter Designer workspace

Did you ever run into the problem that your workspace is not expanding and your mapping doesn’t fit ?

Try setting the zoom level to 100% and drag a transformation to the edge of the workspace. Your workspace will extend. This is probably a bug in version 9.1.0 Hotfix 4

Apr 14 2014

Informatica Powercenter : PMREP Massupdate Commit Type value

If you need to do a mass update of sessions to change the commit type you can use PMREP. This is all explained in the Informatica Help. What is not explained in the help is what value you should enter in the -v option. Here are the values you can enter :

0 – for Source Based Commit

1 – for Target Based Commit

2 – for User Defined Commit.

 

Example command :

pmrep massupdate -t session_property -n ‘Commit Type’ -v 0 -i session_list -u session_list_log

Feb 11 2014

Informatica Powercenter : Bigint primary key violation

While loading data from a SQL Server database into Oracle i came across a problem. Everytime i did a full load of the data i got a primary key violation in the target table. This was weird because the source did not have any duplicate keys.

It turned out to be that the bigint from the source was loaded incorrectly into the target. The following value was in the source :

sourcevalue

The ID was processed by powercenter as :

sessionlog

Because the ID is truncated this causes multiple keys to be the same.

This can be fixed by setting the following option in your session:

session

 

The Bigint will now be loaded correclty.

 

Older posts «

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close