How do I call a stored procedure with an OUT parameter from MATLAB using the Database Toolbox?

I am trying to access stored procedures containing OUT parameters from an Oracle database using functions from the Database Toolbox. Is is possible to do this with the Database Toolbox?

Answer 1

[MathWorks Support Team]

This feature has been added in the Database Toolbox 3.2 (R2006b). You can use the RUNSTOREDPROCEDURE function. If you are using a previous version, read below for any workarounds.

You can retrieve the output value(s) as follows:

% Get one output.

x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC})

% Get two outputs.

x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC java.sql.Types.INTEGER})

% One integer output value.

%returnValue = runstoredprocedure(conn, 'test', {0}, {java.sql.Types.INTEGER})

% Two integer output values.

returnValues = runstoredprocedure(conn, 'test2', {0}, {java.sql.Types.INTEGER java.sql.Types.INTEGER})

To return data from a stored procedure, you will need to issue some commands from the MATLAB other than EXEC and FETCH commands.

The syntax for a stored procedure is:

 {?= call <procedure-name>[<arg1>,<arg2>, ...]}

or

 {call <procedure-name>[<arg1>,<arg2>, ...]}

The first case deals with a procedure that returns an OUT parameter and the second case shows no OUT parameter.

MATLAB (ODBC) example:

conn = database('source','username','password');

cs = conn.Handle.prepareCall('{?= call <procedure-name>[<arg1>,<arg2>, ...]}');

Assume this stored procedure returns a double

cs.registerOutParameter(2, java.sql.Types.DOUBLE, 3);

cs.executeQuery;

x = cs.getDouble(1);

The above code then returns a double value stored at index value 1 of the output parameters.

For more information about the above code, refer to the JDBC API documentation.