Get values in cursor from remote database using database link

Question

How to fetch column values in a variable in a cursor .I am trying to copy data from a database where i have created a dblink. I am using the below query to get the data

DECLARE
    v_remark_column mytablename.remark_column%TYPE;
    CURSOR c_cursor1 IS
    SELECT
        remark_column
    FROM
        mytablename@dblinkname ;

BEGIN
    OPEN c_cursor1;
    LOOP
        FETCH c_cursor1 INTO v_remark_column;
        EXIT WHEN c_cursor1%notfound;
        dbms_output.put_line('v_remark_column: ' || v_remark_column);
    END LOOP;

    CLOSE c_cursor1;
END;

This gives me error and not to fetch the column value. Can i get some guidance on dblink

Answer 1

Hm, only if you said which error you got ...

---

I guess it is about wrongly declared local variable (as it has to inherit datatype from a table over that database link). See the following demo:

Database link:

SQL> create database link dbl_mike
  2    connect to mike
  3    identified by lion
  4    using 'orcl';

Database link created.

SQL> select * from dual@dbl_mike;

D
-
X

PL/SQL procedure:

SQL> set serveroutput on
SQL> declare
  2    v_dummy dual.dummy@dbl_mike%type;         --> see this
  3    cursor c_cursor1 is
  4      select dummy from dual@dbl_mike;
  5  begin
  6    open c_cursor1;
  7    loop
  8      fetch c_cursor1 into v_dummy;
  9      exit when c_cursor1%notfound;
 10      dbms_output.put_line('dummy = ' || v_dummy);
 11    end loop;
 12    close c_cursor1;
 13  end;
 14  /
dummy = X

PL/SQL procedure successfully completed.

SQL>

---

Therefore, you should declare it as

v_remark_column mytablename.remark_column@dblinkname%type;

---

Besides, consider switching to a cursor FOR loop, it is way simpler:

SQL> begin
  2    for c_cursor1 in (select dummy from dual@dbl_mike) loop
  3      dbms_output.put_line('dummy = ' || c_cursor1.dummy);
  4    end loop;
  5  end;
  6  /
dummy = X

PL/SQL procedure successfully completed.

SQL>