Retreiving data from external DB using cursors (Native SQL)

Hi experts,

Iu2019m trying to use some functionality based on the Native SQL on an Oracle Server. Iu2019m facing some difficulties using cursors and fetching data from the external database.

Basically the synonym its zfisicc_c_dblink, and Iu2019m running the for the following code:

TRY.
      OPEN CURSOR WITH HOLD c1 FOR SELECT *
                            FROM  zfisicc_c_dblink
                            WHERE estado_sif = 'I'.
 
    CATCH cx_sql_exception INTO sqlerr_ref.
      PERFORM handle_sql_exception USING sqlerr_ref.
  ENDTRY.
 
DO.
*  "Move the data from the Cursor into the target area.
    FETCH NEXT CURSOR c1 APPENDING TABLE tab.
    IF sy-subrc  0.
      EXIT.
    ENDIF.
 
  ENDDO.
  CLOSE CURSOR c1.

The cursor C1 it's cursor type and the table tab it's ZFISICC_C_DBLINK type, but every time i run it it's giving me the ORA-932 error.

Database error text........: "ORA-00932: inconsistent datatypes: expected %s got %s"

Can anyone help me on this error? What Iu2019m doing wrong when fetching the data ?

Another doubt that Iu2019ve it's when using pl/Sql procedure. Can anyone tell how I can retrieve data from the external database with the following code?

**Select Directo com Cursor
 
  EXEC SQL.
    CONNECT TO :gv_db_name AS 'dblink_con'
  ENDEXEC.
 
  IF sy-subrc NE 0.
    WRITE: 'Não foi possível fazer a ligação à DBCON: ', gv_db_name.
 
  ELSE.
**
    EXEC SQL.
 
      DECLARE CURSOR c_1 IS SELECT tipo_operacao FROM movimento_contribuicao
             WHERE ROWNUM <= 10000;
 
          wa movimento_contribuicao.tipo_operacao%type;
BEGIN
       OPEN c_1;
     LOOP
        FETCH c_1 INTO wa; "Not able to return data to SAP system
         EXIT WHEN c_1%NOTFOUND;
      END LOOP;
  END;
 
    ENDEXEC.
      EXEC SQL.
      DISCONNECT 'dblink_con'
    ENDEXEC.
  ENDIF.
 

On the FETCH c_1 INTO wa; code Iu2019m moving the data to an Oracle variable, wa movimento_contribuicao.tipo_operacao%type;, and not to an program variable. Can anyone explain me this how this is +possible using this PL/SQL procedure?

Thanks in advance,

Best Regards

João Martins

Edited by: Rob Burbank on May 7, 2010 10:25 AM