Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

java stored procedures

Posted on 2005-03-22
9
Medium Priority
?
325 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
  • 4
  • 3
7 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
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 Month11 days, 14 hours left to enroll

564 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