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                                      

110201730120110922100933TEST0003                                                
02000000000000006695000000000000010819                                      
0200000000000000545000000000000001251579
0200000000000000149700000000000000926907                                    
0200000000000000149700000000000000575062                                    
02000000000000001497000000000000001402674                                
12000000500000000001309500000000000                                            
vsarma50Asked:
Who is Participating?
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
I am assuming that your first and lat lines are similar i.e.
02  000000000  00000166950   00000000  0000  10819          

Therefore you can use the following select statement

select substr(col1,1,2), substr(col1, 3,2)  from temp_dev_3 where col2 is null and col1 is not null;
0
 
vsarma50Author Commented:
I wrote the procedure as follows but not working


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;
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_line);
                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_FILE.INVALID_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.INVALID_OPERATION');
      utl_file.fclose(names_file);
   when others then
      dbms_output.put_line('Unhandled Error : '||sqlcode);
      dbms_output.put_line(sqlerrm);
      utl_file.fclose(names_file);
END;
0
 
sdstuberCommented:
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_file, l_line);
        DBMS_OUTPUT.put_line(l_line);
        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.INVALID_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.INVALID_OPERATION');
        UTL_FILE.fclose(names_file);
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Unhandled Error : ' || SQLCODE);
        DBMS_OUTPUT.put_line(SQLERRM);
        UTL_FILE.fclose(names_file);
END;
0
 
vsarma50Author Commented:
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','CRA02-1102.txt');
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_EXTRACT','CRA02-1102.txt');
  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
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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')
)
0
All Courses

From novice to tech pro — start learning today.