Script to read data from comma separated file and insert into a custom table

/*============================================================================+
|
+=============================================================================+
| |
| File Name : NAPP_GLOBAL_RESP_FIX.sql |
| Object Name : NAPP_GLOBAL_RESP_FIX.sql |
| |
| Description : |
| |
| Revision History : |
| |
| Ver Date Name Revision Description |
| === ========= ============== ========================================== |
| 1.0 03-DEC-2010 Shekhar Created the program |
+=============================================================================*/
SET PAGESIZE 0;
SET LINESIZE 10000;
SET SERVEROUTPUT ON SIZE 100000;
SET TRIMSPOOL ON;
SET DEFINE OFF;


DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_eof CHAR;
l_user_name VARCHAR2 (150);
l_resp_name VARCHAR2 (500);
l_text VARCHAR2 (5000);
l_start_date DATE;
l_start_date_str VARCHAR2(50);
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_resp_start_date date;
l_message varchar2(100);
l_dir_name varchar2(100);
l_item varchar2(100);
l_org varchar2(100);
c1 number;
c2 number;
l_value varchar2(32);

BEGIN

BEGIN

select name into l_dir_name from v$database;
dbms_output.put_line('UTL file directory '||'/usr/tmp/'||l_dir_name);

END;

Fnd_Global.apps_initialize (1429,20420,1);
l_eof := 'N';
l_file_handle :=
UTL_FILE.FOPEN
('/usr/tmp/'||l_dir_name
,'item_cat_value.csv'
,'r'
);

LOOP
-- l_user_id := -1;
-- l_resp_id := -1;
-- l_resp_appl_id := -1;
l_message:='SUCCESS';
-- l_resp_start_date := null;

BEGIN
UTL_FILE.GET_LINE (l_file_handle, l_text);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_eof := 'Y';
END;

EXIT WHEN l_eof = 'Y';

c1:=instr(l_text,',');
c2:=instr(l_text,',',c1+1);

l_item := SUBSTR (l_text, 1, INSTR (l_text, ',') - 1);
l_org := REPLACE(SUBSTR (l_text
, c1 + 1
, c2 -c1
),',','');
l_value := SUBSTR (l_text,c2+1,length(l_text));

BEGIN

INSERT INTO napp_item_issue(item,org,napp_intransit) VALUES (l_item,l_org,l_value);


COMMIT;

Exception when OTHERS then

-- write_log ('ERROR: User name ' || l_user_name || 'do not exist');
l_message:='FAILED:VALIDATION';

END;

-- IF l_user_id=-1 OR l_resp_id=-1 OR l_resp_appl_id=-1 OR l_resp_start_date is NULL then

-- l_message:='FAILED:DATA MISSING';

-- ELSE

-- BEGIN

/* fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id
,responsibility_id => l_resp_id
,responsibility_application_id => l_resp_appl_id
,security_group_id => 0
,start_date => l_resp_start_date
,end_date => NULL
,description => l_resp_name
,update_who_columns => 'Y'
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_message:='FAILED:API';
END ;

END IF;

dbms_output.put_line(l_user_name||'|'||l_resp_name||'|'||l_message);
*/

END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('When others ' || SQLERRM);
RAISE;

END;
/