Q

Reading/inserting data from SAP tables

This Content Component encountered an error
I have SAP using SQL Server, and I'm building software that has Oracle as database. I want to create one procedure in Oracle that will read information from SAP tables and insert it in Oracle database table. How can I achieve this task?
Starting with Oracle9i there is a feature that enables you to create what is called an EXTERNAL table. This is a read only table that you can use to extract data in your Oracle stored procedure. You may have to create a view on top of this external table.

For example, you may have a process that writes the SAP table data into a file. The external table will use that file as its data source. Your view will be based on the external table and used in your stored procedure.

For example, say you have an external table called SAP_INVOICE_DISTRIBUTION that uses a file as follows:

CREATE TABLE SAP_INVOICE_DISTRIBUTION
(header_id number,
 line_id   number)
ORGANIZATION EXTERNAL
(
 TYPE oracle_loader
 DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
 RECORDS DELIMITED BY NEWLINE
 LOGFILE log_dir:'log_invoice_distribution'
 FIELDS TERMINATED BY ','
 MISSING FIELDS ARE NULL
)
LOCATION('invoice_distribution.data')
)
PARALLEL 5
REJECT LIMIT UNLIMITED

The data_dir and log_dir need to be set up by your DBA.

Now, if you create a view sap_data_view as SELECT * FROM SAP_INVOICE_DISTRIBUTION, you can use the SAP_DATA_VIEW in your procedure to load data.

You can get fancy and dynamically create the tables using PL/SQL based on your different source files and data layout in the text files.

This was first published in February 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close