Announcement Announcement Module
Collapse
No announcement yet.
Problem with passing complex type from java to plsql procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with passing complex type from java to plsql procedure

    Hi,

    I have the following pl/sql types defined

    Code:
    CREATE TYPE add_typ AS OBJECT ( 
       street          VARCHAR2(30),
       city            VARCHAR2(20),
       state           VARCHAR(20),
       postal_code     VARCHAR2(6) );
    / 
     
    CREATE TYPE add_list is TABLE OF add_typ;
    / 
     
    CREATE TYPE empc_typ AS OBJECT (
      employee_id       NUMBER(6),
      first_name        VARCHAR2(20),
      last_name         VARCHAR2(25),
      email             VARCHAR2(25),
      phone_number      VARCHAR2(20),
      hire_date         DATE,
      job_id            VARCHAR2(10),
      salary            NUMBER(8,2),
      commission_pct    NUMBER(2,2),
      manager_id        NUMBER(6),
      department_id     NUMBER(4),
      address           add_list);
    / 
     
    CREATE TYPE empc_tab IS TABLE OF empc_typ;
    /
    And the following classes:

    Code:
    public EmployeeCompl (String sql_type, int employeeId, String firstName, String lastName, Address[] address)
      {
        this.sql_type = sql_type;
        this.employeeId = employeeId;
        this.firstName = firstName;
         this.lastName = lastName;
         this.l_address = address;
        
       }   
     
    ...
     
      public void writeSQL(SQLOutput stream)
        throws SQLException
      { 
    	  stream.writeInt(employeeId);
              stream.writeString(firstName);
              stream.writeString(lastName);
        for (Address addr : l_address) {
          stream.writeObject(addr);
    
    
    
    and Address:
    
    public Address (String sql_type, String street, String city, String state, String postalCode)
      {
        this.sql_type = sql_type;
        this.street = street;
        this.city = city;
        this.state = state;
        this.postalCode = postalCode;  
       
       }                                                        // line 20
     
      public void writeSQL(SQLOutput stream)
        throws SQLException
      { 
    	  stream.writeString(street);
        stream.writeString(city);
        stream.writeString(state);
        stream.writeString(postalCode);
       
      }
    when I try to execute stored procedure with the following:

    Code:
    Address add = new Address("ADD_TYP","aaaa","bbbb","cccc","dddd");
    	  Address add1 = new Address("ADD_TYP","1111","bbbb","cccc","dddd");
        Address[] address = new Address[]{add,add1};
        EmployeeCompl employee = new EmployeeCompl("EMPC_TYP",1000,"grega","sddd",address);
    	  EmployeeCompl employee1 = new EmployeeCompl("EMPC_TYP",2000,"grega2","sddd",address);
        EmployeeCompl[] employees = new EmployeeCompl[]{employee,employee1};
        dao.setEmpCompl(employees);

    Error is reported:

    May 7, 2010 10:51:48 AM org.springframework.jdbc.datasource.DriverManagerD ataSource setDriverClassName
    INFO: Loaded JDBC driver: oracle.jdbc.driver.OracleDriver
    May 7, 2010 10:51:48 AM org.springframework.jdbc.datasource.DriverManagerD ataSource setDriverClassName
    INFO: Loaded JDBC driver: oracle.jdbc.driver.OracleDriver
    May 7, 2010 10:51:49 AM org.springframework.beans.factory.xml.XmlBeanDefin itionReader loadBeanDefinitions
    INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
    May 7, 2010 10:51:49 AM org.springframework.jdbc.support.SQLErrorCodesFact ory <init>
    INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
    Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call EMPC_TAB_PROC(?)}]; SQL state [99999]; error code [17072]; Inserted value too large for column: "oracle.sql.STRUCT@1493102"; nested exception is java.sql.SQLException: Inserted value too large for column: "oracle.sql.STRUCT@1493102"
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:952)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:985)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:117)
    at dao.EmpProcComplex.executeProc(EmpProcComplex.java :38)
    at dao.EmpDao.setEmpCompl(EmpDao.java:131)
    at test.Main.main(Main.java:68)
    Caused by: java.sql.SQLException: Inserted value too large for column: "oracle.sql.STRUCT@1493102"
    at oracle.jdbc.driver.SQLStateMapping.newSQLException (SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(D atabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:199)
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:263)
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:271)
    at oracle.jdbc.oracore.OracleTypeCHAR.pickle81(Oracle TypeCHAR.java:266)
    at oracle.jdbc.oracore.OracleTypeADT.pickle81rec(Orac leTypeADT.java:1813)
    at oracle.jdbc.oracore.OracleTypeADT.pickle81(OracleT ypeADT.java:1658)
    at oracle.jdbc.oracore.OracleTypeUPT.pickle81(OracleT ypeUPT.java:230)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.pickle81( OracleTypeCOLLECTION.java:310)
    at oracle.jdbc.oracore.OracleTypeADT.pickle81(OracleT ypeADT.java:1637)
    at oracle.jdbc.oracore.OracleTypeADT.linearize(Oracle TypeADT.java:1256)
    at oracle.sql.ArrayDescriptor.toBytes(ArrayDescriptor .java:683)
    at oracle.sql.ARRAY.toBytes(ARRAY.java:632)
    at oracle.jdbc.driver.OraclePreparedStatement.setArra yCritical(OraclePreparedStatement.java:5576)
    at oracle.jdbc.driver.OraclePreparedStatement.setARRA YInternal(OraclePreparedStatement.java:5535)
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctCritical(OraclePreparedStatement.java:7959)
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(OraclePreparedStatement.java:7547)
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(OraclePreparedStatement.java:8212)
    at oracle.jdbc.driver.OracleCallableStatement.setObje ct(OracleCallableStatement.java:4123)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper. setObject(OraclePreparedStatementWrapper.java:230)
    at dao.EmpProcComplex$1.setTypeValue(EmpProcComplex.j ava:35)
    at org.springframework.jdbc.core.StatementCreatorUtil s.setValue(StatementCreatorUtils.java:262)
    at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:217)
    at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:128 )
    at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:213)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:930)
    ... 5 more
    Process exited with exit code 1.



    If I try with only one address it works.

    E.g.

    Address add = new Address("ADD_TYP","aaaa","bbbb","cccc","dddd");
    Address[] address = new Address[]{add};
    EmployeeCompl employee = new EmployeeCompl("EMPC_TYP",1000,"grega","sddd",addre ss);
    EmployeeCompl employee1 = new EmployeeCompl("EMPC_TYP",2000,"grega2","sddd",addr ess);
    EmployeeCompl[] employees = new EmployeeCompl[]{employee,employee1};
    dao.setEmpCompl(employees);



    Am i doing something wrong?
    Last edited by zgrega; May 11th, 2010, 03:28 AM.

  • #2
    Procedure is called with

    Code:
    public class EmpProcComplex extends StoredProcedure {
        private static final String PROC = "EMPC_TAB_PROC";
    
        public EmpProcComplex(DataSource dataSource) {
            super(dataSource, PROC);
            declareParameter(new SqlParameter("EMPC_ARRAY", Types.ARRAY, "EMPC_TAB"));
            compile();
        }
    
        public void executeProc(final Object[] employees) {
    
            Map inParams = new HashMap();
            inParams.put("EMPC_ARRAY", new SqlTypeValue() {
                public void setTypeValue(PreparedStatement cs, int index, int sqlType, String typeName) throws SQLException {
                    Connection con = cs.getConnection();
                    ArrayDescriptor des = ArrayDescriptor.createDescriptor("EMPC_TAB", con);
                    ARRAY a = new ARRAY(des, con, employees);
                    cs.setObject(1, (Object)a);
                }
            });
            Map out = execute(inParams);
        }
    Anyone?

    Comment


    • #3
      did you find a solution .If yes can you post one example .
      i have the same case of a collection inside a type .

      thanks .

      Comment

      Working...
      X