MATLAB: How to call a stored procedure with an OUT parameter from MATLAB using the Database Toolbox

databaseDatabase Toolboxoutoutputparameterprocedurestoredtoolbox

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?

Best Answer

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.
Related Question