This week's book giveaway is in the General Computing forum.
We're giving away four copies of Emmy in the Key of Code and have Aimee Lucido on-line!
See this thread for details.
Win a copy of Emmy in the Key of Code this week in the General Computing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

Call Oracle Procedure from Java on iSeries

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
 I have a requirement to get some data from an Oracle database to a file on an IBM iSeries (AS400, IBMi or whatever they are calling it these days!)
 My Oracle administrator has created a procedure on the Oracle database that I need to run and then from the resulting CLOB that the procedure creates, I can then run a simple SQL extract for the data that I need.

 My java skills are pretty much non-existent  but I have managed to create the java code below that will accept multiple parameters from the iSeries machine and then connect to the relevant Oracle database (we have 3 IBM machines and 3 Oracle databases (Dev, Quality & Production).

So I know that my connection works but I am getting all kinds of errors when I come to call the procedure on the Oracle server.

My code is as follows....




The above should connect to the Oracle database (which it does!) and then call the procedure passing in 2 parameters (Part & Lot).
The procedure should run and send back a return code
        Return Codes I need to check for:

   

Depending on the returned code, I would either run an SQL statement to pull the data from the newly created CLOB or I would send an email to report a failure.
The error I receive at the moment is...



 Some forums point to an authority issue but I am told that this cannot be the case.
Can anyone please assist?

Thanks,



 
 
Rancher
Posts: 4301
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you login to the Oracle server with that username and password and execute that procedure?
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes.  connecting and logging in is no problem.
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All is fixed.  I had a typo when calling the Procedure - my apologies!!!
 
Dave Tolls
Rancher
Posts: 4301
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What I meant (for future reference) is logging into the Oracle instance itself with the username and password, using something like SQL Developer, then trying to execute the stored procedure exactly as written (using cut and paste), modifying the '?' as needed.

That would show you whether the procedure exists or not.
It helps sort out permissions and (as you had) typos.
 
Goodbye moon men. Hello tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!