ViralPatel.net

View Comments

• This is great article

• Hi this is sridhar, I executed the above program and stored procedure but I am getting the malformed sql 92 exception I am not getting aware of where the error is whether it is from java side or oracle zide could you please help me thanks for all

  Thanks & regards
  Sridhar

• Hi Viral,
  I couldn't find any reply from you, please help me regarding the above issue.

• Hi

  I'm Try to execute the above code
  i getting error while creating procedure:
  CREATE OR REPLACE PROCEDURE SchemaName.proc1 (p_array IN array_table,
  len OUT NUMBER,
  p_arr_int OUT array_int)
  AS
  v_count NUMBER;
  BEGIN
  p_arr_int := NEW array_int ();
  p_arr_int.EXTEND (10);
  len := p_array.COUNT;
  v_count := 0;
  FOR i IN 1 .. p_array.COUNT
  LOOP
  DBMS_OUTPUT.put_line (p_array (i));
  p_arr_int (i) := v_count;
  v_count := v_count + 1;
  END LOOP;
  END;

  ---------------------------------
  Error report:
  ORA-01435: user does not exist
  01435. 00000 - "user does not exist"
  *Cause:
  *Action:

• Try to remove "SchemaName." from the script, this is only a placeholder.

• Very nice article, Vibhati.
  However, for this case, I won't extend the nested table by 10. It should either be extended by p_array.COUNT or each time by 1 inside the loop. It would avoid returning null values.

• Hi Thanx,
  nice post,
  I have tried it but it is showing null or blank. it never show correct data.

• Your example use array, can you give an example use ArrayList as input?

• I'm afraid you cannot do that. You need to convert arraylist to array and then pass the array to stored procedure.

• Hi Joe,
  Object -
  [code language="sql"]
  CREATE OR REPLACE type obj_employee AS object (
  EmpNo INTEGER,
  EmpName VARCHAR(50),
  datet DATE);
  [/code]
  Array -
  [code language="sql"]
  create or replace type Arr_Emp as Table of obj_employee;
  [/code]
  Java class.
  [code language="java"]
  public void ArrayListToOracleArray() {
  logger.info(dbSchemaName);
  try {
  ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("ARR_EMP", conn);
  obj_employee obj1 = new obj_employee("OBJ_EMPLOYEE", 1, "Bharat", new Date(new java.util.Date("03-MAY-13").getTime()));
  obj_employee obj2 = new obj_employee("OBJ_EMPLOYEE", 2, "Singh", new Date(new java.util.Date().getTime()));
  ArrayList ob = new ArrayList();
  ob.add(obj1);
  ob.add(obj2);
  ARRAY array = new ARRAY(arraydesc, conn, ob.toArray());
  CallableStatement cstm = conn.prepareCall("{ call SP_TEST_RESULT_ENT_SAVEDATA_V1(?,?) }");
  ((OracleCallableStatement) cstm).setARRAY(1, array);
  cstm.registerOutParameter(2, java.sql.Types.VARCHAR);
  cstm.execute();
  System.out.println(" Test " + cstm.getString(2));
  System.out.println("Please check database");
  } catch (Exception e) {
  System.err.println("dothis method exception: " + e.getMessage());
  }
  }
  [/code]
  Employee java class
  [code language="java"]
  public class obj_employee implements SQLData, Serializable {

  private String sql_type;
  private int EmpNo;
  private String EmpName;
  private Date date;

  public obj_employee(String sql_type, int EmpNo, String EmpName, Date date) {
  this.sql_type = sql_type;
  this.EmpNo = EmpNo;
  this.EmpName = EmpName;
  this.date = date;
  }

  public String getSQLTypeName() throws SQLException {
  return sql_type;
  }

  public void readSQL(SQLInput stream, String typeName) throws SQLException {
  this.sql_type = typeName;
  this.EmpNo = stream.readInt();
  this.EmpName = stream.readString();
  this.date = stream.readDate();
  }

  public void writeSQL(SQLOutput stream) throws SQLException {
  stream.writeInt(EmpNo);
  //stream.writeBinaryStream(new BufferedInputStream(new ByteArrayInputStream("bharat".getBytes())));
  stream.writeString("Bhaart");
  stream.writeDate((java.sql.Date) this.date);
  System.out.println(" This Name : " + this.EmpName);
  }
  [/code]
  .i am facing a problem. EmpName is showing blank in DB. but other field is woking fine.

• Nice article,,,

• Hi,

  Is there any way to bind package level associative arrays in java code with hash map.

  Please help.
  I am trying to do the following.
  create or replace
  package hr_pkg
  as
  type charArray is table of varchar2(255) index by varchar2(10);
  end;

  --Java--
  Map test = new HashMap();
  test.put("PLAN", "2012");
  test.put("ORDER", "9999");
  ArrayDescriptor des = ArrayDescriptor.createDescriptor("hr_pkg.charArray", con);
  ARRAY array_to_pass = new ARRAY(des,con,test);

  When i execute this, i recieve

  java.sql.SQLException: invalid name pattern: hr_pkg.charArray.

  Any work around for this?

  Thanks