Generate a Web Service Client from a WSDL using Jdeveloper

I recently had to code one half of an automated service which would connect to a Webservice and retrieve data based on the parameters sent. It needed to be converted back to XML from a Java Object and stored in a local directory. It wasn’t an easy one to start with as it involved security/authentication certificates which although I have had exposure to with OID/Single Sign on and external Tiers in the E-Business Suite, I had never really got to grips with, after this little project I sure have.

The other half which I didn’t have to code consisted of java stored procedures in an Oracle database which loaded the XML file , validated it and then loaded it into some target tables. This was all to remove the manual process of downloading and uploading the file which needed down by an end user daily. I’ve played around with this on my own time and will expand on this tutorial of sorts in the future to cover that.

First thing is first, download the latest version of Jdeveloper, I’ve used 11g Release 2

http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html

Finding an example to use isn’t that hard, there are plenty of websites which list publicly available webservices.

http://www.xmethods.net
http://www.webservicex.net/ws/default.aspx

I’ve decided to use the stock quote example , the wsdl can be found at the below

http://www.restfulwebservices.net/wcf/StockQuoteService.svc?wsdl

That’s enough links for now !!!

Open up Jdeveloper

Choose New Application > Custom Application

Enter an Application Name

Select Next

Enter Project Name

Click Finish

Right Click the newly created project and select New.

Under Categories, ‘Select Web Services’

Under Items, ‘Web Service Client and Proxy’

 

 

For this it was left as default. JAX-WS Style

Select Next
It is here you paste in the address of the WSDL.

Copy WSDL into Project.

 

Click Next to go through the other options but for me I choose to Finish.

Jdeveloper will start importing in the required packages and classes and building the client.

You should now have something like the above in your project.

The basic code for the client is ready

Thanks to my excellent naming it looks like I’ve ended up with quite the mouthful for the client file , oh dear, never mind.

StockQuoteService defines a Java interface that represents the service. It defines a method that returns a stub for the service.

IStockQuoteService defines a Java interface that represents the methods of the Web Service. These methods will match up with the elements in the WSDL

The client as its created first creates an instance of the StockQuoteService class which implements the interface which represents the service. It then retrieves a stub that represents the remote Web Service.

This is all well and good but we haven’t actually interacted with the webservice yet to get back anything meaningful.

To do this you should use the below code

Create JAXB object instance to accept the response

      StockQuote stockQuote = new StockQuote();

Populate the JAXB instance by calling the interface to the Webservice with a value, in this case  the ticker symbol for Oracle

      stockQuote = iStockQuoteService.getStockQuote("ORCL");

What you then do with the object is very much open, you can take individual elements and print them to a text file after converting them to a string. I choose to output the whole object as an XML file by using the JAXB Marshaller class which is used to convert java objects to XML 

I added the below to my imports for the class BasicHttpBinding_IStockQuoteServiceClient

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;

I added this entry to the StockQuote.java above the class definition to allow the Marsharller to have a root reference

@XmlRootElement(name="StockQuote")

The last part was to write the code to convert the object and output to the console, you can see below the code and result of running this.

This was just a very basic attempt at interacting with a webservice that I thought I’d write up, hopefully it gets some people started and over a few bumps when using jdeveloper.

 

 

Oracle Database 10g Administrator Certified Professional

Well today as part of a large Oracle event at my work in which you were able to go and sit some Oracle exams i took the 10g Admin II exam and passed only missing a few questions.

The amount of work to get ready for it was a lot more than i expected. I've been doing quite a few development projects of late which have been taking up a lot of time as I rediscover the wondeful world of Java and learn webservices.

Luckily i was able to cram in enough revision and only missed 3 questions. The timing couldn't be better. I've a backlog of posts to make about creating java clients for webservices, deploying a pl/sql procedure as a webservice on weblogic and posting the useful topics I've made notes on while revising.

And did I mention i've a new baby due next week, let the fun begin

: ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist

We had this error in a test environment where they were trying to run a few things through workflow mailer in the Ebusiness Suite. It was quite a recent clone but for the most part, I prefer not to have workflow on without reason in test systems without the over-ride email address set.

I've been in the position in the past where a junior DBA has cloned, forgotten to do this or not reset the workflow accounts and workflow has sent out emails to customers and also connected to the live account and moved emails into the discard folder.

There was a direct hit for the error on Metalink [ID 886377.1] which was tried but didnt resolve the issue. On checking then if the workflow queue existed under applsys it wasnt found so it was time to rebuild the queue.

The below should bring back 3 objects under applsys, table, queue and synonym.


select owner, object_type, status
from dba_objects
where object_name='WF_NOTIFICATION_OUT';



I've rebuilt the out queue hundreds of times and its usually down to issues in the INBOX with spamming mail killing the workflow IMAP connection to the account and it 'hangs'. Another popular one is customers replying to a workflow email or copying the address in on a reply to their manager. Nothing is sure to stop the workflow mailer from working as quick.

To rebuild the out queue automatically you would run the below.

select distinct  tablespace_name
from dba_indexes,dba_queues
where index_name like 'WF%N1'
and table_name=queue_table
and name like 'WF%';

connect to SQL*Plus as applmgr user

@$FND_TOP/patch/115/sql/wfntfqup.sql APPS APPSPW APPLSYS

this rebuilds the queue, now to rebuild the index.

connect as applsys , replace question marks with value from first select.

CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE ?????????????;

If you want to rebuild it yourself as I did manually you will want to follow Metalink Note [400493.1]

Change owner of Ebusiness Print Jobs

 

I'm going to have to create a post just to cover printers in the Ebusiness Suite as its an area that will always come up and most people try to avoid it like the plague, i know i did at the start.

I now feel very confident when it comes to looking at printers and setting up new ones but only after a LOT of practise and being tested by lots of scenarios.

In this situation the request was to change the owner of the print job which was sent from Oracle EBS to the print queue. The reason behind this was a new approach to printing, they needed to tie each print job to a unique user.

This was all on Linux using CUPS. Like anything sent from Oracle I didn't think it was possible to change the owner so the only hope was adding information to the print job somehow. We confirmed that you couldn't change the owner with Oracle in an SR.

Printing from Ebusiness is exactly like than printing from the OS (operating system) .
So the owner will be a unix/linux owner .

My intial searches took me to a lot of third party software companies looking after printing and they had articles asking their customers to add information to the PJL-Header information which they could then work with. The problem for me most of these were mostly aimed at SAP and there were solutions out there to handle this common risk.

I tried to get some help in the OTN forums and had a good dialogue going with one of the Guru's : https://forums.oracle.com/forums/thread.jspa?threadID=2517369

This led me to the system administrators guide for printers where i started concentrating on the print command strings.

The system administrator working with the printers initially couldn't use the changes we made to the string, but new what we were trying and suggested we tried using the -U parameter. Now i still cannot find this documented anywhere around the LP print command , maybe a Linux/Unix Guru can enlighten me, but this changed the owner of the print job inside the print job information even though it was coming from 'oracle'.

The solution then was to change the standard driver and we were in business.

OLD VALUE
lp -c -d$PROFILES$.PRINTER -n$PROFILES$.CONC_COPIES -t"$PROFILES$.TITLE" $PROFILES$.FILENAME



NEW VALUE
lp -c -d$PROFILES$.PRINTER -n$PROFILES$.CONC_COPIES -U$PROFILES$.ORIGUSERNAME -t"$PROFILES$.TITLE" $PROFILES$.FILENAME

Windows native operating system authentication for Oracle

While logged into a Windows 2008 server which had an Oracle Database installed, i was trying to get logged in as sysdba using O/S authentication.

I kept getting insufficient priviliges in the error message even as the owner of the oracle software and being in the ora_dba administrator group.

At the time i had been patching their weblogic and fusion middleware tiers and was finishing on the database. I'd completed the task on training environment the day before without issue and this was now their UAT system. I could not find anyone who could tell me the sys password or where i could find it to try to get in through external authentication.

Luckily for me over lunch, my manager who was also onsite remembered hitting the issue before due to a setting in the sqlnet.ora file and after lunch we had a look.

#SQLNET.AUTHENTICATION_SERVICES=(nts)
SQLNET.AUTHENTICATION_SERVICES=(none)
Changing the above around fixed the issue and so i had to go and figure out the why as i wasn't familiar with this setting.

Oracle Documentation Example

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

nts for Windows native operating system authentication

Clear as day, it needs to be set to NTS for windows o/s authentication, i have a feeling i'll remember this setting from now on.

Setting up a Sandbox environment for Oracle in VMPlayer

The following assumes you want an environment which will have an oracle database installed in a windows virtual environment. Whether this is going onto a laptop which will be connecting to multiple wireless networks or a desktop, chances are you’ll be using DHCP to get your IP. Since you’ll be using your VM for Oracle, you’ll want to have a static IP setup in your VM and it’s better to do it at the start than trying to reconfigure later, trust me.

You may also need a static ip address to connect to the guest from the host and possibly from other Virtual Machines on your host.

I’ll not cover installing Windows on a VM , that is covered all over the internet and no point reinventing the wheel.

For this scenario I would recommend setting your VM to use a Host Only adapter. I’ve found this is the simplest solution for getting a local play area set up on VMware Player. You will not have internet in your guest VM but that’s hardly the main reason for this setup. (A Bridged adpater may be the answer but I’ve found trying to configure it difficult as the network tool doesn’t install by default )

When installing VMware Player, it creates three virtual network interfaces, VMnNet0, VMnet1 and VMnet8.  VMnet1 is the 'host only' interface which we will be using and you can see this in your hosts network adapters. It will already have an IP assigned(I’ve found it doesn’t use this IP range when assigning  IP’s  to the VM’s, currently it’s a mystery to me but I think its probably done through the network tool).

The good thing is that it keeps the same IP for the VM as long as it’s around. Once configured with a host-only network card I’ve found my VM’s will keep the same IP. I’ve tested this starting them in different orders and copying them and starting them up, each time the existing VM’s maintain their IP and the new VM;’s are assigned a new one.

The new step before installing the oracle software is to configure your virtual windows environment with a loopback adpater as per oracle instructions.

Here is a summary for Windows 2003 or Windows XP:

1. Open the Windows Control Panel.
2. Double-click Add Hardware to start the Add Hardware wizard.
3. In the Welcome window, click Next.
4. In the ‘Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
5. In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
6. In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
7. From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
8. In the Select Network Adapter window, make the following selections:
• Manufacturer: Select Microsoft.
• Network Adapter: Select Microsoft Loopback Adapter.
9. Click Next.
10. In the The wizard is ready to install your hardware window, click Next.
11. In the Completing the Add Hardware Wizard window, click Finish.
12. If you are using Windows 2003, restart your computer.
13. Right-click My Network Places on the desktop and choose Properties. This displays the Network Connections Control Panel.
14. Right-click the connection that was just created. This is usually named "Local Area Connection 2". Choose Properties.
15. On the General tab, select Internet Protocol (TCP/IP), and click Properties.
16. In the Properties dialog box, click Use the following IP address and do the following:

1. IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:
• 192.168.x.x (x is any value between 0 and 255)
• 10.10.10.10
2. Subnet mask: Enter 255.255.255.0.
3. Record the values you entered, which you will need later in this procedure.
4. Leave all other fields empty.
5. Click OK.
16. Click OK.
17. Close Network Connections.
18. Restart the computer.
19. Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts file with the following format, after the localhost line:

          IP_address   hostname.domainname   hostname

where:

• IP_address is the non-routable IP address you entered in step 16.
• hostname is the name of the computer.
• domainname is the name of the domain.

            For example:

10.10.10.10   mycomputer.mydomain.com   mycomputer

This way oracle will always resolve its connection correctly.

The last step is to install the Oracle Software and then test your connection from your host.

At the end, you should be able to connect from your host desktop to the guest VM over remote desktop or through a SQL client like SQL Developer. The Guest IP will not change so you could add a shortcut to your hosts file. In the Guest VM, the loopback adapter will resolve all traffic back to the guest IP and oracle will have no issues no matter what network your connected to on your host.

In the below example i've used ORADBHOST1 for the install and that is what is present in my tnsnames.ora.

 

Checking status of an Index Rebuild

Without getting into a debate about whether an index needs rebuilt as this is an area that i'll get into in the future once i've learned a LOT more, sometimes its good to be able to know how an index rebuild is going.

SELECT MESSAGE
FROM V$SESSION_LONGOPS
WHERE SID IN (SELECT SID
                             FROM V$SESSION
                             WHERE USERNAME='SYS' AND STATUS='ACTIVE')
ORDER BY START_TIME;

If you have a spare couple of hours and want to learn more about indexes , Index Internals would be a good place to start where some very good case studies are carried out and myths challenged.