Apr 172011
This script can be used to read data from a flat file and insert into a table.
UTL_FILE Read
CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number) AS v_line VARCHAR2(2000); -- Data line read from input file v_file UTL_FILE.FILE_TYPE; -- Data file handle v_dir VARCHAR2(250); -- Directory containing the data file v_filename VARCHAR2(50); -- Data filename v_1st_Comma number; v_2nd_Comma number; v_3rd_Comma number; v_4th_Comma number; v_5th_Comma number; v_empno sample_emp.empno%type; v_ename sample_emp.ename%type; v_job sample_emp.job%type; v_mgr sample_emp.mgr%type; v_hiredate sample_emp.hiredate%type; --v_sal sample_emp.sal%type; BEGIN v_dir := '/usr/tmp'; v_filename := 'sample.dat'; v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r'); -- -------------------------------------------------------- -- Loop over the file, reading in each line. GET_LINE will -- raise NO_DATA_FOUND when it is done, so we use that as -- the exit condition for the loop. -- -------------------------------------------------------- LOOP BEGIN UTL_FILE.GET_LINE(v_file, v_line); EXCEPTION WHEN no_data_found THEN exit; END; -- ---------------------------------------------------------- -- Each field in the input record is delimited by commas. We -- need to find the locations of the two commas in the line, -- and use these locations to get the fields from v_line. -- ---------------------------------------------------------- v_1st_Comma := INSTR(v_line, ',' ,1 , 1); v_2nd_Comma := INSTR(v_line, ',' ,1 , 2); v_3rd_Comma := INSTR(v_line, ',' ,1 , 3); v_4th_Comma := INSTR(v_line, ',' ,1 , 4); v_5th_Comma := INSTR(v_line, ',' ,1 , 5); v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1)); v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1); v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1); v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1)); v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY'); -- v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),'99999'); DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate); -- ------------------------------------------ -- Insert the new record into the DEPT table. -- ------------------------------------------ INSERT INTO sample_emp VALUES (v_empno,v_ename,v_job,v_mgr,v_hiredate); END LOOP; UTL_FILE.FCLOSE(v_file); COMMIT; END; |
UTL_FILE Write
I will try to post this very soon.
Related Articles:
admin
hi
Hi, the program is show warning pls verify and send to me
The code works and helps me a lot.
hii pls can you give us a example for pulling data from a table on to a output file
Hi Sir
Pls give the sample code for write(OUT BOUND).
DECLARE
v_line VARCHAR2 (2000); — Data line read from input file
v_file UTL_FILE.file_type; — Data file handle
v_dir VARCHAR2 (250); — Directory containing the data file
v_filename VARCHAR2 (50); — Data filename
v_1st_comma NUMBER;
v_2nd_comma NUMBER;
v_3rd_comma NUMBER;
v_4th_comma NUMBER;
v_5th_comma NUMBER;
v_empno scott.emp.empno%TYPE;
v_ename scott.emp.ename%TYPE;
v_job scott.emp.job%TYPE;
v_mgr scott.emp.mgr%TYPE;
v_hiredate scott.emp.hiredate%TYPE;
–v_sal sample_emp.sal%type;
BEGIN
v_dir := ‘D:\Test’;
v_filename := ‘domssa_sample.dat’;
v_file := UTL_FILE.fopen (v_dir, v_filename, ‘r’);
– ——————————————————–
— Loop over the file, reading in each line. GET_LINE will
— raise NO_DATA_FOUND when it is done, so we use that as
— the exit condition for the loop.
— ——————————————————–
LOOP
BEGIN
UTL_FILE.get_line (v_file, v_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
– ———————————————————-
— Each field in the input record is delimited by commas. We
— need to find the locations of the two commas in the line,
— and use these locations to get the fields from v_line.
— ———————————————————-
v_1st_comma := INSTR (v_line, ‘,’, 1, 1);
v_2nd_comma := INSTR (v_line, ‘,’, 1, 2);
v_3rd_comma := INSTR (v_line, ‘,’, 1, 3);
v_4th_comma := INSTR (v_line, ‘,’, 1, 4);
v_5th_comma := INSTR (v_line, ‘,’, 1, 5);
v_empno := TO_NUMBER (SUBSTR (v_line, 1, v_1st_comma – 1));
v_ename :=
SUBSTR (v_line, v_1st_comma + 1, v_2nd_comma – v_1st_comma – 1);
v_job := SUBSTR (v_line, v_2nd_comma + 1, v_3rd_comma – v_2nd_comma – 1);
v_mgr :=
TO_NUMBER (SUBSTR (v_line,
v_3rd_comma + 1,
v_4th_comma – v_3rd_comma – 1
)
);
v_hiredate :=
TO_DATE (SUBSTR (v_line,
v_4th_comma + 1,
v_5th_comma – v_4th_comma – 1
),
‘DD-MON-YYYY’
);
– v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),’99999′);
DBMS_OUTPUT.put_line ( v_empno
|| ‘ ‘
|| v_ename
|| ‘ ‘
|| v_job
|| ‘ ‘
|| v_mgr
|| ‘ ‘
|| v_hiredate
);
– ——————————————
— Insert the new record into the DEPT table.
— ——————————————
– INSERT INTO sample_emp
– VALUES (v_empno, v_ename, v_job, v_mgr, v_hiredate);
END LOOP;
UTL_FILE.fclose (v_file);
– COMMIT;
END;
———–
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at line 20
Hi,
Can anybody tell me if we can write data in a formatted way in excel with the help utl_file.
Regards