Results 1 to 6 of 6

Thread: urgent......

  1. #1
    Join Date
    Apr 2004

    Unanswered: urgent......

    Could anybody tell me what are the differences between callable statements and prepared statements?? I am not able to find anything helpful over the net. I would appreciate any quick response.



  2. #2
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    I'm not sure what you might mean in relation to Oracle directly, but from my client side Delphi work - a prepared (preparable) statement usually means it uses bind variables and can therefore be re-used (there is a bit more to it than that, but this will suffice for this explanation!).

    In summary, when a new SQL statement arrives at the database it must first be parsed (check for syntax, validity, security permissions, etc). Then the database works out an optimal plan to execute the statement and finally the statement is executed.

    With a prepared statement, the initial parsing and checks have already been carried out (and sometimes also the plan) and so the only work left is to re-execute the statement. This relies heavily on the SQL statement being exactly syntacticaly identical (the statement is hashed to determine identical statements).

    For example.

    select col1 from table1 where condition = 'a'

    is not identical to...

    select col1 from table1 where condition = 'b'


    select col1 from table1 where condition = :value using 'a'
    is identical to
    select col1 from table1 where condition = :value using 'b'

    as the statement is identical, only the conditional value has changed. In this case, the syntactical validity, security checks and plan of the first query can be applied to the second. (As I said, it is not quite this simple but will suffice).

    You should note that.
    select A....
    is not identical to
    Select a

    As they don't hash the same.

    In many client applications, queries of this form are prepared on startup. Assuming the database isn't struggling for resources, overall application response is much better. Imagine it as a message from the application to Oracle saying "prepare yourself for these sorts of questions".

    This is why you will see, time and time again, on these forums people saying "make sure you use bind variables and not literals". Once again there is a little more to it than this simple explanation, but hope you'll get the picture.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Apr 2004
    Thank a is this different from callable statements....

  4. #4
    Join Date
    Nov 2003
    Callable statements are used for calling your oracle procedures and functions.
    Prepared statements are for SQL DML queries.
    Hope this helps.

  5. #5
    Join Date
    Jul 2003
    how is this "urgent"?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2002
    California, USA
    Here is the question clarification:

    A PreparedStatement object represents a precompiled SQL statement. This object can then be used to efficiently execute this statement multiple times. It allows for variable substitution in the SQL expression - e.g., allow the values of a WHERE clause to be changed at run-time.

    CallableStatement obejcts are used to execute SQL stored procedures. JDBC provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1, second being 2, etc.


    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts