Formatting Data File

There are eleven columns in the data file, in the following order:

1. Receipt number
2. Currency
3. Customer number
4. Payment method id
5. Receipt Type
6. Amount applied
7. Apply date
8. Apply GL date
9. Comments
10. Customer reference

The Excel worksheet must be formatted before exporting to Comma-Separated Values (CSV). There must be NO commas in any cell before exporting the data to CSV. The SQL*Plus script parses the data by commas, so any of the fields with commas will cause the program to fail

Remember to format the Amount Applied column without commas.
Preparing Data File
The file is usually saved with a .csv extension. It is renamed to remove spaces from the filename (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, creatcs1.dat.

The first row of the data file contains the column headings of the Excel worksheet. Remember to delete the first row in the data file before uploading the file to the server.
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:

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

$ cat creatcsh.dat | wc

Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. The scripts automatically sets the ORG ID and serveroutput settings in SQL*Plus, so it is not necessary to set it manually.

Since data from a text file is accessed from the SQL procedure, we must tell the 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;

Running the Create Cash API script
The SQL code for loading the data is in the creatcsh.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:Scriptscreatcsh.sql

Note that the changes are not committed, you must manually commit the changes once you are satisfied:

SQL> commit;