how to load data from UTL file to database
866529Jun 3 2011 — edited Jun 3 2011Hi All,
I am new in this technologies.
I am facing below problem to load data from utl file to database .
below is the script written by me :
---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE load_data AS
v_line VARCHAR2(2000);
v_file UTL_FILE.FILE_TYPE;
v_dir VARCHAR2(250);
v_filename VARCHAR2(50);
v_1st_Comma NUMBER;
v_2nd_Comma NUMBER;
v_deptno NUMBER;
v_dname VARCHAR2(14);
v_loc VARCHAR2(13);
BEGIN
v_dir := ':f/rashi/dataload';
v_filename := 'fake.txt';
v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
EXCEPTION
WHEN no_data_found THEN
exit;
END;
v_1st_Comma := INSTR(v_line, ',' ,1 , 1);
v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);
v_deptno := SUBSTR(v_line, 1, v_1st_Comma-1);
v_dname := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);
v_loc := SUBSTR(v_line, v_2nd_Comma+1);
DBMS_OUTPUT.PUT_LINE(v_deptno || ' - ' || v_dname || ' - ' || v_loc);
INSERT INTO don
VALUES (v_deptno, UPPER(v_dname), UPPER(v_loc));
END LOOP;
UTL_FILE.FCLOSE(v_file);
COMMIT;
END;
/
show error
------------------------------------------------------------------------------------------------------------
I am getting the below errors:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/8 PL/SQL: Item ignored
3/8 PLS-00201: identifier 'UTL_FILE' must be declared
15/1 PL/SQL: Statement ignored
15/1 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
20/1 PL/SQL: Statement ignored
20/19 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
36/1 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
36/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
----------------------------------------------------------------------------------------------------
could anyone please advice me on above error.
thanx.