Using Oracle user-defined object types in Apache Camel SQL-STORED Component

Question

Does anybody have an example on how to use Oracle user-defined object types when calling a stored procedure via the Camel sql-stored component? The Camel documentation (http://camel.apache.org/sql-stored-procedure.html) provides only an example using built-in data types.

Here are my details:

I am trying to call the following procedure:

PROCEDURE get_eco_record(
      p_instance_id IN NUMBER,
      p_inparm IN InParm_rec,
      p_error_cd OUT NUMBER,
      p_error_message OUT VARCHAR2 )

This is the definition of the InParm_rec object type:

CREATE OR REPLACE TYPE InParm_rec
AS
  object
  (    
    part_no   VARCHAR2(75 BYTE) ,
    part_type VARCHAR2(100 BYTE) ,
    eff_date DATE,
    sub_name VARCHAR2(100),
    rec_type VARCHAR2(1)
    );

Answer 1

I opened a pull request https://github.com/apache/camel/pull/1749 based on your requirements.

Answer 2

After some digging the answer is "No". However, the extension should be fairly straightforward. When using custom data types, declaration of each SqlParameter needs to have (at least) three parameters - parameter_name, Oracle_type and custom_data_type_name. In my example, the declaration of the attributes should be as follows:

    declareParameter(new SqlParameter("p_instance_id", OracleTypes.INTEGER));
        declareParameter(new SqlParameter("p_inparm",OracleTypes.ARRAY, "InParm_rec"));
        declareParameter(new SqlOutParameter("p_error_cd", OracleTypes.INTEGER));
        declareParameter(new SqlOutParameter("p_error_message", OracleTypes.VARCHAR));

Going back to Camel SQL Stored Procedure Component - the component is declaring the procedure parameters in Class

org.apache.camel.component.sql.stored.TemplateStoredProcedure

Here is the code snippet that handles declaration of procedure parameters:

for (Object parameter : template.getParameterList()) {
    if (parameter instanceof InputParameter) {
        InputParameter inputParameter = (InputParameter) parameter;
        declareParameter(new SqlParameter(inputParameter.getName(), inputParameter.getSqlType()));
        inputParameterList.add(inputParameter);

    } else if (parameter instanceof OutParameter) {
        OutParameter outParameter = (OutParameter) parameter;
        declareParameter(new SqlOutParameter(outParameter.getOutValueMapKey(), outParameter.getSqlType()));
        setFunction(false);
    }
}

The code currently supports only two parameters (parameter_name, Oracle_type). The code would have to be extended to add the third parameter (custom_data_type_name). The additional parameter would have to be applied to user-named types like: STRUCT, DISTINCT, JAVA_OBJECT, named array types.

Another parameter that might be needed is "scale" which is applicable to NUMERIC and DECIMAL data types.

The best approach would be to inspect the org.springframework.jdbc.core.SqlParameter and org.springframework.jdbc.core.SqlOutParameter classes and extend Camel Sql-Stored to support all their options.