MATLAB: How to call a stored procedure from the database using the Database Toolbox

Database Toolboxdbfunctionsoraclepackagespl/sqlquery

I am working with a database that has a stored procedure or function. I would like to call my database's stored procedure using the Database Toolbox.

Best Answer

The documentation for MATLAB 7.6 (R2008a) has been updated to incorporate the relevant information. For previous product releases, read below for any possible workarounds:
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 execute a database's stored procedure using the valid SQL command to do so.
The following is an example showing a call to a database stored procedure that returns a cursor:
setdbprefs('DataReturnFormat','structure')
ssql_cmd1 = '{?= call get_int_by_id(1,1,to_date('07/02/05','MM/DD/YY'),to_date('07/07/05','MM/DD/YY'))}';
curs = exec(conn, ssql_cmd1)
curs =
         Attributes: []
               Data: 0
     DatabaseObject: [1x1 database]
           RowLimit: 0
           SQLQuery: [1x97 char]
            Message: []
               Type: 'Database Cursor Object'
          ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
             Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
          Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
              Fetch: 0
a = fetch(curs)
a =
         Attributes: []
               Data: [1x1 struct]
     DatabaseObject: [1x1 database]
           RowLimit: 0
           SQLQuery: [1x97 char]
            Message: []
               Type: 'Database Cursor Object'
          ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
             Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
          Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
              Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]
a.Data
ans = 
         TS_DT: {'2005-07-02 00:00:00.0'}
     INT_VALUE: 1
sql_cmd2='{?= call nrg.ts_get_int_by_id(1,1,to_date(''07/02/05'',''MM/DD/YY''),to_date(''07/20/05'',''MM/DD/YY''))}';
curs = exec(conn, ssql_cmd2)
curs =
         Attributes: []
               Data: 0
     DatabaseObject: [1x1 database]
           RowLimit: 0
           SQLQuery: [1x97 char]
            Message: []
               Type: 'Database Cursor Object'
          ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
             Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
          Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
              Fetch: 0
a = fetch(curs)
a =
         Attributes: []
               Data: [1x1 struct]
     DatabaseObject: [1x1 database]
           RowLimit: 0
           SQLQuery: [1x97 char]
            Message: []
               Type: 'Database Cursor Object'
          ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
             Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
          Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
              Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]
a.Data
ans = 
         TS_DT: {2x1 cell}
     INT_VALUE: [2x1 double]
a.Data.TS_DT
ans = 
     '2005-07-02 00:00:00.0'
     '2005-07-10 00:00:00.0'
a.Data.INT_VALUE
ans =
      1
      6
For more information on calling stored procedures, consult the Database Toolbox documentation by issuing the following command at the MATLAB command prompt:
doc procedures
Related Question