Declare and pass variables between non-nested blocks

Questions
,
1

I would like to know if there is a possibility to use variables across non-nested begin...end; blocks.

Because the way Invantive parses scripts that includes create or replace table and subsequent access to theses created table, I cannot use a unique begin...end; nor nested ones as discussed here: SQL strange behaviour with a begin + loop + create or replace @inmemorystorage - 7 van forums

For exemple, creating a log, that will be afterwards saved thanks to write_file@Os

-- block1
declare 
  l_log_txt varchar2;
begin
    l_log_txt := l_log_txt + (chr(10) || 'script started at : ' || sysdate);
    -- do things here like create or replace a table
end;
--
-- block2
--declare l_log_txt varchar2;
begin
  l_log_txt := l_log_txt + (chr(10) || 'script finished at : ' || sysdate);
  -- do things here like read, insert or else on the above table
end;
-- block3
--declare l_log_txt varchar2;
begin
  insert into write_file@Os
  ( file_path
  , file_contents
  ) 
  values 
  ( 'c:\jobs\log\log.txt'
  , base64_encode(ascii_to_blob(l_file_content))
  );
end;

Of course a workaround could be creating a inmemory table

create or replace table variables@inmemorystorage
( var_name varchar2
, var_value varchar2
);

to store variables. But it is not very easy to manipulate … when inserting, reading and updating values.

2

There is currently no way to store state easily in the session across PSQL-blocks other than through storing it in an in-memory or other table.

A good example is shown in Easily determine changed values in custom data-integration triggers with synchronize.

In the future, the package concept might be extended to allow defining your own packages. In that case, there might be variables available to store data in from outside the package or through a function in the package, just like with the dbms_output package.

3

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.