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