Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation