Thursday, February 6, 2014

How to pass array from java to stored procedure which accepts argument of type varray ?

Oracle
---------
I think Mysql wont support varray as per my knowledge.

1)Create varray type
CREATE OR REPLACE TYPE STUDENTARRAY as varray(20) of varchar2(50) 

2)Create table with new type studentarray

create table studentsvarraytable(
     starray studentarray

);

3)Create procedure which accepts argument of type studentarray

CREATE OR REPLACE PROCEDURE VARRAYPROC ( svarray in studentarray )

is

   begin

        insert into studentsvarraytable values ( svarray );

   end;

4)Pass array from java code in to pl/sql procedure which inserts the passed
    students in to table

Code:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbcsample;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;


/**
 *
 * @author Rishitha
 */
public class ArrayPassingClass {


 

    /**
     * @param args the command line arguments
     */
    public static Connection getConnection() {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
                    + "otherwise set the respective database driver jar file.In netbeans right click on"
                    + "libraries,click on add jar/folder and provide your jar file");

        }
        Connection connection = null;

        try {
            //XE -- Here Give your database name
            //hr -- Give Your database username
            //hr -- Give your database password
            //You have to add ojdbc.jar in your classpath

            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:XE", "hr",
                    "hr");
            System.out.println("Hurrah got connection " + connection);

        } catch (SQLException e) {

            System.out.println("Please check if database name username password are correct ?");

        }
        return connection;

    }

   

    public static void main(String args[]) throws Exception {
       Connection con = getConnection();
       ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STUDENTARRAY",con);
       String[] students = new String[] { "Anilkumar", "Sunilkumar" };
        ARRAY array = new ARRAY ( desc, con, students );
       
      String stmtString =
        "begin VARRAYPROC(?); end;";
      CallableStatement cstmt = con.prepareCall( stmtString );
      cstmt.setArray( 1, array );
      cstmt.execute();
    }

}

5)Select * from studentsvarraytable;




No comments:

Post a Comment