Newbie PLSQL devekoper needs help

[jlakhani]

Hi. these are my requirements

1.read a csv file that has 3 fields
employee_id,department_name,subdepartment_name

2.compare that data with table sop_departments that has columns: department_name,
subdepartment_name and employee_id from sop_employee

Compare the data. if the csv file has a new record then insert it into sop_employees

insert it into sop employee: values employee_id and department_id from (sop_departments).

I started putting the pl sql together but I am not good with pl sql
=============================================================
CREATE OR REPLACE PROCEDURE LOAD_SOP_DATA IS

file_handle UTL_FILE.FILE_TYPE
department_name CHAR;
subdepartment_name CHAR;
retrieved_buffer VARCHAR2(100);


BEGIN

CURSOR COMPARE

IS
select department_name, subdepartment_name
...............
...............
..............
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(file_handle);
END;

Thanks in advance for help

[gandolf989]

1) You don't need a cursor on the table.
2) You need to open the file, read the first line and declare a while loop that exits when you get no data found.
3) Inside the above loop do an update that updates the table if the records are newer.
4) close the file
5) include an exception section that closes the file and reraises any error.

If you really want to learn PL/SQL get a good PL/SQL book and write the procedure. Doing is the best form of learning.

[jlakhani]

thats fine but for now can i pls get some help. its urgent and it will take me some time.

[hrishy]

Hi

There is no need to write pl/sql code at all...

1)create a table called sop-t table
2)load the records into this table
3)write your insert statement as follows
insert into sop_employee
select department_name,subdepartment_name ,employee_id from sop_employee
minus
select department_name,subdepartment_name ,employee_id from sop-t

truncate sop-t table and then later drop it..

regards
Hrishy

[hacketta]

It you do decide you want to go the PL/SQL route you want want to visit this URL:

http://www.quest-pipelines.com/Pipel...s/Gen_util.sql

It's a PL/SQL package you can download that parses a line from a CSV file and returns it as a nested table array. I reckon it could make life considerably simpler.

Hope it helps.

Austin