I encountered inexplicable freezing of pl/sql procedure while trying to fetch and insert data into table, however same operation runs successfully when called with sql query in any IDE.
I am trying to realise ETL process in data warehouse database.
I need to load data from a remote database's table into a local one, i have low privileges on the remote database meaning i cant tune network or create materialised view logs there.
Target table is 'long' and daily data that must be transferred is about one million rows 7 columns wide with mostly numeric data and no large objects.
I created db link and a simple view that represents remote table in local database and granted select privilege to

'DATAW'

schema designed for data storage.

In the

'DATAW'

i created a procedure for merging data into identical local table. Something like this:

create procedure merge_t_data 
(p_days_offset in number)
as 
begin 
merge /*+ APPEND */ into t_data_local tgt
using (
select col_id, col2, ..., col7 
from pdbadmin.v_data_remote
where updated_at >= trunc(sysdate) - interval '1' day * p_days _offset ) src on (tgt.col_id = src.col_id) 
when matched then update 
set ...
when not matched then
insert 
...;
end;

When i run the procedure the session acquires wait event 'Sql*net message from dblink' or 'Sql*net more data from dblink' which stays the same forever.
When i check incoming traffic on the server while the procedure is running i see that it is not used at all.
HOWEVER
When i run the same merge operatiion using query like:

merge /*+ APPEND */ into t_data_local tgt
using (
select col_id, col2, ..., col7 
from pdbadmin.v_data_remote
where updated_at >= trunc(sysdate) - interval '1' day * 3 ) src on (tgt.col_id = src.col_id) 
when matched then update 
set ...
when not matched then
insert 
...;

it runs successfully: i see incoming traffic up to 2Mb, and query finishes after +-10 minutes.
I am the only user of the database, no other people works with it for now.
I have also tried inserting the data into temporary table; using fetch cursor bulk collect; running execute immediate in the procedure, result was the same - execution freezes.
Also worth mentioning that i also successfully realised ETL process for second table: it is much wider: daily data needed for transferring is about 50k rows and the number of collumns is more than 20. I did it with similar merge procedure that runs successfully unlike the previously discussed one.


I want to know if it is possible to achieve success in running my merge procedure for the 'long' table or what might be other solutions to this problem