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
>Solution :
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>