How do I return an Oracle ref cursor to matlab

ho to retreive data from oracle ref cursor when I excute my sql statment I get:

result = fetch(exec(conn,cmd))

result =

        Attributes: []
              Data: {[1x1 oracle.jdbc.driver.OracleResultSetImpl]}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select EDB_DI_ADMIN.PKG_GPIS_READ.GetPredictionsForMatlab('avg generation','forecast','li...'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 oracle.jdbc.driver.OracleResultSetImpl]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 oracle.jdbc.driver.OracleStatementWrapper]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

I dont know how to extract data thanks for your help

Answer 1

[prabhat kumar sharma]

Hi Belgacem,

I understand you want to retrieve data from an Oracle REF CURSOR using MATLAB, follow these steps:

1. Execute the SQL Statement: Use exec to execute the SQL statement.
2. Fetch the Result Set: Use fetch to get the result set.
3. Extract Data: Access and process data from the ResultSet field.

You can check the below refrence code.

% Establish connection
conn = database('your_database_name', 'your_username', 'your_password', ...
    'Vendor', 'Oracle', 'AutoCommit', 'on', 'ReadOnly', 'off');

% Define SQL command
cmd = 'BEGIN :1 := EDB_DI_ADMIN.PKG_GPIS_READ.GetPredictionsForMatlab(''avg generation'',''forecast'',''li...); END;';

% Prepare and execute statement
stmt = conn.Handle.createStatement();
stmt.registerOutParameter(1, java.sql.Types.REF_CURSOR);
stmt.execute(cmd);

% Retrieve and process REF CURSOR
refCursor = stmt.getObject(1);
resultSet = refCursor.getResultSet();
metaData = resultSet.getMetaData();
numCols = metaData.getColumnCount();

% Fetch data
data = {};
while resultSet.next()
    row = cell(1, numCols);
    for col = 1:numCols
        row{col} = resultSet.getObject(col);
    end
    data = [data; row];
end

% Cleanup
resultSet.close();
stmt.close();
close(conn);

% Display data
disp(data);

• Ensure MATLAB is configured for Java integration.
• Add error handling as required.
• Adjust data extraction logic based on column types.

I hope it helps!