java_stored_procedure_vs.PL/SQL_procedures

Discussion in 'Java' started by marko, Feb 25, 2009.

  1. marko

    marko Guest

    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 ?
     
    marko, Feb 25, 2009
    #1
    1. Advertising

  2. In article <go3o3h$ho8$-com.hr>, "marko" <>
    wrote:

    > 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.]
    --
    John B. Matthews
    trashgod at gmail dot com
    <http://sites.google.com/site/drjohnbmatthews>
     
    John B. Matthews, Feb 25, 2009
    #2
    1. Advertising

  3. marko

    Arne Vajhøj Guest

    marko wrote:
    > 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
     
    Arne Vajhøj, Feb 26, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page