?
Solved

java stored procedures

Posted on 2005-03-22
9
Medium Priority
?
323 Views
Last Modified: 2010-03-31
Hi
    I need some feed back on how to use jave stored procedres..

basically i've written a pl/sql procedure that uses utl file functions to load a csv file into a oracle database.  A sheel scipt is used to call the plsql procedure that loads it from a particular directory. all this works fine.

I need  the stored procedure to do the following..as it parses each record in the file and loads it inot the oracle database, i need it to connect to a postgres database and do an updtate...i have the jar files to connect to a postgres database..i've heard this can be accomplished using java stored procedures ..so basically have the plsql stuff and the postgres stuff within the java stored poc and call the java stored proc from the shell script.

..can anyone help me here?..an example would be good











 
0
Comment
Question by:desiboy1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 29

Accepted Solution

by:
bloodredsun earned 1000 total points
ID: 13599746
With a stored procedure, all the logic is performed on the database in sql as I'm sure you already know. All Java can do is call the stored procedure, pass parameters if necessary and then deal with any resultses or update counts depending on the results on the stored proc.

Here's an example of calling a stored proc in java, taken from http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
:

try
{
    int age = 39;
    String poetName = "dylan thomas";
    CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
    proc.setString(1, poetName);
    proc.setInt(2, age);
    cs.execute();
}
catch (SQLException e)
//your exceptions
{
    // ....
}
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13599792
Typo
>>cs.execute();
prov.execute();
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13599850
Can you exaplain exactly what you want to do? DO you want the java app to read a file and then connect to the Oracel db AND the PostGres Db?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 19

Assisted Solution

by:Jim Cakalic
Jim Cakalic earned 1000 total points
ID: 13601009
I think he's talking about Oracle's "Java in the database" feature that has a JVM embedded in the database engine so that you can write stored procedures as Java classes -- not simply PL/SQL. I've not done it myself but I'm sure there's some information in Oracle's knowledge base. I'll do some research. In the meantime, here's an article on the topic to whet your appetite:

http://www.developer.com/java/data/print.php/3337411

Jim
0
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 13601130
Here's quite a few samples on Oracle's technet site:
http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html

An article from Oracle Magazine (pay particular attention to the links at the bottom)
http://www.oracle.com/technology/oramag/oracle/03-jan/o13java.html

I've got to get to work. More later perhaps.

Regards,
Jim
0
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 13797611
OK by me.
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13798194
I'm happy...:-)
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question