Oracle SQL – BLOB to XML Type ( ORA-06502 PL/SQL : numeric or value error : raw variable length too long )

I am working with a database schema where xml content is being stored in a BLOB and I’m working on querying that XML.
When you cast the BLOB to VARCHAR, you’ll run into the limit of 2000 characters with the following error:

ORA-06502 PL/SQL : numeric or value error : raw variable length too long

So this trick came in handy:

SELECT XMLTYPE( BLOB_FIELD, 871 ) AS AWESOMEXML FROM YOUR_TABLE;

Please be very aware of the character set that you are using, you can enter a world of hurt when you’re using the wrong one. The number 871 is the character set UTF8, which we are using.
For other character sets, check http://www.mydul.net/charsets.html

Weblogic does not start, java.lang.NumberFormatException, EmbeddedLDAP

I encountered the following exception when trying to start our AdminServer today:

<May 30, 2013 1:04:27 PM CEST> <Critical> <WebLogicServer> <BEA-000386> <Server subsystem failed. Reason: java.lang.NumberFormatException: null
java.lang.NumberFormatException: null
 at java.lang.Integer.parseInt(Integer.java:454)
 at java.lang.Integer.parseInt(Integer.java:527)
 at weblogic.ldap.EmbeddedLDAP.validateVDEDirectories(EmbeddedLDAP.java:1104)
 at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:242)
 at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
 Truncated. see log file for complete stacktrace
>
<May 30, 2013 1:04:27 PM CEST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED>
<May 30, 2013 1:04:27 PM CEST> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down>
<May 30, 2013 1:04:27 PM CEST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN>

This does not really explain what went wrong, but the solution is to remove the replicas.prop file and restart the AdminServer again.
The replicas.prop file is located at :
$DOMAIN/servers/AdminServer/data/ldap/conf/replicas.prop
Rename (move) replicas.prop to replicas.prop.bak and retry the startWeblogic. In my case this solved the not starting server.

How to try to drop a table without raising exception ORA-00942: table or view does not exist

With the piece of code below it’s quite easy to first make sure your table exists before dropping it, so you’re not running into SQL Error: ORA-00942: table or view does not exist
Script explanation: It will check if the table is present before trying to drop it.

-- Drop table
DECLARE
 l_count NUMBER;
BEGIN
 SELECT COUNT(1)
 INTO l_count
 FROM ALL_TABLES
 WHERE table_name = 'TABLE_NAME'
 AND owner = 'USERNAME';
 IF l_count > 0 THEN
 EXECUTE IMMEDIATE 'Drop table USERNAME.TABLE_NAME CASCADE CONSTRAINTS';
END IF;
END;
/

Oracle Database 11gR2, quick and easy

It’s been a while since the last article and I just found myself being enthousiastic about a neat Oracle hands on again. So while it’s downloading I might as well tell you guys about it.
Last year I have been working with Oracle XE a lot, but some features are not available in the express edition (XE), so some queries might not run as expected or simply don’t run at all. Oracle has a VM ware image, which provides a really nice solution for this problem:
They have created a database application environment inside a Virtualbox VM image, with the following specifications:

  • Oracle Linux 5
  • Oracle Database 11g Release 2 Enterprise Edition
  • Oracle TimesTen In-Memory Database Cache
  • Oracle XML DB
  • Oracle SQL Developer
  • Oracle SQL Developer Data Modeler
  • Oracle Application Express
  • Oracle JDeveloper
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

So if you’re looking for a nimble way to quickly run a OraDB on your laptop, without the hassle of installing all these components, this might just be what you’re looking for.
You can download the Oracle Developer Days 11g DB image here:
http://download.oracle.com/otn/other/virtualbox/dd/Oracle_Developer_Day.ova
The only prerequisite is that you have Oracle Virtualbox installed on your machine;
But this is easily installed via this link
 
In the coming days I will post another article, which will describe a neat way of connecting to this database!
oracle-11g-express-edition
The original article can be found here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Oracle Service Bus SFTP: BEA-381801 InvalidHostFileException

I have just been working on an OSB SFTP business service, and got this error while testing the service.
While googling, I found out that you need to create a known_hosts file inside your OSB domain. The easiest way of creating a known_hosts file for me, was to use Cygwin and create a ssh (or sftp) connection to the destination.

Steps for the solution:

  • Cygwin: log in to remote system via ssh (or sftp)
  • Copy known_hosts file from ~/.ssh to OSB directory
  • Test business service

Cygwin: login to remote system

Use this command to login to the remote system:

ssh username@hostname

Optionally: Type ‘yes’ when asked if you trust this host
This will create an entry in your known_hosts file in the .ssh folder in your cygwin home directory
This is a (scrambled) contents of the known_hosts file:

joris@jorisworkstation/cygdrive/c/Oracle/Middleware/user_projects/domains/joris_domain/config/osb
$ cat ~/.ssh/known_hosts
10.200.242.65 ssh-rsa <<INSERT A LOT OF GARBLEGARBLE>>

Copy the known_hosts file to OSB

First we need to create the SFTP transports directory in the domain home:
Go to <<domain_home>>/config/osb and create this structure: (Only create the directories in RED)

- <<domain_home>>
- config
- osb
- transports
- sftp

We are now ready to copy the known_hosts file:
In cygwin:

 cp ~/.ssh/known_hosts /cygdrive/c/Oracle/Middleware/user_projects/domains/joris_domain/config/osb/transports/sftp

In Windows: Find known_hosts in your cygwin home folder (in my case: in C:\development\program\cygwin\home\joris.visscher\.ssh\known_hosts) and copy it to the newly created “sftp” folder in your domain home.

Test business service

All steps are now done, we can test the business service!

Additional information:

You will encounter the following error in your logs when this error occurs:
com.sshtools.j2ssh.transport.InvalidHostFileException

####<18-ott-2012 10.57.33 CEST> <Error> <SFTPTransport> <JORISWORKSTATION> <osb_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <cef9dbcf0859648a:31ba6f:13a730795ec:-8000-000000000000014f> <1350550653493> <BEA-381801> <Si ▒ verificato un errore per l'endpoint com.bea.wli.sb.transports.TransportException: com.sshtools.j2ssh.transport.InvalidHostFileException: Il file known_hosts non esiste o non dispone di autorizzazioni di lettura.
 com.bea.wli.sb.transports.TransportException: com.sshtools.j2ssh.transport.InvalidHostFileException: Il file known_hosts non esiste o non dispone di autorizzazioni di lettura.
 at com.bea.wli.sb.transports.sftp.connector.SFTPTransportProvider.sendMessage(SFTPTransportProvider.java:155)
 at com.bea.wli.sb.transports.sftp.connector.SFTPTransportProvider.sendMessageAsync(SFTPTransportProvider.java:110)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at com.bea.wli.sb.transports.Util$1.invoke(Util.java:83)
 at $Proxy122.sendMessageAsync(Unknown Source)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageAsync(LoadBalanceFailoverListener.java:148)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageToServiceAsync(LoadBalanceFailoverListener.java:603)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageToService(LoadBalanceFailoverListener.java:538)
 at com.bea.wli.sb.transports.TransportManagerImpl.sendMessageToService(TransportManagerImpl.java:566)
 at com.bea.wli.sb.transports.TransportManagerImpl.sendMessageAsync(TransportManagerImpl.java:434)
 at com.bea.wli.sb.test.service.ServiceMessageSender.send0(ServiceMessageSender.java:380)
 at com.bea.wli.sb.test.service.ServiceMessageSender.access$000(ServiceMessageSender.java:79)
 at com.bea.wli.sb.test.service.ServiceMessageSender$1.run(ServiceMessageSender.java:137)
 at com.bea.wli.sb.test.service.ServiceMessageSender$1.run(ServiceMessageSender.java:135)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
 at com.bea.wli.sb.security.WLSSecurityContextService.runAs(WLSSecurityContextService.java:55)
 at com.bea.wli.sb.test.service.ServiceMessageSender.send(ServiceMessageSender.java:140)
 at com.bea.wli.sb.test.service.ServiceProcessor.invoke(ServiceProcessor.java:454)
 at com.bea.wli.sb.test.TestServiceImpl.invoke(TestServiceImpl.java:172)
 at com.bea.wli.sb.test.client.ejb.TestServiceEJBBean.invoke(TestServiceEJBBean.java:167)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl.__WL_invoke(Unknown Source)
 at weblogic.ejb.container.internal.SessionRemoteMethodInvoker.invoke(SessionRemoteMethodInvoker.java:40)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl.invoke(Unknown Source)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl_WLSkel.invoke(Unknown Source)
 at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:667)
 at weblogic.rmi.cluster.ClusterableServerRef.invoke(ClusterableServerRef.java:230)
 at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:522)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
 at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:518)
 at weblogic.rmi.internal.wls.WLSExecuteRequest.run(WLSExecuteRequest.java:118)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:252)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
 Caused By: com.sshtools.j2ssh.transport.InvalidHostFileException: Il file known_hosts non esiste o non dispone di autorizzazioni di lettura.
at com.bea.wli.sb.transports.sftp.client.KnownHostVerifier.validateKnownHosts(KnownHostVerifier.java:144)
at com.bea.wli.sb.transports.sftp.client.SFTPClient.authenticate(SFTPClient.java:108)
 at com.bea.wli.sb.transports.sftp.connector.SFTPResource.<init>(SFTPResource.java:78)
 at com.bea.wli.sb.transports.sftp.resource.SFTPUtils.createSFTPResource(SFTPUtils.java:170)
 at com.bea.wli.sb.transports.sftp.resource.SFTPConnectionPool.getResource(SFTPConnectionPool.java:104)
 at com.bea.wli.sb.transports.sftp.connector.SFTPTransportProvider.getSFTPResource(SFTPTransportProvider.java:958)
 at com.bea.wli.sb.transports.sftp.connector.SFTPTransportProvider.sendMessage(SFTPTransportProvider.java:145)
 at com.bea.wli.sb.transports.sftp.connector.SFTPTransportProvider.sendMessageAsync(SFTPTransportProvider.java:110)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at com.bea.wli.sb.transports.Util$1.invoke(Util.java:83)
 at $Proxy122.sendMessageAsync(Unknown Source)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageAsync(LoadBalanceFailoverListener.java:148)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageToServiceAsync(LoadBalanceFailoverListener.java:603)
 at com.bea.wli.sb.transports.LoadBalanceFailoverListener.sendMessageToService(LoadBalanceFailoverListener.java:538)
 at com.bea.wli.sb.transports.TransportManagerImpl.sendMessageToService(TransportManagerImpl.java:566)
 at com.bea.wli.sb.transports.TransportManagerImpl.sendMessageAsync(TransportManagerImpl.java:434)
 at com.bea.wli.sb.test.service.ServiceMessageSender.send0(ServiceMessageSender.java:380)
 at com.bea.wli.sb.test.service.ServiceMessageSender.access$000(ServiceMessageSender.java:79)
 at com.bea.wli.sb.test.service.ServiceMessageSender$1.run(ServiceMessageSender.java:137)
 at com.bea.wli.sb.test.service.ServiceMessageSender$1.run(ServiceMessageSender.java:135)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
 at com.bea.wli.sb.security.WLSSecurityContextService.runAs(WLSSecurityContextService.java:55)
 at com.bea.wli.sb.test.service.ServiceMessageSender.send(ServiceMessageSender.java:140)
 at com.bea.wli.sb.test.service.ServiceProcessor.invoke(ServiceProcessor.java:454)
 at com.bea.wli.sb.test.TestServiceImpl.invoke(TestServiceImpl.java:172)
 at com.bea.wli.sb.test.client.ejb.TestServiceEJBBean.invoke(TestServiceEJBBean.java:167)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl.__WL_invoke(Unknown Source)
 at weblogic.ejb.container.internal.SessionRemoteMethodInvoker.invoke(SessionRemoteMethodInvoker.java:40)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl.invoke(Unknown Source)
 at com.bea.wli.sb.test.client.ejb.TestService_sqr59p_EOImpl_WLSkel.invoke(Unknown Source)
 at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:667)
 at weblogic.rmi.cluster.ClusterableServerRef.invoke(ClusterableServerRef.java:230)
 at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:522)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
 at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:518)
 at weblogic.rmi.internal.wls.WLSExecuteRequest.run(WLSExecuteRequest.java:118)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:252)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

Create sample XML message from XSD in Eclipse (Free solution!)

On an Oracle Service Bus project you are often supplied with XSD’s or WSDL’s when you’re integrating with clients. The customer or client expects you to test their web service but before you do, you might want to take a look at the XML which should be sent to make sure you’re sending compliant XML.
One way to do this is by importing the WSDL in SoapUI and creating sample test requests, this comes in quite handy when you are actually using a WSDL, but what if the customer only supplies an XSD? SoapUI does not support creating sample XML from an XSD element.
There are several commercial products which can solve this problem but if you’re only looking for generating sample XML messages from XSD, you might want to look at using Eclipse’s built in XML generating option:
First step is to import the XSD in your project, the right click the Schema and select “Generate” and then “XML”

Screenshot - Eclipse - Generate XML from XSD
Screenshot – Eclipse – Generate XML from XSD

You will be presented with a “New XML file” dialogue which lets you save your new XML sample. Choose any location you want, and click “Next”. Then you will be presented with the options for your new XML sample. Select the options you desire and press “Finish”
Screenshot - Generate XML - New file dialogue
Screenshot – Generate XML – New file dialogue

Your new XML file has been generated and ofcourse is compliant with your XSD right now!

How to start with Oracle Service Bus (OSB) development

This post is meant to explain the few steps you need to take to start with Oracle Service Bus (OSB) development on a Windows 7 laptop. The OSB version used is 11g, although these steps are also suitable for OSB 10gR3 and Aqualogic Service Bus (ALSB) as well.
There are as many ways of organizing a project as there are people, this is the way I have chosen and this by no means is meant as “the way to go“. It is merely meant as a guideline, (so please do adapt where you find it more suitable for your needs)
(You can find the installation instructions for an OSB 11g development environment in this blog post)
There are two OSB projects which you can create when you open the Oracle Enterprise Pack for Eclipse (OEPE):

  • Oracle Service Bus Project
  • Oracle Service Bus Configuration Project

An OSB Project contains the actual project which you will create. These are often functionally bound and represent a hierarchical structure similar to the structure on your hard disk.
An OSB Configuration Project represents one domain on which you will deploy your projects. These Configuration Projects contain common settings (like a pointer to an SMTP server)

Create an OSB Configuration Project

The first step in a every new OSB Workspace is to create a new configuration project. Later on when you create OSB projects you need to choose the Configuration Project to which they will belong.
Side note: OSB Configuration Projects can be used as pointers to a OSB Domain (or a local domain)
Within Eclipse, right click the empty space in the Project Explorer and choose “New” and then “Oracle Service Bus Configuration Project”. For now we will not use the advanced functions so name your Configuration project to a name suitable for you (for me that mostly means “OSB Configuration”) and press “Finish”
In a new Workspace you will get the question if you would like to switch to the “Oracle Service Bus perspective”, choose “Yes”.
The above steps are the same for every project. I keep my workspaces aligned with the domains to which I deploy.
This means a new workspace for each domain, I often work with common schema’s and services (e.g. loggers / mailers) which I place in a separate project. Although I do re-use a lot of services, I found it better to keep this lean and not to tightly coupled so I create a new Common Services project for each domain instead of creating ONE Project to rule them all.

Create an OSB Project

This paragraph will focus on creating an OSB Project and will contain a sample service.
Right click the empty space in the “Project Explorer” and choose “New” and then “Oracle Service Bus Project”
For the start of every new project you will create the following structure:

  • Project name (In this case “OSB Sample Project”)
    • Business Service
    • Proxy Service
    • Schema
    • Transformation
    • WSDL

This leads to this view:

Screenshot - OSB Eclipse - Project Explorer Sample Project
Screenshot – OSB Eclipse – Project Explorer Sample Project

This is the basis you need for creating Oracle Service Bus services. I will expand in further detail in later posts.

Create a local development OSB domain

If you do not have a development environment available or prefer to test your service locally you can also create a OSB domain on your local machine. The next paragraph leads you through the creation of a new OSB domain via Eclipse.
We will use the “Configuration Wizard” which will let us create a fresh new local Weblogic domain on our computer.
Press “Start” / the Windows button and enter “Configuration Wizard” which will show the start menu entry in the OSB menu folder:

Screenshot - Start Menu - Weblogic Configuration Wizard
Screenshot – Start Menu – Weblogic Configuration Wizard

If all went well, you will be presented with this choice:
Choose “Create a new Weblogic domain” and click “Next” to continue to the product selection:
Screenshot - Weblogic Configuration Wizard - Product Selection
Only select “Oracle Service Bus for developers – 11.1.1.6 [Oracle_OSB1]”, this will automatically select the dependencies as well. Click “Next” to continue to the Domain name and location screen:
Screenshot - Weblogic Configuration Wizard - Domain Name and Location
Tip: I tend to use a domain name which indicates that I am working on my own machine as well as states the product and version which is deployed on the Weblogic server. The domain name is quite visible in the web console and this way I’m sure I’m working on the correct domain.
Enter the domain name you wish and press “Next” to continue to the admin user name and password screens:
Screenshot - Weblogic Configuration Wizard - Admin user name and password
Since 11g the password is bound to rules which prohibit you from choosing a really simple password. So with this added difficulty make sure you pick one which you will remember!
When you’re done, click “Next” on every screen until you get to the configuration summary.
Screenshot - Weblogic Configuration Wizard - Configuration Summary
The configuration is done when you select “Create”, your domain will then be created, after which you will be presented with this summary:
Screenshot - Weblogic Configuration Wizard - Finished
Click “Done” to exit the configuration wizard. We will not start the Admin server yet.
We can now continue to configuring Eclipse to use this domain.

Add our local OSB domain to Eclipse

This paragraph explains how to configure an OSB domain in Eclipse. It is possible to configure a remote (DEV) domain here which is running on another server. I would advise to only configure local domains within your Eclipse, a DEV environment is often shared among colleagues and you might overwrite or tamper with your fellow colleague his or her services without knowing it.
As a guideline: Use the DEV environment to test project exports and see if those are working without problems.
Start Eclipse and choose your workspace.
First activate the “Servers” tab in the bottom window, as shown in the screenshot below:
Screenshot - OSB Eclipse - Servers tab
Then right click the empty space in the window and select “New” and then “Server”.
Just to make sure you don’t encounter strange behavior when you deploy to production, you should always choose and use the same version for development and deployment. In this example we choose “Oracle WebLogic Server 11gR1 (10.3.6)”:
Screenshot - OSB Eclipse - New local domain
Again: Although it is not necessary, I do like to rename the domain name (Server’s host name) to a value which will show me that I’m working local and on which version.
In the following examples we will also log into the Application server Weblogic via the web console. This Weblogic domain is also named “local_osb_11g” which again helps you to identify the domain when you’re shifting between local / DTAP environments.
For your consideration: Depending on your needs you can later on create several local domains. If you are working on two projects at the same time it makes sense to create two domains.
Most of the time I re-use one domain since you can redeploy all projects in your workspace within a minute. This way you are also absolutely sure that your local configuration is always the way you expect it to be.
In general: Most clients will use one configuration profile for all OSB domains.
After pressing “Next” you will be presented with the Domain Directory selection dialogue, where you will need to adjust three settings:

  1. Choose “Local” as “Server type”
  2. Choose your newly created domain via the dropdown and “Known Domains”
  3. Select “Always start Weblogic server in debug mode” to ease debugging later on

Screenshot - Eclipse Domain configuration - Directory Configuration
Press “Next” to select the resources which we will deploy on our local domain.
Screenshot - Eclipse Domain Configuration - Add resources
Add the Configuration Project you wish and click “Next” in the next three screens. Click “Finish” in the last screen to finalize your domain configuration:
Screenshot - Eclipse Domain Configuration - Finalize the config
If you are successful you should have a bottom window looking like this:
Screenshot - OSB Eclipse - Local OSB Domain
We are now ready to start this local server and deploy our newly created Projects.
Right click the top line (which stands for the domain) and select “Start”.
Side note: You can also start the domain via the start menu or straight through the command shell by using “startWeblogic.cmd”. In my installation, it is located at: C:\Oracle\middleware\osb11g\user_projects\domains\local_osb_11g\bin
The tab “Console” will activate and will show the details of the server starting. It will take a minute or two but when the local domain is started you will see the following line:

<10-jul-2012 1:20:47 uur CEST> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

Open a internet browser and go to the following URI to open the OSB web console to access your synchronized brand new OSB domain:
http://localhost:7001/sbconsole
After logging in with the user “weblogic” and the password you’ve chosen, it should look like this:
Screenshot - Chrome running SBConsole
You can see the OSB projects you’ve deployed to this server via the button “Project Explorer”.
Note: You can also develop new services via this SBConsole, most functions which are available via Eclipse also exist in the SBConsole.
That’s it! You are now fully equipped with a running OSB domain on your machine and can start developing and running your own web services.

Oracle Service Bus 11gR1 IDE Eclipse OEPE installation on Windows 7

Follow these steps to setup a development environment for Oracle Service Bus 11gR1 (11.1.1.6 is used in this example)
Download these components:

  • Oracle WebLogic Server 11gR1 (10.3.6) and Coherence and OEPE for Windows (32-bit) (Part Number: V24367-01)
  • Oracle Service Bus 11g (11.1.1.6.0) (Part Number: V29756-01)
I could not find these files through the Oracle.com website but you can download previous versions through the Oracle Software Delivery Cloud. These two installation files can be found in this category:
Product Pack: Oracle Fusion Middleware
Platform: Microsoft Windows (32bit)
Choose media pack: Oracle Fusion Middleware 11g Media Pack for Microsoft Windows (32-bit) 11.1.1.6.0 (Part number: B55383-41)

(Tip: Search for the Part numbers to make sure you download the correct versions)
First, install WebLogic Server with Oracle WebLogic Server 11gR1 + Coherence + OEPE and then install Oracle Service Bus.
When you install OSB, be sure to select the OEPE that is located inside the middleware directory.
After both installations, in the directory “Middleware directory/oepe_11gR1PS2” is eclipse.exe. If you execute this file, you can open Eclipse and start with creating an osb project.
Side note;
Some people can’t see the OSB perspective in Eclipse after this installation. Apparently it’s a rights issue which can be solved by navigating to the Oracle_OSB directory via Windows Explorer. You will be asked for permission to access that directory and after that dialogue you will be able to open the OSB perspective within Eclipse.

Unlock HR user and schema in Oracle Database

In an Oracle database there is a sample user called “HR”. This user comes with a schema which contains data which is quite convenient for demo purposes.
You can unlock this user using the following steps:

  1. Connect to your database with the system user
  2. Execute the following command (Note: replace “password” with your password)
    ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;
  3. Log in to the database with the HR user and your specified password

You can use this user for tutorials / demo’s in almost all Oracle databases since the tables are all equal.

Screenshot - Tables, Views and Procedures for the HR user
Screenshot – Tables, Views and Procedures for the HR user

Ps. I’ve posted this small tutorial mainly because it will serve as a prerequisite for other tutorials.