-
HELP-Load Text File into Oracle Table - PL/SQL Script
It seems simple enough, but it's not working for me. If anyone can help with the syntax to make this work, i'd really appreciate it:
File(s):
"DataExtract-20051231.txt"
"DataExtract-20051222.txt"
"DataExtract-20051208.txt"
Contents of a file could be:
34 John A.Smith jasmith Engineer
23 Jim P.Johnson jpjohnson Developer
43 Bob D.User bduser King
Lets say
The First 4 characters are: Age
Next 5: First name
Next 2: Middle Initial
Next 8: Last name
Next 10: Username
Last 9: Title.
I have a table... my_table with the fields:
AGE
F_NAME
M_NAME
L_NAME
U_NAME
TITLE
How do I put the data from all of these data files into the table using a PL/SQL script? Any ideas?
Thank you!
-
-
How do I put the data from all of these data files into the table using a PL/SQL script? Any ideas?
Where is your script?
Tamil
-
This is the only READ_FILE procedure that compiled.
CREATE OR REPLACE PROCEDURE READ_FILE
(PI_DIRECTORY IN VARCHAR2,
PI_FILE_NAME IN VARCHAR2)
AS
V_File_handle UTL_FILE.FILE_TYPE;
V_FILE_Line VARCHAR2(906);
BEGIN
V_File_handle :=
UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'R');
LOOP
UTL_FILE.GET_LINE( V_File_handle , v_file_line);
DBMS_OUTPUT.PUT_LINE(v_file_line);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN UTL_FILE.FCLOSE( V_File_handle );
END;
/
-
This link was related... :
http://www.dbasupport.com/oracle/ora9i/data_obj2.shtml
I'm going to try to post the important tidbits here...
Last edited by bullcrap420; 01-10-2006 at 05:52 PM.
-
Apparently, here is how to create a directory and set the permissions:
In order to create a directory you, (the database user), should have the following privileges:
CREATE ANY DIRECTORY.
CREATE OR REPLACE DIRECTORY test_files AS ‘E:\oracleWork’;
By default, you do get the READ WRITE privileges on this object. However, if you wish to assign a READ WRITE privilege to another user you can GRANT the necessary privileges as follows:
GRANT READ ON DIRECTORY test_files TO PUBLIC;
-
This is how to use a view to see the data from the file, but not load it:
Consider a data file "emp_load.dat"
This file emp_load.dat should be located in the physical directory "E:\oracleWork" identified by the directory object TEST_FILES.
The DDL to create the external table will be as follows:
CREATE TABLE emp_external
( emp_id NUMBER(4)
, ename VARCHAR2(12)
, job VARCHAR2(12)
, mgr_id NUMBER(4)
, hiredate DATE
, salary NUMBER(8)
, comm NUMBER(8)
, dept_id NUMBER(2))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TEST_FILES
ACCESS PARAMETERS (records delimited BY newline
fields terminated BY ',')
LOCATION ('emp_load.dat')
);
To view the data, all you have to do is query the table like a regular table viz:
SELECT * FROM emp_external;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|