Getting pl/sql array of struct from stored procedure using JdbcSimpleCall

Question

im trying to execute oracle stored procedure using SimpleJDBCCall, all tables and stored procedures are in restaurant schema, table looks like:

CREATE TABLE STAFF
(
    STAFF_ID             NUMBER(5),
    STAFF_FIRST_NAME     VARCHAR2(10 BYTE)        NOT NULL,
    STAFF_LAST_NAME      VARCHAR2(20 BYTE)        NOT NULL,
    STAFF_ROLE           VARCHAR2(20 BYTE)        NOT NULL,
    STAFF_OTHER_DETAILS  VARCHAR2(50 BYTE)
);

my type package:

CREATE OR REPLACE PACKAGE Staff_Types 
AS
    TYPE Staff_Collection IS TABLE OF Staff%ROWTYPE;  
END Staff_Types;

my access package:

CREATE OR REPLACE PACKAGE Staff_TAPI 
AS
    FUNCTION getAllStaff RETURN Staff_Types.Staff_Collection;
END Staff_TAPI;

CREATE OR REPLACE PACKAGE BODY Staff_Tapi
AS
    FUNCTION getAllStaff 
        RETURN Staff_Types.Staff_Collection
    IS
        all_staff Staff_Types.Staff_Collection;
    BEGIN
        SELECT * 
        BULK COLLECT INTO all_staff
        FROM Staff;

        RETURN all_staff;
    END;

END Staff_Tapi;

Java Access:

@Component
@Qualifier("staffJdbcDAO")
public class StaffJDBCDAO implements StaffDAO {
    JdbcTemplate jdbcTemplate;
    SimpleJdbcCall getAllMembersSP;

    @Autowired
    @Qualifier("dataSource")
    DataSource dataSource;

    @Autowired
    @Qualifier("jdbcTemplate")
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        initializeStoredProceduresCalls();
    }

    private void initializeStoredProceduresCalls() {
        getAllMembersSP = new SimpleJdbcCall(jdbcTemplate);
        getAllMembersSP.withCatalogName("Staff_Tapi");
        getAllMembersSP.withFunctionName("getAllStaff");
        getAllMembersSP.declareParameters(
            new SqlOutParameter("return",
                    Types.OTHER,
                    "Staff_Types.Staff_Collection",
                    new SqlReturnStructArray<>( new StaffMapper() )
            )
        );
        getAllMembersSP.compile();

    }

    @Override
    public List<Staff> getAllMembers() {
        Staff[] staff = getAllMembersSP.executeFunction(Staff[].class,new   HashMap<String,Object>() );
        return Arrays.asList(staff);
    }
}

mapping class:

public class StaffMapper implements StructMapper<Staff> {

    @Override
    public STRUCT toStruct(Staff staff, Connection connection, String typeName) throws SQLException {
        StructDescriptor descriptor = StructDescriptor.createDescriptor(typeName, connection);

        Object[] attributes = new Object[5];
        attributes[0] = new Integer( staff.getId() );
        attributes[1] = new String("STAFF_FIRST_NAME");
        attributes[2] = new String("STAFF_LAST_NAME");
        attributes[3] = new String("STAFF_ROLE");
        attributes[4] = new String("STAFF_OTHER_DETAILS");
        Struct staffStruct = connection.createStruct(typeName,attributes);
        return new STRUCT(descriptor,connection,attributes);
    }

    @Override
    public Staff fromStruct(STRUCT struct) throws SQLException {
        StructDescriptor descriptor = struct.getDescriptor();
        ResultSetMetaData metaData = descriptor.getMetaData();
        Object[] attributes = struct.getAttributes();
        Map<String,Object> attributeMap = new HashMap<>();
        int idx = 1;
        for ( Object attribute : attributes )
            attributeMap.put( metaData.getColumnName(idx++),attribute );


        int id = ((Integer)attributeMap.get("STAFF_ID")).intValue();
        String firstName = (String) attributeMap.get("STAFF_FIRST_NAME");
        String lastName = (String) attributeMap.get("STAFF_LAST_NAME");
        String staffRole = (String) attributeMap.get("STAFF_ROLE");
        String otherDetails = (String) attributeMap.get("STAFF_OTHER_DETAILS");
        return new Staff(id,firstName,lastName,staffRole,otherDetails);
    }
}

and staff:

public class Staff {
    private int id;
    private String firstName;
    private String lastName;
    private String profession;
    private String otherDetails;


    public Staff(int id, String firstName, String lastName, String profession, String otherDetails) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.profession = profession;
        this.otherDetails = otherDetails;
    }

    public int getId() {
        return id;
    }

    public int setId(int id) {
        this.id = id;
    }

    // and others getters and setters
}

when i execute getAllMembers from StaffDAO im constatly getting : CallableStatementCallback; uncategorized SQLException for SQL [{? = call STAFF_TAPI.GETALLSTAFF()}]; SQL state [99999]; error code [17004]; Invalid column type: 1111;

when i change return type parameter to Types.Array i get: CallableStatementCallback; uncategorized SQLException for SQL [{? = call STAFF_TAPI.GETALLSTAFF()}]; SQL state [99999]; error code [17074];
invalid name pattern: restaurant.Staff_Types.Staff_Collection;

i tried in both ways with pattern "Staff_Types.Staf_collection" getting same results, im trying to do this for nearly 2 days without any idea what should i do, if anyone has any suggestions i will be greateful.

Answer 1

In order to easily integrate your PL/SQL call, and since it is already built as a function: have you thought about something like this?

select * from TABLE(CAST(Staff_Tapi.getAllStaff() as Staff_Types.Staff_Collection))

This way, you can execute it easily as a regular JDBC query. Once done, just process the ResultSet it returns with some minor variant of your fromStruct method in order to return a List<Staff> list to whatever business logic you have on top of it. Hope you find this useful!

Answer 2

You cannot load a PL/SQL record from a stored procedure through JDBC. In fact, you cannot even load such a type from Oracle SQL. See also this question for details:

• Fetch Oracle table type from stored procedure using JDBC

You can only load SQL types through JDBC (as opposed to PL/SQL types). Given your example, you'll need to write:

-- You cannot really avoid this redundancy
CREATE TYPE STAFF AS OBJECT
(
    STAFF_ID             NUMBER(5),
    STAFF_FIRST_NAME     VARCHAR2(10 BYTE)        NOT NULL,
    STAFF_LAST_NAME      VARCHAR2(20 BYTE)        NOT NULL,
    STAFF_ROLE           VARCHAR2(20 BYTE)        NOT NULL,
    STAFF_OTHER_DETAILS  VARCHAR2(50 BYTE)
);
CREATE TYPE STAFF_TABLE AS TABLE OF STAFF;

And then:

CREATE OR REPLACE PACKAGE Staff_TAPI 
AS
    FUNCTION getAllStaff RETURN STAFF_TABLE;
END Staff_TAPI;

Answer 3

You may want to capitalize your custom type in java code like

    getAllMembersSP.declareParameters(
        new SqlOutParameter("return",
                Types.OTHER,
                "STAFF_TYPES.STAFF_COLLECTION",
                new SqlReturnStructArray<>( new StaffMapper() )
        )
    );