This script can be used to read data from a flat file and insert into a table.<\/p>\n
UTL_FILE Read<\/p>\n
v_empno sample_emp.empno%type; BEGIN<\/p>\n v_dir := '\/usr\/tmp'; -- -------------------------------------------------------- LOOP UTL_FILE.GET_LINE(v_file, v_line);<\/p>\n EXCEPTION -- ---------------------------------------------------------- v_1st_Comma := INSTR(v_line, ',' ,1 , 1); v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1)); DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);<\/p>\n -- ------------------------------------------ INSERT INTO sample_emp UTL_FILE.FCLOSE(v_file);<\/p>\n COMMIT;<\/p>\n END; 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-1863","post","type-post","status-publish","format-standard","hentry","category-scripts"],"yoast_head":"\n
\nCREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)
\nAS
\nv_line VARCHAR2(2000); -- Data line read from input file
\nv_file UTL_FILE.FILE_TYPE; -- Data file handle
\nv_dir VARCHAR2(250); -- Directory containing the data file
\nv_filename VARCHAR2(50); -- Data filename
\nv_1st_Comma number;
\nv_2nd_Comma number;
\nv_3rd_Comma number;
\nv_4th_Comma number;
\nv_5th_Comma number;<\/p>\n
\nv_ename sample_emp.ename%type;
\nv_job sample_emp.job%type;
\nv_mgr sample_emp.mgr%type;
\nv_hiredate sample_emp.hiredate%type;
\n--v_sal sample_emp.sal%type;<\/p>\n
\nv_filename := 'sample.dat';
\nv_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');<\/p>\n
\n-- Loop over the file, reading in each line. GET_LINE will
\n-- raise NO_DATA_FOUND when it is done, so we use that as
\n-- the exit condition for the loop.
\n-- --------------------------------------------------------<\/p>\n
\nBEGIN<\/p>\n
\nWHEN no_data_found THEN
\nexit;
\nEND;<\/p>\n
\n-- Each field in the input record is delimited by commas. We
\n-- need to find the locations of the two commas in the line,
\n-- and use these locations to get the fields from v_line.
\n-- ----------------------------------------------------------<\/p>\n
\nv_2nd_Comma := INSTR(v_line, ',' ,1 , 2);
\nv_3rd_Comma := INSTR(v_line, ',' ,1 , 3);
\nv_4th_Comma := INSTR(v_line, ',' ,1 , 4);
\nv_5th_Comma := INSTR(v_line, ',' ,1 , 5);<\/p>\n
\nv_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);
\nv_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);
\nv_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));
\nv_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');
\n-- v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),'99999');<\/p>\n
\n-- Insert the new record into the DEPT table.
\n-- ------------------------------------------<\/p>\n
\nVALUES (v_empno,v_ename,v_job,v_mgr,v_hiredate);
\nEND LOOP;<\/p>\n
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"