topic Re: Retreiving data from external DB using cursors (Native SQL) in Application Development and Automation Discussions

Retreiving data from external DB using cursors (Native SQL)

Former Member — Thu, 06 May 2010 14:39:23 GMT

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

Re: Retreiving data from external DB using cursors (Native SQL)

Former Member — Fri, 07 May 2010 10:01:45 GMT

Hi,

Can anyone help me on this subject?

I'll be appreciated.

Best Regards

Re: Retreiving data from external DB using cursors (Native SQL)

former_member358980 — Fri, 07 May 2010 12:54:44 GMT

Joao,

Try FETCH NEXT CURSOR c1 INTO TABLE TAB.

Are the both table fields from the same type?

Regards

Re: Retreiving data from external DB using cursors (Native SQL)

Former Member — Fri, 07 May 2010 14:17:35 GMT

Hi Joao,

Regarding your first question: It looks like there's a conversion problem (with one or more columns) from your Oracle table to your ABAP internal table.

In the following link you can see (in your case in the SELECT section) the type conversions used for each type combination.

Some combinations lead to an oracle error (specified in the "Result" column), like in your case (error 932).

http://help.sap.com/saphelp_470/helpdata/EN/a3/74caa1d9c411d1950e0000e8353423/content.htm

Regarding your second question: You can use the PERFORMING addition in the EXEC SQL statement.

You can see an example in the following link:

http://help.sap.com/saphelp_470/helpdata/EN/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Hope it helps.

Regards,

Ana Luisa.

Re: Retreiving data from external DB using cursors (Native SQL)

Former Member — Fri, 07 May 2010 15:24:54 GMT

Hi Ana,

Thanks for your reply but isn´t the oracle server syntax diferent from SQL server??

They haven´t got syntax error, only problems trying to send the data to an SAP internal table.

Regards

Joao Martins

Edited by: joao martins on May 10, 2010 4:41 PM