Page 245
SQL*LOADER can create multiple logical records from a physical record and load multiple tables in a procedure. This is done using multiple into clauses in the control file.
SQL*LOADER can also create a single logical record from multiple physical records using the concatenate and continueif clauses.
SQL*LOADER can also be initiated from the command line by using the SQLLOAD keyword. This option is used with an operating system other than Windows.
This chapter completes Part III. You have now learned about the PL/SQL language used in applications discussed in the next several parts of the book. You have also learned about SQL*LOADER, which is used to load data into the Oracle tables.
The next chapter is about Oracle Forms 4.5, the product used to create the graphical user interface (GUI) applications that manage and display the data contained in the Oracle tables.
The next chapter introduces you to an Oracle form and tells you how to use it. Oracle forms have a sophisticated searching capability, as you'll learn in the next chapter.
The task of this installment is to populate the tables created in installment 2 with data from the original Employee database that is to be replaced by this project. The data from the original system is in the "RAWDATA.CSV" file. The data is in a comma-delimited format.
The fields in the logical record are:
last_name,first_name, absences, wages, street, city, state, phone, social_security_number, employment_date, birth_date, current_position, position_date, department, department_name, department_1, historic_position_1, position_date_1, wages_1, comments_1, department_2, historic_position_2, position_date_2, wages_2, comments_2, department_3, historic_position_3, position_date_3, wages_3, comments_3, department_4,
Page 246
historic_position_4, position_date_4, wages_4, comments_4, department_5, historic_position_5, position_date_5, wages_5, comments_5, department_6, historic_position_6, position_date_6, wages_6, comments_6, purchase_date_1, optician_1, cost_1, check_number_1, purchase_date_2, optician_2, cost_2, check_number_2, purchase_date_3, optician_3, cost_3, check_number_3, tool_purchase_date_1, payroll_deduct_1, tool_name_1, tool_cost_1, payment_amount_1, last_payment_amount_1, first_payment_date_1, last_payment_date_1, tool_purchase_date_2, payroll_deduct_2, tool_name_2, tool_cost_2, payment_amount_2, last_payment_amount_2, first_payment_date_2, last_payment_date_2, tool_purchase_date_3, payroll_deduct_3, tool_name_3, tool_cost_3, payment_amount_3, last_payment_amount_3, first_payment_date_3, last_payment_date_3
Create a temporary Oracle table to hold the values in the "RAWDATA.CSV" file. Write an SQL*LOADER procedure to populate the temporary table. Write a PL/SQL conversion program to populate the emp_dept, emp, emp_tools, and emp_glasses tables from the data in the temporary table.
Note that the employees in the original Employee database did not have a payroll number and that all employees in the new database will have a payroll number as the primary key. Your conversion program has to generate the payroll number for each employee, and populate the primary and foreign key fields in the tables.l
Page 247
11 Using an Oracle Form249
12 Using the Object Navigator to Create Your First Oracle Form265
13 Formatting Your Form295
14 Creating and Modifying Master-Detail Forms337
15 Using Triggers and Modifying Classes381
16 Calling Other Forms and Using Menus411
Page 248