Obtaining Data File

User passes the Microsoft Excel spreadsheet for the agent-customer relationships from analyst. Analyst extracts the customer number and agent number onto a new worksheet in Excel and saves the worksheet as type “Formatted Text (Space delimited)”. A sample of the data file is shown below:

01AAS00101PGCMM       999999
01ARE00101PGCMM       999999
01BLI00101PGCMM       999999
01BMA00101PGCMM       999999
01CPA00101PGCMM       999999
01CTR00101PGCMM       999999
01DEQ00101PGCMM       999999

The customer number is usually about 15 characters wide, right padded with spaces to 22 characters. The agent number is 6 characters wide.

This data file is passed to the DBA for loading.

Preparing Data File
The exported file is saved with a .prn extension by default. Rename the file (in Command Prompt) to an 8.3 format (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example:

Type of Listing	File name
Provisor Customer Relationships	provcust.dat
Quantum Customer Relationships	quancust.dat

The first row of the data file contains the column headings of the Excel worksheet. This header row must be deleted, so that there is only data in the file.

Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:

Server Name	Upload Directory
testdata	/raid01/oraclone/custload
oradata	/raid01/oraprod/custload

After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Rename (copy and paste) the data file to ‘datafile.dat’. To obtain the number of rows in the file, execute the command (shown in bold):

$ cat datafile.dat | wc

This command returns three figures. The first figure is the number of lines in the file. Verify that the data file has the correct number of rows to upload.

Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. Set the ORG ID and serveroutput settings in SQL*Plus before running the SQL procedure:

SQL> exec dbms_application_info.set_client_info(’21’);

SQL> set serveroutput on

Recall that ‘21’ refers to the ID of the Trinidad Operating Unit. Since the customer data is tied to the ORG ID, you must ensure that the correct ORG ID is set before running the code.

Since data from the text file is being accessed from the SQL procedure, the instance must have access to the file system. We must tell the Oracle instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:

SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;

To verify that the directory has been set up, query the DBA_DIRECTORIES view:

SQL> select * from DBA_DIRECTORIES;

Note that you only need to run the ‘CREATE DIRECTORY’ command once.

Running the TCA API
The SQL code for loading the data is in the tcaapi.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:

SQL> @ C:Scriptstcaapi.sql

Note that the changes are not committed, you must manually commit the changes once you are satisfied the script completed without errors:

SQL> commit;

Errors Encountered
Problem: The API code is run with several dozen records in the data file and the following error is thrown:

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

ORA-06512: at “SYS.DBMS_OUTPUT”, line 32

ORA-06512: at “SYS.DBMS_OUTPUT”, line 97

ORA-06512: at “SYS.DBMS_OUTPUT”, line 112

ORA-06512: at line 85

Solution: This error is caused by the put_line statements. To overcome this, disable printing output from the procedure (set the value of c_print_data to ‘N’), or remove all put_line statements. Alternatively, set serveroutput to a large buffer:

SQL> set serveroutput on size 1000000

Problem: The code runs and completes successfully, but does not read or populate any records.

Solution: The record header from Excel still exists in the data file. Remove the headers and rerun.