| Importing .csv to Oracle table without sql loader [message #372825] |
Mon, 12 March 2001 08:18  |
Robert Angel
Messages: 8
Registered: March 2001
|
Junior Member |
|
|
Can I do this?
I've read countless manuals / web pages and not come up with a definitive how to, if someone could give me the code to pull comma delimited data into Oracle table ra_tt_price2 with fields (prodid, price) I would be delirously happy!!!!
|
|
|
|
| Re: Importing .csv to Oracle table without sql loader [message #372827 is a reply to message #372825] |
Mon, 12 March 2001 10:04  |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi Robert,
I haven't tested the following code, but it should do the job in principle.
Please note that you have to add the directory where your csv file is located
to the accessible directories list in your INITxxx.ORA
i.e. UTL_FILE_DIR = <directory name>
declare
fhandle utl_file.file_type;
fbuffer varchar2(1000);
begin
fhandle := utl_file.fopen ('c:\csv\', 'csv_file', 'r');
while true loop
utl_file.get_line (fhandle, fbuffer);
insert into ra_tt_price2 (prodid, price)
-- I assumed the target data type is number in both cases.
-- Conversion part is likely to be more complex depending
-- on characteristics of source data. So, watch out ...
select to_number(substr(fbuffer,1,instr(fbuffer,','))),
to_number(substr(fbuffer,instr(fbuffer,',')+1))
from dual;
end loop;
exception
when value_error then
-- fbuffer smaller than file record length
utl_file.fclose (fhandle);
rollback;
when no_data_found then
-- end of file reached
utl_file.fclose (fhandle);
commit;
when others then
-- data conversion problem or anything else
utl_file.fclose (fhandle);
rollback;
end;
/
|
|
|
|
| Re: Importing .csv to Oracle table without sql loader [message #372837 is a reply to message #372825] |
Tue, 13 March 2001 05:49 |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi Robert,
1. Issue a SHOW ERRORS after executing the code to check if there are really no errors.
2. Place some DBMS_OUTPUT.PUT_LINE statements in your code for debugging purposes.
Don't forget to SET SERVEROUTPUT ON before you run the program for dbms_output messages to be presented.
3. Replace select (substr(fbuffer,1,instr(fbuffer,','))), ... by
select (substr(fbuffer,1,instr(fbuffer,',')-1)), ... to remove the ',' itself from prodid value. Sorry, my fault ...
|
|
|
|
| Re: Importing .csv to Oracle table without sql loader [message #372840 is a reply to message #372825] |
Tue, 13 March 2001 07:54 |
Robert Angel
Messages: 8
Registered: March 2001
|
Junior Member |
|
|
Think the problem may be with the 'accessible directories list in your INITxxx.ORA
i.e. UTL_FILE_DIR = ' where I haven't been able to locate the file in question to ensure that my directory is accessable. These are the errors (when I strip the error trapping out)
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 82
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at line 5
I'm trying to get my dba to look into the issue.
Thanks for all your help and patience,
regards,
Robert.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
