QuerySQL strange behaviour with a begin + loop + create or replace @inmemorystorage

Good morning

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 above 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, nos 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 ?)

thanks for your help

1 Like

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.

To our excuses, Oracle never supports this construct :slight_smile:

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.

To be noted I did a create table t1@inmemorystorage (field1 data-type, field2 data-type….)

Because doing a lazy
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 stmt but then data types errors are appearing (1 data-type is numeric, but if my first real stuff row is datetime and query is not happy with that)