Announcement Announcement Module
Collapse
No announcement yet.
Submitting an array of Oracle struct values using JDBC extension Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Submitting an array of Oracle struct values using JDBC extension

    Hello.

    This may be related to this post where I'm having trouble sending Structs to Oracle from Spring.

    I would like to be able to send an array of struct values via Spring.

    I tried doing this by creating an Object[] and setting each of its elements to a new SqlStructValue.

    Any help would be greatly appreciated.

    Code:
    create or replace
    TYPE actor_type_array 
        AS VARRAY(20) OF ACTOR_TYPE;
    Code:
    create or replace
    PROCEDURE add_actors_types (in_actors IN actor_type_array)
    AS
    BEGIN
      FOR i IN 1..in_actors.count loop
        insert into actors(id, name) values(in_actors(i).id, in_actors(i).name);
      END LOOP;
    END;
    Code:
    public void insertActors(ArrayList<Actor> actors) {
    		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSource())
    								.withSchemaName(Constants.SCHEMA)
    								.withoutProcedureColumnMetaDataAccess()
    								.withProcedureName("add_actors_types")
    								.declareParameters(
    										new SqlParameter("in_actors", OracleTypes.ARRAY, Constants.SCHEMA+".ACTOR_NAME_ARRAY"));
    		
    		Object[] typeActors = new Object[actors.size()];
    		
    		for(int i=0; i < actors.size(); i++) {
    			typeActors[i] = new SqlStructValue(actors.get(i));
    		}
    		
    		Map in = Collections.singletonMap("in_actors", new SqlArrayValue(typeActors));
    		
    		simpleJdbcCall.execute(in);
    	}
    Error message:
    Code:
    org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call MY_SCHEMA_NAME.ADD_ACTORS_TYPES(?)}]; SQL state [99999]; error code [17072]; Inserted value too large for column: "org.springframework.data.jdbc.support.oracle.SqlStructValue@64dd45d3"; nested exception is java.sql.SQLException: Inserted value too large for column: "org.springframework.data.jdbc.support.oracle.SqlStructValue@64dd45d3"
    null

  • #2
    I'm also having trouble using JDBC extensions with an ARRAY of STRUCT as an input parameter, but in my case my ARRAY is a TABLE OF my STRUCT (not a VARRAY). I'm dealing with a different error message:
    Code:
    SQL state [null]; error code [17059]; Fail to convert to internal representation; nested exception is java.sql.SQLException: Fail to convert to internal representation
    Is this the correct approach? That is, using an Object[] array of SqlStructValue, and passing it to the SqlArrayValue constructor?

    Any help appreciated.

    Comment

    Working...
    X