how to send an array of java objects to an Oracle stored procedure

Today, one of my friends asked me to give her some code I written a couple of years ago. Why? She needed it and she couldn’t find resources on the internet. Maybe Google does not work so well these days?

I remember that when I wrote the code, I also couldn’t find something that would solve my problem.

But let me tell what the problem was: how to send an array of java objects to an Oracle stored procedure.

And below is my resolution. Please, fell free to comment if you have found an easier way to do it or you have improvments.

In the following example I will pass an array of java.sql.Date – java.sql.Date[], but I have succesfully used for long[] and it can be used for arrays containing some other objects that can be represented in Oracle.

Basically, what you need to do can be separated in three big steps:

  1. create some custom type in your db
  2. create or replace type DATE_ARRAY as table of date
    
    
  3. create the stored procedure
  4. <code>create or replace procedure <your_procedure>  ( in DATE_ARRAY) as <whatever>;
    BEGIN
    
    <do some processing here>
    
    END;
  5. java code
  6. java.sql.Date[] sqlDates = null;// change with you array
    String sql =  "{ call <your_procedure>(?) }";
     CallableStatement cstmt = null;
     cstmt = conn.prepareCall(sql);
     // create the descriptor for array type
     ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "DATE_ARRAY", conn );
     // create the array, based on descriptor, connection and your array
     ARRAY array_to_pass = new ARRAY( descriptor, conn, sqlDates );
     // set the array
     ((OraclePreparedStatement)cstmt).setARRAY( 1, array_to_pass );
    

That’s all!

Good luck!

It’s possible to be interrested in:

Posted on 2011/02/03, in tehnic and tagged , . Bookmark the permalink. 2 comentarii.

Lasă un răspuns

Completează mai jos detaliile despre tine sau dă clic pe un icon pentru autentificare:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s