Following are the steps to create a java stored procedure which invokes a web service.
Database : Oracle 11g
Jdev version : 10.1.3.3.0
Prerequisite : Set up oracle 11g database to call webservices . (refer my previous post)
Open jdevelepor and follow the steps below.
Step 1 : Create a generic application with no template
Step 2 : Create a project
Step 3 : Right click on project–> Go to project properties–>click technology scope
Step 3.1: Shuttle Database and Web Services technologies to right side and say ok.
Step 4 : Right click on project and click New
Step 4.1: In categories, expand business tier and select webservices
Step 4.2: Select Web Service Proxy from items on right side and say ok.
Step 4.3: In Create Web Service Proxy wizard, step1 : enter the wsdl url and check copy wsdl into project checkbox
Step 4.4: In step 5 of create web service proxy wizard, u may change the package name as u want. click on finish.
Step 5 : Select the new proxy that is created in the navigator. In structure window, you can see all the related sources.
Step 6 : Select the Client.java file that is created.
Step 7 : In this class, you can find a client method which can be called from main to test whether your webservice is working fine or not.
Step 7.1: Create a static method and get the _port object using code similar to above client method. you can use this port object to invoke the service.
Step 8 : call this new method from main method and run the java file to know if its working fine
Loading the created java classes to database as a stored procedure.
Step 1 : Right click on project and click New
Step 2 : In new gallery, click on general–> click on deployment profile –> Select Load Java and Java Stored Procedures and click ok
Step 3 : give some name to storedproc
Step 4 : Uncheck all options in Load Java Options, Privileges and Resolver tabs.
Step 5 : Under Resources, you can see a .deploy file. Right click and say Add PLSql Package and give some name to your package.
Step 6 : Right click on the plsql package and click on Add Stored Procedure and select the static client method that we created.
Step 6 : Right click on .deploy file, and click deploy to (database to which u want to deploy).
Step 7 : Check the deployment window to see if the package has been successfully created!
Package should be created successfully.
You may use the java stored procedure in this package anywhere to call the web service
PS : In my case ,in the package body created, it is not using the fully qualified name(including package) while referring the Java class.
so when I tried to call the procedure, it gave me class not found error
So I updated the created package body to include the java package name as well before the class name and could see the output correctly.