Retrieve data from Separate Oracle System

By Ribhu Ahuja, TCS

Suppose we have a separate Oracle system in which there is a database table - PVACCOUNTBALANCE2 and we want to bring the data of that table into SAP system. The first thing to be done for that is making a DB connection. TCode DBCO is used to create a DBCON connection.  

Open TCode DBCO and click on the change button. 

Now click on the ‘new entries’ button.  

We see the following screen in which we make all the required entries: 

In the box, DB Connection, give any name of the connection. It is to be noted that we would be using this connection name in our SAP programs to connect to the oracle system. 

In DBMS, choose the option ORA for oracle system. In the user name box, give the name of the oracle system user. We see two boxes for DB password – one is the password box and the other one is the confirm password box. Give the password in both the boxes. In Conn. Info box, give the name of the Oracle server – we may ask the name of the same from the Oracle system Administrator. Check the check box Permanent to make the connection permanent and give the connection limit as 5. Once we save the entry and come back, we see the following screen:   

Now, open SE38 and write a program to retrieve the data from PVACCOUNTBALANCE2 table of the Oracle system. This table has 3 fields:

ACCOUNT_NUM – type char length 20

UNBILLED_USAGE type Int4 length 10

ACCOUNT_BALANC type Int4 length 10 

In order to retrieve the data, we need to use similar data type fields but when I used int, it gave me a short dump. So I used char 10 instead of int4 10 and it worked. 

First of all we make a program to retrieve a single field from the Oracle database. Mentioned below is the code: 

REPORT  ZDATAFRMORA_RIBZ.
data: DBN(20).
* opening the connection
EXEC SQL.
  CONNECT TO 'MYCONNECTION'
ENDEXEC.

*executing the select command
EXEC SQL.
  SELECT ACCOUNT_NUM into :dbn from PVACCOUNTBALANCE2
ENDEXEC.

*closing the connection
EXEC SQL.
  DISCONNECT :'MYCONNECTION'
ENDEXEC.
WRITE: / dbn.

This program connects to the oracle server using dbcon ‘myconnection’ and retrieves the value of a single ACCOUNT_NUM into variable DBN. Below is the output: 

Note the format of the program – there is no period (.) in the statements between EXEC SQL. and ENDEXEC. Also note how the select statement is used. Now, let us see how we can use the where condition in the same program: 

REPORT  ZDATAFRMORA_RIBZ.
data: DBN(20).

EXEC SQL.
  CONNECT TO 'MYCONNECTION'
ENDEXEC.

EXEC SQL.  

  SELECT ACCOUNT_NUM into :dbn from PVACCOUNTBALANCE2 
  where ACCOUNT_NUM <> 'ACC0000005160'.

ENDEXEC.

EXEC SQL.
  DISCONNECT :'MYCONNECTION'
ENDEXEC.
WRITE: / dbn. 

The output comes as below. Note that for ‘not equal to’ in oracle ‘<>’ is used, NE won’t work there and if we use NE, it gives short dump. 

Now, let us make a program to read the entire table. It is possible to do so with help of declaring a ‘cursor type’ data and using fetch statement. The code is shown below: 

REPORT  ZDATAFRMORA_RIBZ.
DATA: c TYPE cursor.
data: DBN(20).
data: begin of ithead occurs 0,
      ACCOUNT_NUM(20),
      UNBILLED_USAGE(20),
      ACCOUNT_BALANCE(20),
      end of ithead.
EXEC SQL.
  CONNECT TO 'MYCONNECTION'
ENDEXEC.

EXEC SQL.
  open c for
    SELECT * from PVACCOUNTBALANCE2
ENDEXEC.

DO.
  EXEC SQL.
    FETCH NEXT c INTO :ithead
  ENDEXEC.
  append ithead.
  IF sy-subrc ne 0.
    EXIT.
  ENDIF.
ENDDO.

EXEC SQL.
  CLOSE c
ENDEXEC.

EXEC SQL.
  DISCONNECT :'MYCONNECTION'
ENDEXEC.
loop at ithead.

  WRITE: / ithead-ACCOUNT_NUM, ithead-UNBILLED_USAGE, 
   ithead-ACCOUNT_BALANCE.

endloop.

The output of the program is shown above. One very important thing to be noted here is the declaration of cursor c. Since we declared it as: 

DATA: c TYPE cursor. 

It is to be made sure that wherever we use this cursor ‘c’, we use it in small and not in caps ( ‘C’) otherwise it would give a short dump – the cursor declaration and use are case sensitive.