Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to load data from UTL file to database

866529Jun 3 2011 — edited Jun 3 2011
Hi 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 1 2011
Added on Jun 3 2011
6 comments
377 views