Oracle Tip: Oracle Warehouse Builder Workflow Installation

To my surprise, Oracle Workflows were not installed in my current Development  environment (OWB Tahoe Development Version 11.2.0.3 and Oracle Database 11.2). I found out when I tried to deploy a new workflow. I was asked to input connection  details and when I tested the connection, I got the following error:
 "The connection was succesful but failed to retrieve workflow version." 

After Googling it, I discovered you have to install the Oracle Workflow Server by executing the wfinstall.bat script. You can find it in <OWB_HOME>\wf\install.

The screenshot shows a Windows installation. You have to input the following parameters:

• Workflow Account: OWF_MGR is the default user
• Workflow Password: Choose a password for the Workflow Manager User
• SYS Password: password for SYS user 
• TNS Connect Descriptor: specify connection details in the format <host>:<port>:<SID>

and then click on Submit. You do not have to create the DB user previously, the process will create it automatically.
At the end of the process, you will receive an encouraging message "Workflow Configuration has completed successfully".

Now, you would probably try to deploy the workflow again. At least, this is what I did and I got this error:

 RPE-02260: Database User OWF_MGR must be a Control Center User. Please use the OWB Design Client against the Control Center repository to  grant the Control Center User role.

So you first need to register the OWF_MGR user as a Workspace user (in Oracle Warehouse Builder -> Administration -> Repository Assistant). Finally, you will be able to create and deploy your workflows.

Oracle Tip: OWB Workspace Installation Error

I am starting a new project and I need to use Oracle Warehouse Builder. I have been given a 64bit Windows machine. A 32 bit version of OWB had already been installed, but when I tried to run it I got the following error:

The application was unable to start correctly (0xc0000018). 
After asking Google and Oracle Forums for explainations, I realized I had to download and install  OWB 11 for Microsoft Windows 64-bit (http://www.oracle.com/technetwork/es/developer-tools/warehouse/downloads/index.html).
I have downloaded it and I installed it. I needed to create a new Workspace, but after a few steps, I got the error:

The Warehouse Builder workspace owner installation failed on user OWBSYS. ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: OWBSYS is not granted access to <OWB_HOME>\repos.properties: Please run UnifiedRepos/reset_owbcc_home.sql

What is that? I have discovered that since the new installation was forced to be on a different PATH from the original, the OWBSYS user did not have access to the .properties file and installation could not go further.
So, I did what suggestes. I have located the set_owbcc_home.sql script and I run it as SYSTEM.

This is the script output:

SQL> @reset_owbcc_home.sql
Enter the full path of the Oracle home for the OWB Control Center install.
If you are installing in a Windows environment, please ensure that the case of t
he path exactly matches the Oracle install path including the drive letter.

OWB Control Center Home:
D:\product\11.2.0\dbhome_2
PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

The script asked me for the Oracle Warehouse Builder path, as shown.

I then retried the Workspace creation and it worked fine.

Oracle Tip: ORA-30657 External Table Error

I was trying to load a CSV file into an external table. I copied the definition of the table from the final target table, adding the external table parameters: 

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>'));

When I compiled the table, I got the following error. 

ORA-30657:  Operation not supported on external organized table  

Cause: User attempted on operation on an external table which is not supported.  

Action: Don't do that!

Quite hilarious... but what did I do wrong? The Oracle error message did not help me at all (and who could ever help?). I checked the directory. It was there and its name was typed correctly. File names were ok. So what was the issue? 

It was as easy as removing the NOT NULL clause on the field specification.  

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>')

Silly error, but it took me a while to find out what was wrong. Hope this helps. 

Generar Documentación con ODI

Recién se acaba de publicar mi post sobre como generar documentación con ODI, en castellano.

http://blog.avanttic.com/2013/07/02/generar-documentacion-con-oracle-data-integrator/

Enjoy!