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

[Former Member]

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

[Former Member]

Hello Guys.

Please reply on my query.

[Former Member]

SELECT statement is required to fetch the data from database from SAP.

Also MODIFY & UPDATE is required to update the database.

INSERT also can insert the data into database.

you can access transaction /nabaphelp to know more about the above statements.

Reward points if useful

[Former Member]

Hello Guys,

Do some one help me in the below Question.

Swati...

[Former Member]

Hi

If you need to read or update an another database it's used to use a DBCON (database connection) in order to create a SQL native code.

See the help for EXEC SQL statament.

Max

[Former Member]

hi,

Pls chk this link.

http://sap4.com/contentid-146.html

Rgds

Reshma

[Former Member]

Hi Swati,

In your codes you are using ole, so please make sure you have object ADODB.

You can check it using tcode SOLE.

And to make sure you've created the connection successfully, please put a message before EXIT.

e.g.:

CREATE OBJECT con 'ADODB.Connection'.

IF NOT sy-subrc = 0.

MESSAGE i398(00) with ' Error when creating connection object'.

EXIT.

ENDIF.

Regards,

Hendy

[Former Member]

Hi swati,

1. for this u will also require help of basis team.

2. these are the steps.

a) make an entry in DBCON

b) make connection string

(on the physical application server,

so that it can connect to secondary database)

(this will be done by basis team,

in which, they will specify the

IP address of the secondary database server,

the DATABASE ID, and the port number)

c) then using open sql / native sql,

we can use the secondary database connection,

just like normal.

d) if we use open sql,

then there must be Y/Z table on

sap as well as secondary database,

and the field names , their type all should be identical.

regards,

amit m.