ORA - 02018: Database link of same name has an open connection
Hi,
I have stored procedure as shown below. When I run this stored procedure most of the times it is getting successful and few times it throws an exception called ORA - 02018: Database link of same name has an open connection while dropping the database link. Could anyone give the explanation why is it failing sometimes and what would be the solution. Thanks.
procedure change_password(p_password in varchar2) is
num_rows INTEGER;
v_username varchar2(30);
stage varchar2(70) := 'Beginning';
passthroughstr varchar2(300);
inputstr varchar2(100);
BEGIN
commit;
v_username := get_system_parameter('UID');
stage := 'changing password on Teradata side ';
passthroughstr := 'begin :1 := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@AC_GDW(:2); end;';
inputstr := 'MODIFY USER ' || v_username || ' AS PASSWORD = ' ||
p_password;
execute immediate passthroughstr
using out num_rows, in inputstr;
commit;
--update_seed_data(p_password);
stage := 'dropping database link';
execute immediate 'drop database link AC_GDW';
stage := 'creating database link ';
execute immediate 'create database link AC_GDW connect to ' ||
v_username || ' identified by ' || p_password ||
' using ' || '''' || 'AC_GDW' || '''';
EXCEPTION
when others then
raise_application_error(-20555,
'Error occurred when ' || stage || sqlerrm);
END change_password;