Skip to Content
0
Former Member
Jul 09, 2007 at 11:47 AM

How to Access oracle DB table through SAP..problem in code

84 Views

Hello Guys

I have to Access oracle table through SAP and need to create a report.

From Some source I got the following code . But it's not working , I am not able to update the table as well not able to access the data from table.

Can some one provide me the necessary help.

REPORT ZORACLE.

INCLUDE ole2incl.

DATA: con TYPE ole2_object,

rec TYPE ole2_object.

DATA SQL(1023).

DATA: BEGIN OF SPL OCCURS 0,

VAL(1023),

END OF SPL.

DATA: BEGIN OF I1 OCCURS 0,

F1(10) ,

F2 TYPE I,

END OF I1.

IF con-header IS INITIAL OR con-handle = -1.

CREATE OBJECT con 'ADODB.Connection'.

IF NOT sy-subrc = 0.

EXIT.

ENDIF.

CREATE OBJECT REC 'ADODB.Recordset'.

IF NOT sy-subrc = 0.

EXIT.

ENDIF.

ENDIF.

  • MDB connetion infomations ....

break-point.

CONCATENATE 'Provider=' '''OraOLEDB.Oracle''' ';'

INTO SQL.

CONCATENATE SQL 'Password=' '''prodx''' ';'

INTO SQL.

CONCATENATE SQL 'User ID=' '''prod''' ';'

INTO SQL.

CONCATENATE SQL 'Data Source=' '''sterlite''' ';'

INTO SQL.

CONCATENATE SQL 'Mode=' '''Share Deny None'''

INTO SQL.

  • MDB connection ...

CALL METHOD OF CON 'Open'

EXPORTING #1 = SQL.

*

  • Query (insert) statement ...

SQL = 'insert into ofusers values('.

CONCATENATE SQL '''Atul''' ',' '''121''' ')' INTO SQL.

  • Query run ...

CALL METHOD OF REC 'Open'

EXPORTING #1 = SQL

#2 = CON

#3 = '1'.

  • Query (select) statement ...

SQL = 'select * from ofusers'.

  • Query run ...

CALL METHOD OF REC 'Open'

EXPORTING #1 = SQL

#2 = CON

#3 = '1'.

  • Selecting MDB record into SAP internal table ...

DO.

CALL METHOD OF REC 'getstring' = SQL

EXPORTING #1 = '2' "Do not modify!

#2 = 1 "Do not modify!

#3 = '|' "Do not modify!

#4 = '|'. "Do not modify!

IF sy-SUBRC EQ 0.

REFRESH SPL. CLEAR SPL.

SPLIT SQL AT '|' INTO TABLE SPL.

LOOP AT SPL.

CASE SY-TABIX.

WHEN 1.

I1-F1 = SPL-VAL.

WHEN OTHERS.

I1-F2 = SPL-VAL.

ENDCASE.

ENDLOOP.

APPEND I1. CLEAR I1.

ELSE.

EXIT.

ENDIF.

ENDDO.

  • Result writing ...

LOOP AT I1.

WRITE: AT /1(10) I1-F1,

AT (10) I1-F2.

ENDLOOP.

  • connetion close & destroy

FREE OBJECT con.

FREE OBJECT rec.

Thanks in Advance

Swati Namdeo