Home » SQL & PL/SQL » SQL & PL/SQL » Problem In Loading The CSV File Into The Database
Problem In Loading The CSV File Into The Database [message #221497] Tue, 27 February 2007 03:31 Go to next message
lokesh.sql
Messages: 9
Registered: November 2006
Location: Pune
Junior Member
Hi All ,

I am not able to select the table xx_emp , after executing the procedure given below. The following problem occurs after executing the procedure.Kindly help me out and remembered for forever.

Thanks in advance,

Regards
Lokesh




Here is the table test_emp and xx_emp structures.




create table test_emp ( 
 EMPNO NUMBER(10),
 ENAME VARCHAR2(20),
 JOB   VARCHAR2(20));

create table xx_emp ( 
 EMPNO NUMBER(10),
 ENAME VARCHAR2(20),
 JOB   VARCHAR2(20));



EXCEL FILE:test_emp.csv
---------------------------------------------------------------------------------------
EMPNO ENAME   JOB
9999  CHARLES CLERK
1000  LOKESH  KIRAN
1001  GJDFKS  FKGL

----------------------------------------------------------------------------------------
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);


l_colCnt number default 0;

col_length number;

str_st_pos number; 

str_end_pos number;

l_cnt number default 0;

TYPE emp_type IS TABLE OF test_emp%ROWTYPE ; 

emp_tt emp_type := emp_type() ;

begin


l_output := utl_file.fopen('DBDIR','test_emp.csv','r',32767);

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

LOOP

begin

dbms_output.put_line('I am in Begning 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);

dbms_output.put_line('CSV file data is : ' || l_columnValue);

str_st_pos := 0;

l_cnt := 1;

emp_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 

emp_tt(emp_tt.last).EMPNO := substr(l_columnValue,2,(str_end_pos-2));


dbms_output.put_line('.1..EMPNO...' || substr(l_columnValue,2,(str_end_pos-2)));

elsif(l_cnt = 2) then

emp_tt(emp_tt.last).ENAME := substr(l_columnValue,3,(str_end_pos-3));

dbms_output.put_line('.2..ENAME...' || emp_tt(emp_tt.last).ENAME);

elsif(l_cnt = 3) then

emp_tt(emp_tt.last).JOB := substr(l_columnValue,3,(str_end_pos-3));

dbms_output.put_line('.3..JOB...' || emp_tt(emp_tt.last).JOB);



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 emp_rec IN emp_tt.first .. emp_tt.last LOOP

insert into xx_emp(EMPNO, ENAME, JOB)

values( emp_tt(emp_rec).EMPNO, emp_tt(emp_rec).ENAME, emp_tt(emp_rec).JOB);

commit; 

end loop; -- end for code_rec


emp_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


utl_file.fclose_all; 

when others then

dbms_output.put_line('In OTHERS exception block:::' || sqlerrm); 

utl_file.fclose_all;

end test_emp_proc_csv2DB;

/ 

Procedure created.

SQL> 
SQL> exec test_emp_proc_csv2DB('test_emp.csv');
*** File opened succesfully ***
I am in Begning of for loop ie line.. 1
CSV file data is : EMPNO,ENAME,JOB,
I am in Begning of for loop ie line.. 2
CSV file data is : 9999,CHARLES,CLERK,
I am in Begning of for loop ie line.. 3
CSV file data is : 1000,LOKESH,KIRAN,
I am in Begning of for loop ie line.. 4
CSV file data is : 1001,GJDFKS,FKGL,
I am in Begning of for loop ie line.. 5

PL/SQL procedure successfully completed.

SQL> select * from xx_emp;

     EMPNO ENAME                JOB
---------- -------------------- --------------------












     EMPNO ENAME                JOB
---------- -------------------- --------------------












     EMPNO ENAME                JOB
---------- -------------------- --------------------



24 rows selected.

SQL>  

[Updated on: Tue, 27 February 2007 04:51] by Moderator

Report message to a moderator

Re: Problem In Loading The CSV File Into The Database [message #221520 is a reply to message #221497] Tue, 27 February 2007 05:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The whole idea of csv files is NOT to process them with pl/sql, but to use either external tables of sql*loader
Re: Problem In Loading The CSV File Into The Database [message #221558 is a reply to message #221497] Tue, 27 February 2007 07:50 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Hi,

This piece of code is incorrect.You need to change the logic for this.

-------------------------------------------------------------------------
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),',',1,l_count)-1;

l_columnValue := substr(l_columnValue,str_st_pos,str_end_pos);

if(l_cnt = 1) then

emp_tt(emp_tt.last).EMPNO := l_columnValue;


dbms_output.put_line('.1..EMPNO...' || l_columnValue);

elsif(l_cnt = 2) then

emp_tt(emp_tt.last).ENAME := l_columnValue;

dbms_output.put_line('.2..ENAME...' || emp_tt(emp_tt.last).ENAME);

elsif(l_cnt = 3) then

emp_tt(emp_tt.last).JOB := l_columnValue);

dbms_output.put_line('.3..JOB...' || emp_tt(emp_tt.last).JOB);



end if; -- l_cnt

l_cnt := l_cnt+1;

str_st_pos := str_end_pos+2;

end if; -- end if for finding the position of the comma(,)"

end loop; -- close for loop of j ie looping the individual CSV record

---------------------------------------------------------------------------------

If, the requirement does not say specifically to use UTL_FILE to read the excel then you can use external table also to achieve the same.

--Puneet
Re: Problem In Loading The CSV File Into The Database [message #221582 is a reply to message #221497] Tue, 27 February 2007 09:36 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
Hi

If you need any infomation on creating external tables, the following link has some examples.

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11210

Hope this helps
Razz
Previous Topic: Objects in a Tablespace
Next Topic: Updating....
Goto Forum:
  


Current Time: Sun Dec 04 23:00:56 CST 2016

Total time taken to generate the page: 0.19372 seconds