java_stored_procedure_vs.PL/SQL_procedures

[marko]

What is the difference between these two ?

Isn't java stored procedure just a connection via JBDC, and a SQL call to
database, for example :

PreparedStatement stmt = connection.prepareStatement
("SELECT STANDARD_RATE " +
" FROM AVAILABLE_ROOM_TYPES WHERE HOT_ID = TO_NUMBER(?) " +
" AND ROOM_TYPE = ? ");

stmt.setString(1,hotelId); stmt.setString(2,roomType);

ResultSet rset = stmt.executeQuery();and what is faster ?

 

[John B. Matthews]

What is the difference between these two ?

I assume you mean the difference between a stored procedure and a
PreparedStatement, each performing the same query.

Isn't java stored procedure just a connection via JBDC, and a SQL
call to database, for example :

PreparedStatement stmt = connection.prepareStatement
("SELECT STANDARD_RATE " +
" FROM AVAILABLE_ROOM_TYPES WHERE HOT_ID = TO_NUMBER(?) " +
" AND ROOM_TYPE = ? ");

stmt.setString(1,hotelId); stmt.setString(2,roomType);

ResultSet rset = stmt.executeQuery(); and what is faster ?

Surely the relative performance depends on how the target database
parses, caches and plans to execute the query. What are your results?

[Please don't post tabs.]

 

[Arne Vajhøj]

What is the difference between these two ?

Isn't java stored procedure just a connection via JBDC, and a SQL call to
database, for example :

PreparedStatement stmt = connection.prepareStatement
("SELECT STANDARD_RATE " +
" FROM AVAILABLE_ROOM_TYPES WHERE HOT_ID = TO_NUMBER(?) " +
" AND ROOM_TYPE = ? ");

stmt.setString(1,hotelId); stmt.setString(2,roomType);

ResultSet rset = stmt.executeQuery();and what is faster ?

Java can actually be used in at least two ways with Oracle.

The code using the the Oracle DB can be Java code that access
the database via JDBC.

That is what your code example it.

But Oracle support Java in another way.

Instead of writing your stored procedure in PL/SQL then you can
write in in Java.

Oracle DB has a JVM embedded and can execute Java stored procedures.

I am far from an Oracle expert and I can not say what is fastest.

I would probably use:
- PL/SQL for stored procedures that are SQL statement oriented
- Java for stored procedures that are really code oriented (string
manipulation, math, etc.)

Arne