Different behavior creating private temporary tables with MATLAB execute vs Oracle SQL Developer

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.

Answer 1

[Jacob Lynch August]

I have not determined the exact difference MATLAB's database connection's execute function and Oracle SQL Developer, but I have resolved the issue with the help of MATLAB support. My creation statement lacked the keywords "ON COMMIT PRESERVE DEFINITION". Early tests used "preserve rows" which was not appropriate in these circumstances.

% -- EXAMPLE CREATE STATEMENT IN MATLAB M-FILE
% CREATE PRIVATE TEMPORARY TABLE 
%         ORA$PTT_MY_RESULTS 
% ON COMMIT PRESERVE DEFINITION AS (
% SELECT  [COLUMNS]
% FROM    SOME_TABLE
% WHERE   [FILTERING CLAUSES]
% );

I cannot create multiple tables in a single creation statement if they reference one another (in order). Executing the creation statements sequentially requires pausing for at least 1 second, otherwise Oracle issues an error "ORA-01466: unable to read data - table definition has changed".

Answer 2

[Piyush Kumar]

Hi @Jacob Lynch August,

As mentioned here, for private temporary tables, both table definition and data are temporary and are dropped at the end of a transaction or session. On top of that, Oracle stores private temporary tables in memory and each temporary table is only visible to the session that created it.

This is why you could create the same temporary table in the MATLAB session, which you had already created in the past.

I tried creating and accessing temporary tables with "MySQL Workbench" and it worked fine as soon as I was using the same session.

In MATLAB, Can you please try to find at which step it is failing - in creating the temporary table or in fetching the data?