Oracle Database Discussions

Dear All:

I am using Oracle Enterprise Edition Release 8.0.5.0.0
I have found a procedure to upload the csv files in Oracle database, but I received the subjected error during compilaion.
Procedure is as follows

create or replace procedure test_emp_proc_csv2DB(p_filename varchar2) is

l_output utl_file.file_type;
l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnValue VARCHAR2(32767);
--replaced_str varchar2(32767);
l_colCnt NUMBER DEFAULT 0;
col_length NUMBER;
str_st_pos NUMBER;
str_end_pos NUMBER;
l_cnt NUMBER DEFAULT 0;

TYPE areas_type IS TABLE OF areas%ROWTYPE;
areas_tt areas_type := areas_type();

BEGIN

l_output := utl_file.fopen('C:\ERP_DB',p_filename,'r',32767);

dbms_output.put_line('************ File opened succesfully *******************');

LOOP
BEGIN
dbms_output.put_line('I am in BegIning of for loop ie line.. ' || (l_colCnt+1));
utl_file.get_line(l_output,l_columnValue);
l_columnValue := l_columnValue || ',';
col_length := dbms_lob.getlength(l_columnValue); --remarked by hassan bcz giving error

col_length := 22;added by hassan for testing
dbms_output.put_line('CSV file data is : ' || l_columnValue);

str_st_pos := 0;
l_cnt := 1;
areas_tt.extend;

FOR j IN str_st_pos.. col_length
LOOP
IF(instr(l_columnValue,'",') != 0) THEN
str_end_pos := instr(substr(l_columnValue,str_st_pos,col_length),'",');
l_columnValue := substr(l_columnValue,str_st_pos,col_length);

IF(l_cnt = 1) THEN
areas_tt(areas_tt.last).CODE := substr(l_columnValue,2,(str_end_pos-2));
dbms_output.put_line('.1..Areas Code...' || substr(l_columnValue,2,(str_end_pos-2)));

ELSIF(l_cnt = 2) THEN
areas_tt(areas_tt.last).AREANAME := substr(l_columnValue,3,(str_end_pos-3));
dbms_output.put_line('.2..Area Name' || areas_tt(areas_tt.last).AREANAME);

ELSIF(l_cnt = 3) THEN
areas_tt(areas_tt.last).CNTRYCODE := substr(l_columnValue,3,(str_end_pos-3));
dbms_output.put_line('.3..Country Code...' || areas_tt(areas_tt.last).CNTRYCODE);
END IF; -- l_cnt

l_cnt := l_cnt+1;
str_st_pos := str_end_pos+1;

END IF; -- end if for finding the position of the comma(,)"
END LOOP; -- close for loop of j ie looping the individual CSV record

FOR areas_rec IN areas_tt.first .. areas_tt.last
LOOP
INSERT INTO areas(CODE, AREANAME, CNTRYCODE)
VALUES( areas_tt(areas_rec).CODE,areas_tt(areas_rec).AREANAME,
areas_tt(areas_rec).CNTRYCODE);

COMMIT;
END LOOP; -- end for code_rec

areas_tt.delete;

l_colCnt := l_colCnt+1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;-- close begin/end after for of i
END LOOP; -- close for loop of i ie looping for each record in the CSV file

COMMIT;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
dbms_output.put_line('I am in EXCEPTION blcok ie File location or name '|| p_filename || ' was invalid');
utl_file.fclose_all;

WHEN OTHERS THEN
dbms_output.put_line('I am in OTHERS exception block 1 :::' || sqlerrm);
utl_file.fclose_all;
END;
----------------------------------- Procedure Ends ----------------------------------------------------

I am facing problem at this point
col_length := dbms_lob.getlength(l_columnValue); --remarked by hassan bcz giving error

When I hard coded the col_length = 22 (as per the csv file record length it works fine) e.g
--col_length := 22;--added by hassan for testing


Wud anybody plz help me?

Thanks in advance.

Hassan