Yesterday, I and my team mates conducted a brownbag session about a functionality that allows loading of CSV files to a database table. The general overview of this functionality is to call a Unix shell script that invokes SQL Loader to load the CSV to the table.
The first stage is uploading the CSV file from the user’s local drive to the Unix server. Oracle Forms must validate the file if the file is a CSV file and the filename has no space. Once validated, the CSV will be transferred to the server.
Oracle Forms will now call a Unix shell script that will invoke SQL Loader to load the CSV from the server to the table. A stored procedure will be called to validate the data loaded in the table. All invalid records will be flag as ‘Y’ in the processed_ind field of that table and the cause of the error is saved in the column of the table.
If there are rejected records, the form will prompt the user if he want to see the rejected records. If yes, the rejected records will be displayed.
At the end of the brownbag, there are attendees who asked why CSV is loaded in this matter instead of creating a new batch program that will insert the records to the table. I answered them that the purpose of this functionality is for the business user which no technical background should be able to load the records from the CSV file with ease. Even without learning about Unix, Pro *C batch run and shell script. The role of the solutions designer is to hide these technical things to a normal user with no technical background.
Another question has been raised by two attendees, why there is a need to call the SQL Loader? Why not do the loading in the Oracle Forms itself since it’s capable of reading files? I answered them with a question. Do Oracle Forms supports reading files? He answered yes but not on the version used by our client. I said to them, in that case then if Oracle Forms can read files, then the developer needs to create a logic that will mimic the functionality of the SQL Loader. This effort will eat up more time that just merely call the SQL Loader. Thinking of Cost -Benefit Analysis, it is advisable to complete the requirement in the shortest time possible to keep the cost low therefore invoking SQL Loader is much efficient to achieve this goal.