I have convert all the procedure and function present in my schema into single package.  The problem is there is a type present in that schema like  
CREATE OR REPLACE TYPE chararray as VARRAY(20) of NVARCHAR2(200); the client ask me to include this too in package.  I tried execute immediate too , but gives error.  How to inlude this into the package?

By Suman

You can do it by 2 way.......
1> using a java-oracle object at java end what exactly maps with your VARRAY at DB end.
2> using oracle.sql.STRUCT object. (that one is easy ). see the following code .
 
at oracle side you have 
 
CREATE OR REPLACE TYPE CLIENT_OBJ AS OBJECT (
   Client_Id varchar2(20),
   Client_Name varchar2(100),
   Location_id varchar2(20),
   Client_city varchar2(100),
   Client_street varchar2(100),
   Client_zip varchar2(50),
   Client_Entity_DunsName varchar2(100),
   Client_Entity_DunsNumber varchar2(100));
   
CREATE OR REPLACE TYPE CLIENTARRAY is VARRAY(10000) OF CLIENT_OBJ;
 
at your case the object only contains a single attribute of NVARCHAR2() type.
java code: 
 
you need to import the following class/ interface
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.jdbc.OracleTypes;
 
You have to use OracleCallableStatement  instead of java.sql.CallableStatement .
 
public void addtoDataBaseList(Vector clientListForDataBaseInsert) throws SQLException
 {
OracleCallableStatement callableStmt = null;
  try{
   
   String sql="{ call PKG_NAME.Proc/fnc_name(?) }";
   callableStmt = (OracleCallableStatement)con.prepareCall(sql);
   
   STRUCT[] structArray = new STRUCT[clientListForDataBaseInsert.size()];
   StructDescriptor strDesc = StructDescriptor.createDescriptor("CLIENT_OBJ // name of the db object", con);
   
   for (int i=0; i<clientListForDataBaseInsert.size(); i++)
   {
    Client client = (Client)clientListForDataBaseInsert.get(i);
    Object [] clientarray= new Object[8];
    clientarray[0]=client.getClientID();
    clientarray[1]=client.getClientName();
    clientarray[2]=client.getLocationcode();
    clientarray[3]=client.getAd().getAddress_city();
    clientarray[4]=client.getAd().getAddress_Street();
    clientarray[5]=client.getAd().getAddress_zip();
    clientarray[6]=client.getDi().getEd().getEntityDUNSname();
    clientarray[7]=client.getDi().getEd().getEntityDUNSnumber();
    STRUCT tempStruct = new STRUCT(strDesc,con,clientarray);// it will map your java object to the oracle types object
    structArray[i]=tempStruct;
   }
// it will map your java object array to the oracle types object array

   ArrayDescriptor arrDesc =  ArrayDescriptor.createDescriptor("CLIENTARRAY // name of db array/nested table/index by table ",con);
   ARRAY arr = new ARRAY(arrDesc,con,structArray);
// set the populated array
   callableStmt.setARRAY(1, arr);
   callableStmt.execute();
   callableStmt.close();
   callableStmt = null;
   //con.commit();
 

  }
  catch(SQLException e)
  {
   //
  }
  finally{
   //this.closeConnection();
 
 }
 }

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Home
Oracle Database, SQL, Application, Programming Tips