Win a copy of Spark in Action this week in the Open Source Projects 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
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

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: 4603
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: 4603
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.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic