How To Include Type Into The Package

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

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.