I need to update values in an existing table with values from a CSV file. The matching is only possible with the original CSV files. With the existing loading procedures, the values I need to read do not get parsed. I have two CSV files with different content in column x. Before loading the second one to the DB, I want to update the values in the DB according to the data in the file 2, column x, so that the matching is still possible. How can I simply load the CSV file 2 with utl file read to a temp table?
1
-
you may use SQL*LoaderAndrey Khmelev– Andrey Khmelev2017-08-18 19:17:52 +00:00Commented Aug 18, 2017 at 19:17
Add a comment
|
1 Answer
to read a file in plsql u should to do the next steps, 1. You should create a objet directory. ea
create directory dir_tmp as ‘c:\temp’;
grant read, write on directory dir_tmp to user;
2.to read.
create or replace procedure reading is
v_file utl_file.file_type;
v_line varchar2(1024);
begin
v_file := utl_file.fopen (‘DIR_TMP’, ‘test_utl_file.txt’,‘r’);
loop
utl_file.get_line (v_file, v_line);
dbms_output.put_line (v_line);
end loop;
utl_file.fclose(v_file);
exception
when no_data_found then
dbms_output.put_line (‘End file’);
end;
/
3.- there are several exception useful ea.
utl_file.invalid_operation
utl_file.access_denied
Erod
1 Comment
Michael Russo
Just a note for anyone who sees this in the future, since I wasted some time trying to get this work without realizing it: You can only work with directories and files that are local to the machine running oracle. Hope this helps someone out there.