vsarma50
asked on
reading from textfile and load into a temp table
Hi,
In my application I have a requirement as follows. i have a textfile with the format below without spaces. I want to read the data from file delimited with position.i.e., exclude first and last row and take the data in positions. like as follows 2,9,11,8,4,12
ie., excludding first row and last row i want to insert the data into 6 columns as below.how can i write a plsql to do the same please advice. thanks very much
02 000000000 00000166950 00000000 0000 10819
110201730120110922100933TE ST0003
02000000000000006695000000 0000000108 19
02000000000000005450000000 0000000125 1579
02000000000000001497000000 0000000092 6907
02000000000000001497000000 0000000057 5062
02000000000000001497000000 0000000014 02674
12000000500000000001309500 000000000
In my application I have a requirement as follows. i have a textfile with the format below without spaces. I want to read the data from file delimited with position.i.e., exclude first and last row and take the data in positions. like as follows 2,9,11,8,4,12
ie., excludding first row and last row i want to insert the data into 6 columns as below.how can i write a plsql to do the same please advice. thanks very much
02 000000000 00000166950 00000000 0000 10819
110201730120110922100933TE
02000000000000006695000000
02000000000000005450000000
02000000000000001497000000
02000000000000001497000000
02000000000000001497000000
12000000500000000001309500
that doesn't even attempt an insert, how could it work?
try something like this...
CREATE OR REPLACE PROCEDURE crafile_to_table(loc_in IN VARCHAR2, file_in IN VARCHAR2)
IS
--loc_in := '/oracle/app/oracle/xtern_ data';
--file_in := 'CRA02-1102.txt';
names_file UTL_FILE.file_type;
l_line VARCHAR2(32767);
line_counter INTEGER := 1;
v_prev_line VARCHAR2(32767);
v_first BOOLEAN := TRUE;
BEGIN
UTL_FILE.fclose(names_file );
names_file := UTL_FILE.fopen(loc_in, file_in, 'R');
LOOP
UTL_FILE.get_line(names_fi le, l_line);
DBMS_OUTPUT.put_line(l_lin e);
line_counter := line_counter + 1;
IF v_first
THEN
v_first := FALSE;
ELSE
INSERT INTO your_table(col1,
col2,
col3,
col4,
col5,
col6
)
VALUES (SUBSTR(v_prev_line, 1, 2),
SUBSTR(v_prev_line, 3, 9),
SUBSTR(v_prev_line, 12, 11),
SUBSTR(v_prev_line, 23, 8),
SUBSTR(v_prev_line, 31, 4),
SUBSTR(v_prev_line, 35, 12)
);
v_prev_line := l_line;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose(names_file );
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line('UTL_ FILE.INVAL ID_PATH');
UTL_FILE.fclose(names_file );
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.put_line(' UTL_FILE.READ_ERROR');
UTL_FILE.fclose(names_file );
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.put_line('UTL_ FILE.WRITE _ERROR');
UTL_FILE.fclose(names_file );
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line('UTL_ FILE.INVAL ID_OPERATI ON');
UTL_FILE.fclose(names_file );
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Unha ndled Error : ' || SQLCODE);
DBMS_OUTPUT.put_line(SQLER RM);
UTL_FILE.fclose(names_file );
END;
try something like this...
CREATE OR REPLACE PROCEDURE crafile_to_table(loc_in IN VARCHAR2, file_in IN VARCHAR2)
IS
--loc_in := '/oracle/app/oracle/xtern_
--file_in := 'CRA02-1102.txt';
names_file UTL_FILE.file_type;
l_line VARCHAR2(32767);
line_counter INTEGER := 1;
v_prev_line VARCHAR2(32767);
v_first BOOLEAN := TRUE;
BEGIN
UTL_FILE.fclose(names_file
names_file := UTL_FILE.fopen(loc_in, file_in, 'R');
LOOP
UTL_FILE.get_line(names_fi
DBMS_OUTPUT.put_line(l_lin
line_counter := line_counter + 1;
IF v_first
THEN
v_first := FALSE;
ELSE
INSERT INTO your_table(col1,
col2,
col3,
col4,
col5,
col6
)
VALUES (SUBSTR(v_prev_line, 1, 2),
SUBSTR(v_prev_line, 3, 9),
SUBSTR(v_prev_line, 12, 11),
SUBSTR(v_prev_line, 23, 8),
SUBSTR(v_prev_line, 31, 4),
SUBSTR(v_prev_line, 35, 12)
);
v_prev_line := l_line;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose(names_file
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line('UTL_
UTL_FILE.fclose(names_file
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.put_line(' UTL_FILE.READ_ERROR');
UTL_FILE.fclose(names_file
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.put_line('UTL_
UTL_FILE.fclose(names_file
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line('UTL_
UTL_FILE.fclose(names_file
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Unha
DBMS_OUTPUT.put_line(SQLER
UTL_FILE.fclose(names_file
END;
ASKER
created procedure and put the file in the directory and created directory inoracle and granted all priviliges but still getting error.
begin
crafile_to_table('/oracle/ app/oracle /mv_xtern_ data','CRA 02-1102.tx t');
end;
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "CRA_USER.CRAFILE_TO_TABLE ", line 12
ORA-06512: at line 2
if I used the directory name of oracle
1 begin
2 crafile_to_table('DATA_EXT RACT','CRA 02-1102.tx t');
3* end;
begin
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "CRA_USER.CRAFILE_TO_TABLE ", line 12
ORA-06512: at line 2
begin
crafile_to_table('/oracle/
end;
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "CRA_USER.CRAFILE_TO_TABLE
ORA-06512: at line 2
if I used the directory name of oracle
1 begin
2 crafile_to_table('DATA_EXT
3* end;
begin
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "CRA_USER.CRAFILE_TO_TABLE
ORA-06512: at line 2
Consider external tables as well.
--- Note I created directory from diff user.
create directory d_tmp_exp as '/home/virdid1/work'
grant read, write on directory d_tmp_exp to TIERS1CON;
create table temp_dev_3
(
col1 varchar2(1000),
col2 varchar2(1000),
col3 varchar2(1000),
col4 varchar2(1000),
col5 varchar2(1000),
col6 varchar2(1000)
)
organization external
(
type oracle_loader
default directory sys.d_tmp_exp
ACCESS PARAMETERS ( records delimited by newline )
LOCATION ('temp.dat')
)
--- Note I created directory from diff user.
create directory d_tmp_exp as '/home/virdid1/work'
grant read, write on directory d_tmp_exp to TIERS1CON;
create table temp_dev_3
(
col1 varchar2(1000),
col2 varchar2(1000),
col3 varchar2(1000),
col4 varchar2(1000),
col5 varchar2(1000),
col6 varchar2(1000)
)
organization external
(
type oracle_loader
default directory sys.d_tmp_exp
ACCESS PARAMETERS ( records delimited by newline )
LOCATION ('temp.dat')
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CREATE OR REPLACE PROCEDURE CRAfile_to_table
(loc_in IN VARCHAR2, file_in IN VARCHAR2) IS
--loc_in := '/oracle/app/oracle/xtern_
--file_in := 'CRA02-1102.txt';
names_file UTL_FILE.FILE_TYPE;
l_line VARCHAR2 (32767);
line_counter INTEGER := 1;
BEGIN
utl_file.fclose(names_file
names_file:= UTL_FILE.FOPEN(loc_in, file_in, 'R');
LOOP
UTL_FILE.GET_LINE (names_file, l_line);
DBMS_OUTPUT.PUT_LINE(l_lin
line_counter := line_counter + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.FCLOSE (names_file);
when utl_file.invalid_path then
dbms_output.put_line('UTL_
utl_file.fclose(names_file
when utl_file.read_error then
dbms_output.put_line(' UTL_FILE.READ_ERROR');
utl_file.fclose(names_file
when utl_file.write_error then
dbms_output.put_line('UTL_
utl_file.fclose(names_file
when utl_file.invalid_operation
dbms_output.put_line('UTL_
utl_file.fclose(names_file
when others then
dbms_output.put_line('Unha
dbms_output.put_line(sqler
utl_file.fclose(names_file
END;