Different behavior creating private temporary tables with MATLAB execute vs Oracle SQL Developer
25 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Jacob Lynch August
el 19 de Jul. de 2024 a las 16:25
Editada: Jacob Lynch August
hace alrededor de 8 horas
I am a mechanical engineer working with manufacturing factory data from an Oracle database (via ODBC). My predecessors used a large quantity of specialty SQL scripts and query statements written by the database team, but I have replaced many of them with far fewer MATLAB functions.
The final (and largest) piece of the legacy scripts involve creating temporary tables. (Please do not suggest converting into with statements; the database is much more complicated than I'm showing.) My credentials to this database permit me to create private temporary tables. I can successfully create and fetch from these tables with Oracle SQL Developer using the patterns below.
Create
% -- EXAMPLE CREATE STATEMENT IN MATLAB M-FILE
% CREATE PRIVATE TEMPORARY TABLE ORA$PTT_MY_RESULTS AS (
% SELECT [COLUMNS]
% FROM SOME_TABLE
% WHERE [FILTERING CLAUSES]
% );
Fetch
% -- EXAMPLE SELECT STATEMENT IN MATLAB M-FILE
% SELECT * FROM ORA$PTT_MY_RESULTS
When I execute the same create statements in MATLAB, nothing seems to happen. Fetch fails indicating the table or view does not exist.
oracle_db = database( ...
name_oracle_db, ...
my_username, ...
my_password ...
,'AutoCommit','on' ...
,'ReadOnly','off');
oracle_db.execute(my_create_statement);
%{
oracle_db.execute(my_create_statement);
% ^ should've caused an error that the table exists.
%}
%{
oracle_db.commit();
% ^ no effect.
%}
% my_results = oracle_db.sqlread( "ORA$PTT_MY_RESULTS");
% my_results = oracle_db.select("SELECT * FROM ORA$PTT_MY_RESULTS");
% my_results = oracle_db.fetch( "SELECT * FROM ORA$PTT_MY_RESULTS");
% % ^ table or view does not exist
What are the differences between MATLAB and Oracle SQL Developer when executing the creation of a private temporary table? Standard select statements work exactly the same between MATLAB's fetch/select methods and Oracle SQL Developer. MATLAB's execute method does not seem to create the tables.
NOTE: This difference was detected early earlier in the week, before CrowdStrike took down everything.
0 comentarios
Respuestas (0)
Ver también
Categorías
Más información sobre Database Toolbox en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!