ORA-02080: database link is in use

Question

I have 10 functions and each function returns a table from using dblinks.

The structure is like

    function declare
.
.
.
begin
.
if
for i in ( select * from table@dblinks)

loop

i.
i.
i.

--return tble
pipe row(obj);

end loop;
end if;
--close dblink statement..
execute immediate 'ALTER SESSION CLOSE DATABASE LINK TEST_LINK';

end;

All functions are like this only .

I tried rollback and commit inside before closing db link but it returns an error as:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

I want to close the dblink.

I think the problem is related to cursors, which were not properly closed. How to close these types of anonymous explicit cursors?

Welcome to Stack Overflow. Please read stackoverflow.com/help/how-to-ask for guidelines on how to write questions. For starters: 1. Please format your code. 2. Add more code to your question to help us understand it better and for future readers to benefit from this question. 3. Look for other questions which might help you out already. 4. Format your text, and give us a minimum working example — abarisone
I reworded a bit your question fixing some typos but I reccommend you to improve the code section — abarisone

Gary Myers Gary Myers · Accepted Answer · 2016-07-12T01:13:20

PL/SQL tries to cache cursors, which means it doesn't really close them. You should be able to use alter session to tell it not to cache, then close the link.

execute immediate 'ALTER SESSION SET SESSION_CACHED_CURSORS = 0';
dbms_session.CLOSE_DATABASE_LINK('####');
execute immediate 'ALTER SESSION SET SESSION_CACHED_CURSORS = 50';

ORA-02080: database link is in use

1 Answers