Announcement Announcement Module
Collapse
No announcement yet.
Trouble getting Oracle Structs to work with JDBC Extensions Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Trouble getting Oracle Structs to work with JDBC Extensions

    Hello all.

    I have tried using the JDBC extensions with Oracle support and was able to pass arrays to Oracle but am having trouble getting the STRUCTS to work.

    I am following the code examples at the following link:
    http://static.springsource.org/sprin...tatypes.struct

    I am using RC3 downloaded from here:
    http://www.springsource.org/download/community

    I've tried using both techniques (by implementing the SQLData interface and SqlStructValue) and am getting the following errors

    When using the SQLData technique:
    Code:
    org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call MY_SCHEMA_NAME.ADD_ACTOR(?)}]; SQL state [99999]; error code [17001]; Internal Error: Inconsistent catalog view; nested exception is java.sql.SQLException: Internal Error: Inconsistent catalog view
    null
    When using the SqlStructValue technique:
    Code:
    org.springframework.dao.DataIntegrityViolationException : CallableStatementCallback; SQL [{call MY_SCHEMA_NAME.ADD_ACTOR(?)}]; ORA-01403: no data found
    ORA-06512: at line 1
    ; nested exception is java.sql.SQLException: ORA-01403: no data found
    ORA-06512: at line 1
    
    null
    Below is my Java and SQL code.

    Any help would be greatly appreciated.

    Code:
    create or replace
    TYPE actor_type
        AS OBJECT (id NUMBER, name VARCHAR2(50));
    Code:
    create or replace
    PROCEDURE add_actor (in_actor IN actor_type)
    AS
    BEGIN
      INSERT into actors (id, name) VALUES(in_actor.id, in_actor.name);
    END;
    SQLData method:
    Code:
    public void insertActor(SqlActor sqlActor) {
    		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSource())
    								.withSchemaName(Constants.SCHEMA_NAME)
    								.withProcedureName("add_actor")
    								.declareParameters(
    										new SqlParameter("in_actor", OracleTypes.STRUCT, Constants.SCHEMA_NAME+".ACTOR_TYPE"));
    		
    		Map in = Collections.singletonMap("in_actor", sqlActor);
    		
    		simpleJdbcCall.execute(in);
    	}
    SqlStructValue method:
    Code:
    public void insertActors(ArrayList<Actor> actors) {
    		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSource())
    								.withSchemaName(InfoTableCongruencyConstants.INFOTABLECONGRUENCY_SCHEMA)
    								.withProcedureName("add_actor")
    								.declareParameters(
    										new SqlParameter("in_actor", OracleTypes.STRUCT, InfoTableCongruencyConstants.INFOTABLECONGRUENCY_SCHEMA+".ACTOR_TYPE"));
    
    		Map in = Collections.singletonMap("in_actor", new SqlStructValue(actors.get(0)));
    		
    		simpleJdbcCall.execute(in);
    	}

  • #2
    I was able to get it to work with the following:

    Code:
    	public List<TypeOut> getListTypeOut(final TypeIn typeIn) {
    		
    		SimpleJdbcCall call =
    			new SimpleJdbcCall(dataSource)
    				.withFunctionName("MY_SCHEMA.MY_FUNCTION")
    				.withoutProcedureColumnMetaDataAccess()
    				.declareParameters(
    					new SqlOutParameter("RETURNED_CURSOR", OracleTypes.CURSOR, ParameterizedBeanPropertyRowMapper.newInstance(TypeOut.class)),
    					new SqlParameter("IN_PARAM1", OracleTypes.STRUCT, "MY_SCHEMA.TYPE_IN"));
    		
    		MapSqlParameterSource input = new MapSqlParameterSource()
    			.addValue("IN_PARAM1", new SqlStructValue(typeIn));
    		
        		call.setFunction(true);
        		return call.executeFunction(List.class, input);
    	}
    It's slightly different than the JDBC Extensions example: in my case it's a function that takes a STRUCT (Oracle type), and returns a SYS_REFCURSOR. Maybe someone can adapt it to suit their needs

    Comment

    Working...
    X