How do I return an Oracle ref cursor to matlab

Question

Belgacem 2014-9-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/156201-how-do-i-return-an-oracle-ref-cursor-to-matlab

回答： prabhat kumar sharma 2024-7-24

在 MATLAB Online 中打开

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

prabhat kumar sharma 2024-7-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/156201-how-do-i-return-an-oracle-ref-cursor-to-matlab#answer_1489836

在 MATLAB Online 中打开

Hi Belgacem,

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

Execute the SQL Statement: Use exec to execute the SQL statement.
Fetch the Result Set: Use fetch to get the result set.
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);