Re: Database Link / ORA-02018

Kai Sodemann wrote:
>
> Hi,
>
> I have created a database package, in which I
> 1. Create a database link (DBMS_SQL)
> 2. select data from the remote database.
> 3. Drop the database link (DBMS_SQL)
>
> The problem is, that if I select data from the remote system,
> I can not drop the database link anymore. I get the following error:
>
> ORA-02018: database link of same name has an open connection
> ....

Hello Kai,

If you use things like

  FOR c IN (SELECT...) LOOP
  ...
  END LOOP; there is an implicit cursor opened.

I'm not sure if following works but it may work. Use instead of FOR-LOOP:

CURSOR <cursor> IS SELECT ...;
BEGIN
  OPEN <cursor>;
  LOOP
    FETCH <cursor> INTO ...;
    EXIT WHEN <cursor>%NOTFOUND;
    ...
  END LOOP
  CLOSE <cursor>;

and make an COMMIT; before dropping the link.

If this does'nt work insert into the init-ORA the parameter

        close_open_cursor_on_commit=true
and restart the database (the name of the parameter may slightly be different).   

Good luck
  Marko Received on Fri Oct 01 1999 - 14:47:59 CDT