I have some strange behaviour of QueryTool Beta 20.1.435 when having a combined create or replace table + loop with a for r in a @InMemory created table
See and test the statement:
begin
create or replace table t1@InMemoryStorage as select 1;
begin
for r in (select * from t1@InMemoryStorage)
loop
begin
dbms_output.put_line('hello');
end;
end loop;
end;
drop table t1@InMemoryStorage;
end
to make this working, the t1 table needs to exists before executing the statement:
create or replace table t1@InMemoryStorage as select 1;
begin
begin
for r in (select * from t1@InMemoryStorage)
loop
begin
dbms_output.put_line('do something');
end;
end loop;
end;
drop table t1@InMemoryStorage;
end
with this statement, no errors:
begin
create or replace table t1@InMemoryStorage as select 1;
drop table t1@InMemoryStorage;
end;
It seems that Query Tool tries to evaluate for r in (select * from t1@InMemoryStorage) before having been able to create or replace table t1@InMemoryStorage as select 1 or am I doing something wrong, like need to declare t1@InMemoryStorage ?)
I will add an explanation later but the basics on this version of Invantive SQL are that doing DDL within a PSQL block has interesting perks. This is one of them.
We are moving to a new version with a stronger capability to handle such scenario by delaying parts of the evaluation.
As a workaround, create the table in a SQL or PSQL statement before and just use DML to fill it correctly within the block.
All excuses are granted to Invantive , QueryTool does so much things that doing a small table creation before the loop statement is affordable.
It works now as expected.
create or replace table t1@inmemorystorage as select 1
With having again a create or replace table t1@inmemorystorage as select [real stuff here]
in the block statement will solve the evaluation error on the loop statement but then data types errors are appearing (1onedata-type is numeric, but if my first real stuff row is datetime and query is not happy with that).
I have the same problem on a stored procedure, where I’m creating a table in memory storage and later try to loop over it. Could you please explain a little further how to work around this issue?
The workaround is to create the table@inmemorystoragebefore the stored procedure statement executes. Then you can re-create it in the stored procedure and loop on it.
The issue comes from the loop that tries to parse and recognize the table@inmemorystorage whereas it is not yet created when executing the stored procedure.
Like:
create or replace table shifts@inmemorystorage
( opening_date datetime
, closing_date datetime
, shift_id varchar2
);
-- PSQL procedure starts here
declare
-- my declarations
begin
create or replace table shifts@inmemorystorage
as
select opening_date
, closing_date
, shift_id
from mytable@mycontainer
;
--here you can also loop on your shifts@inmemorystorage;
end;
As promised some more background. It took longer to come back here due to capacity being needed to handle an out-of-control situation with one of the connected platforms.
The Invantive SQL-statements (including the PSQL-blocks started as a SQL-statement by surrounding them with begin and end) assume that all database objects exist format prior to execution and do not change definition during execution.
This is essentially the border between DML (Data Manipulation Language) and DDL (Data Definition Language).
However, during compilation of DML-statements (including PSQL), the Invantive SQL-engine does not always check that the database objects and code meet these requirements. Often code can create or replace database objects during compilation and execution without any impact given the nature of the compilation process of Invantive SQL.
Despite providing advantages, this current behavior is considered a bug and not a feature.
The next version of the SQL-engine will split the compilation phase into more sub-phases. Existing code dynamically creating database objects such as tables may fail or continue working.
We will try for the next version to keep the advantages, but removing the fuzzy nature of changing database objects during execution of DML.
To write maintainable code, it is recommended to create the database objects in a separate SQL statement-batch(es) using either PSQL, SQL DDL-statements or dynamic SQL. The original DML remains as the last SQL-batch executed.
A SQL-batch is a range of SQL-statements executed in one go (see Invantive SQL Grammar v22.1). In case there are questions, please address them in a separate topic and refer to this topic.
