Tag Archives: Oracle

Getting More From Oracle Support – Get Hot Topics

It really can be annoying logging into Oracle Support every day, multiple times, just to try to view the latest bugs, patches, and information center updates. What happens if you skip a day or a week of logging into the web-based interface? You are then just that far out of sync with the ever changing world of Oracle software. But don't fret. You can actually get daily email sent to your inbox like my daily feeds seen in the image below.

OBIEE Administration Tool – Import Metadata shows no schemas

Importing Metadata with the Administration Tool

The client-only install of the OBIEE 11g Administration Tool is installed with a set of OCI libraries. This means that it can support basic Oracle Database interaction, without the need for a full Oracle Client installation on the machine. For example, you can update row counts in the Physical layer of the RPD of tables that are on Oracle.

Unfortunately, the supplied OCI libraries are not complete, which leads to a rather tricky problem to diagnose. When you use the Import Metadata operation (either from the File menu, or context menu on an existing Connection Pool), the step (“Select Metadata objects”) which ought to show the schemas just shows a stub, and no schemas.

No schemas shown in Select Metadata Objects / Import Metadata

No error is shown by the Administration Tool, giving the erroneous impression that there just aren’t any schemas in the database.

Missing OCI library

The Administration Tool writes a log, which by default is in the following rather long path: C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orainst\diagnostics\logs\OracleBIServerComponent\coreapplication\Administrator_NQSAdminTool.log

If you examine the log, you’ll see this error:

[2011-12-16T15:10:12.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: ] [tid: 8b4]  [nQSError: 93001] Can not load library, oracore11.dll, due to, The specified module could not be found. [[
.
The specified module could not be found.

]]
The key bit in this is “Can not load library, oracore11.dll“. This is the library which is missing and on which there is a dependency. The library isn’t provided by InstantClient, so you must install the full Oracle Client.

 

Installing the Oracle Client

Download the Oracle Client from the Oracle Website. The link is currently this, but may change. In this instance I downloaded “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)” (all 600MB+ of it).

 

Unzip the installer and run setup.exe. If you want as minimal an installation as possible, then select the custom installation, and choose just the “Oracle Call Interface (OCI)” option.

Oracle Client installer - OCI libraries

Once you’ve installed the Full Client, restart the AdminTool and the Import Metadata function will now work.

 

Footnote – tnsnames.ora

Don’t forget that if you don’t install the full Oracle Client and use the OCI functionality provided by the OBIEE installation alone, you will need to configure your tnsnames.ora file in C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\network\admin\tnsnames.ora. The exception is if you are using Easy Connect DSNs (dbserver:port/sid) in your RPD rather than TNS entries (orcl etc)

Footnote – troubleshooting library issues

Microsoft’s SysInternals suite includes the program ProcMon. You can point this at a running process and see what it’s up to in terms of file access, DLLs, and networking. It is great for detecting things like:

  • Which files a process writes to (eg where is a user preference stored)
  • Check which PATHs are being searched for an executable / library
  • Which tnsnames.ora is being picked up
  • What network connections are being made, or failing
  • Registry key access

When you run ProcMon you’ll realise how much is going on in the background of your Windows machine – there’ll be screenful upon screenful of output. To focus on the target of your analysis, use the Include Process option to just show AdminTool.exe:

Include AdminTool in procmon traces

You can then see things like it searching for the oracore11.dll which it is missing:

oracore11.dll missing

The next entry shows the log file being updated, giving you the path if you didn’t know it already:

AdminTool log file

Reminder: OBIEE 10g premier suppport ending soon

It has been 19 months since the GA of OBIEE 11g and its third incarnation (11.1.1.6) is celebrating its one-month-birthday today. Time to make a point on the support situation for all OBIEE 10g implementations out there:

The last 10gR3 versions (10.1.3.4.x) will reach the end of the Permier Support lifecycle in 4 months, July 2012.
 

The full documentation for lifetime support of Fusion Middleware products can be found here, on the Oracle website (last updated February 2012). The screenshot above can be found on page 13 of the pdf.

So to wrap up: the product has had more than one and a half years to mature, Oracle provided us with two new version since the release and with the end of premier support quickly coming up, this is really the moment to start thinking about upgrading your 10g solutions!

Event Triggers in BI Publisher 11g

Event Triggers in BI Publisher 11g give the facility to call a function in Oracle either before or after a data set is refreshed. The function must return a boolean (true/false), and if it returns false the data model will abort execution.

In this article I will demonstrate how to pass a parameter through to a function in the database, and write this parameter to a table in the database. This could be useful for auditing the use of the system. The parameter that I will use is a system variable, User locale, that can be accessed through the BI Publisher System Variable :xdo_user_report_locale. There are several others available (see list here), and you can also reference your own parameters that you define in the dataset.

In this very simple example, we will write an entry to an audit table every time the data set is refreshed. The table we will use is defined as follows:

--Create table
CREATE  TABLE bip_audit_log  (action_desc VARCHAR(255), action_ts DATE);

The interface between the BI Publisher Event Trigger, and writing to the Database table, is a custom PL/SQL package. You can write your own depending on what you want to achieve with the Event Trigger. If you use parameters, then make sure they are declared globally (and see the note below about matching up parameter names, if you are trying to use parameters defined within the data model).

Here is the example package:

-- This is the package declaration.
-- The global variable is essential if you want to pass parameters in.
CREATE OR REPLACE PACKAGE bip_audit AS
g_input varchar2(255);
FUNCTION beforeDatasetRefreshWithInput(g_input IN VARCHAR2)
  RETURN BOOLEAN;
END;
/

-- This is the package body - the function
CREATE OR REPLACE PACKAGE body bip_audit AS
FUNCTION beforeDatasetRefreshWithInput(g_input IN VARCHAR2)
  RETURN BOOLEAN AS
BEGIN
  INSERT  INTO bip_audit_log
    (
      action_desc,
      action_ts
    )
    VALUES
    (
      'Locale : '
      || g_input,
      sysdate
    );
  COMMIT;
  RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
  RETURN false;
END;

END;
/

Before you use your package, you should test it:

-- test function
-- Use this to invoke the function
DECLARE
  result BOOLEAN;
BEGIN
  result := bip_audit.beforedatasetrefreshwithinput('foobar');
END;
/
-- Now select from the table, and make sure we got an entry
SELECT action_desc,         TO_CHAR(action_ts,'YYYY-MM-DD HH24:MI:SS')
from bip_audit_log;
/

Once you have created the PL/SQL package, create a New -> Data Model in BI Publisher/OBIEE and go to the Data Model Properties page and set the Oracle DB Default Package to that which you have created. Make sure that the PL/SQL package is accessible from the Default Data Set connection that is defined for your Data Model.

Build your Data Set as required, and save the Data Model.

Now go to Event Triggers, and click on Create new Event Trigger.

Name the event trigger Write locale to audit log. Set the Type to Before Data, and Language as PL/SQL.

In the lower pane of the window, under Available Functions you should see the name of the package that you set in the Properties page. If you don’t, check that the package exists as defined in Properties, and can be accessed by the user and connection defined as the Default Data Source for the Data Model.

Underneath the package, you should see the function that you defined, listed in upper case. Highlight the function and click the right arrow to move it into the Event Trigger box on the right.

Now manually edit the Event Trigger text to replace the part in brackets:

G_INPUT:VARCHAR2

with

:xdo_user_report_locale

(note the colon prefix)


Save the changes to your data model, and then click on view XML, and click Run.

When you clicked Run, the Event Trigger should have fired, and you’ll see the results of the Data Set as normal. To confirm the success of the Event Trigger, check the contents of the audit table:

SELECT action_desc,         TO_CHAR(action_ts,'YYYY-MM-DD HH24:MI:SS') from bip_audit_log;

Result:

ACTION_DESC          TO_CHAR(ACTION_TS,'
-------------------- -------------------
Locale : en_US       2011-12-01 16:26:56

This completes the simple example, having shown how to pass a parameter through to a function via an Event Trigger. However, read on below to see details of the problems you may expect to encounter deviating from the very narrow example above.

Gotcha!

The above works fine, unless you add your own parameter to the dataset (regardless of whether you try to use it in the event trigger).

If you try to use the above Event Trigger, in a Data Model in which a Parameter exists, you’ll get this error:

PLS-00302: component 'NEW_PARAMETER_2' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

(where New_Parameter_2 is the name of the parameter that exists in the data set).

I would imagine that the workaround for this would be to amend the package function to also accept the parameter names also defined in the report, even if they’re not used by the function.

Using your own parameters

If you want to use your own parameter (rather than a system variable as above), then from my testing it appears that the issue described here in 2007 (MOS DocID 859980.1) still holds: your Data Set parameter name must match the name of a global parameter defined in your package function.

So in the above example, a parameter defined in the data set as g_input would work as input to the Event Trigger :

BIP_AUDIT.BEFOREDATASETREFRESHWITHINPUT(:g_input)

But changing the parameter name in the Data Set, and its corresponding reference in the Event Trigger, would not:

BIP_AUDIT.BEFOREDATASETREFRESHWITHINPUT(:my_new_parm_name)

This throws the error :

Message	oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 2, column 11:
Supplemental Detail	PLS-00302: component 'MY_NEW_PARM_NAME' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Why this doesn’t apply to passing through system variables (for example, xdo_user_report_locale seen above), I don’t know.

Troubleshooting

Watch out for unhelpful error handling within BI Publisher. If there’s a problem with the XML generation (for example – but not limited to – the Event Trigger call being incorrect), expect to get this kind of thing:

XML Parsing Error: mismatched tag. Expected: </img>.
Location: http://rm-win01:9704/xmlpserver/servlet/xdo
Line Number 2, Column 580:<table style='background=c[...]

The annoying thing about this is that the actual error is tantalisingly close – if you scroll to the end of the line, you’ll see that it truncates:

oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 4, column 51:

So to find out the actual error, go to Enterprise Manager (FMC) and under your Web Logic Domain, navigate to your bi_server and right-click, Logs -> View Logs

Within here you should find the error, and by taking advantage of the ECID (Execution Context ID), you can drill through to related log entries. In this instance, that includes both the error at the BI Publisher level and that reported back from the database.

Version

The above was all tested on BI Publisher 11.1.1.5.

Documentation

Event Triggers are covered in the documentation here

store non-OBIEE objects in the catalog

Thanks to John Minkjan (obiee101.blogspot.com) I was able to disclose (and secure) non-OBIEE content on a dashboard.

Using the regular Upload functionality you can easily upload a (for instance) PDF file into the Shared Folders of the webcatalog.

The awkward thing is, that if you put a link on your dashboard and use the Browse button to navigate to the particular folder, you don't see the PDF file since it is not an OBIEE object.

A workaround for this is to create a (dummy) analysis and store it in the same folder as the PDF. Then, as described above, put a link on your dashboard and navigate to the dummy analysis. As a final step, (since you now have the path to the folder where the PDF resides) replace the name of the OBIEE analysis with the PDF's name.
There you go ! Now you can have a link on your dashboard to non-OBIEE objects and use your security model on it !

Kind regards,
René