mercoledì 27 novembre 2013

Oracle Tip: BI Publisher Report URL without Login

My current customer wants to integrate BI Publisher reports in its web portal. They want to publish URLs that allow users to view reports, skipping the login page.
We can retrieve the Report URL by selecting any of Share Report Link options.

 Copy the URL and add the parameters id and passwd for logging into BI Publisher.

http://<server>:<port>/xmlpserver/Components/<Report_Name>.xdo?id=<user_id>&passwd=<user_password>&_xpf=&_xpt=0&_xdo=<Path to the Report>.xdo&_xmode=4&_params<parameters_if_any>&_xt=<Layout_Name>&_xf=analyze&_xana=view

The main disadvantage of this method is that user credentials are not encrypted. This is not the safest method on earth, but it works and it made my customer happy.

giovedì 14 novembre 2013

Oracle Tip: Oracle Warehouse Builder (OWB) import table error

I have installed the latest version of Oracle Warehouse Builder 11g 64 bit on a Windows pc (my work pc, actually) since I needed to connect to a customer OWB ETL implementation. 
Everything looked ok (I could connect to the OWB Workspace, navigate through the project, open and deploy mappings, etc.), since I have tried to import new tables in the Oracle Schema. 
As soon as I clicked on Import Database Object, I got the following (and cryptic) error:

SQL Exception 
Persistent Layer Error: SQL Exception
Class name: CacheMediator
Method Name: getSQLResult
Error Message from Persisten Layer: ORA-02085
If I clicked on OK, the next step was a grey window with much more known error Java null pointer exception.
I really needed to import the new tables I had created to be able to work, so I started Googling the error, with no success. After a considerable amount of time I got on this note:

"Note the property "User Global Name", it specifies the unique name of the database, which is composed of the database name and the domain, in the form of database_name.database_domain. If you see the error ORA-02085: database link *** connects to *** during the import of metadata of database objects into OWB. It may mean the database has the system parameter global_names set to true, but you did not select the option "Use Global Name" of the Oracle location, as a result, the generated database link name for the import doesn't include the global database name and hence cannot access the database."

Basically I needed to specify the DB global name in each location that specified a connection to the DB. 
If you do not know the global name of your DB, this SQL query can give you a precious hand. 

SELECT * FROM global_name;

So unregister your location, specify the DB global name option and register the location again.

lunedì 4 novembre 2013

Oracle Tip: Generating Documentation Reports with Oracle SQL Developer Data Modeler

This is another tip for the lazy programmer. When it comes to documentation, no developer would like to go back extracting the code for every single table in his model.
Oracle SQL Developer Data Modeler has a very useful tool for generating reports.

Selecting Reports from the File Menu, you can choose if you want it generated in:
  • RTF
  • PDF 
  • HTML 
You can create a template (e.g. if you want to select only tables of your models) or selecting only some objects of your model.
When you execute the report, it will be stored in a local route.
This is how a PDF report looks like. Ready to be sent to the customer or do some nice cut&paste from it.