|Home • Trainings • Quiz • Tips • Tutorials • Functional • Cert Q's • Interview Q's • Jobs • Testimonials • Advertise • Contact Us|
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
Now click on the ‘new entries’
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.
SELECT ACCOUNT_NUM into :dbn from PVACCOUNTBALANCE2 where ACCOUNT_NUM <> 'ACC0000005160'.
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.
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:
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
Please send us your feedback/suggestions at webmaster@SAPTechnical.COM
©2006-2007 SAPTechnical.COM. All rights reserved.
product names are trademarks of their respective companies. SAPTechnical.COM
is in no way affiliated with SAP AG.
Graphic Design by Round the Bend Wizards