Solved

reading from textfile and load into a temp table

Posted on 2011-09-23
5
489 Views
Last Modified: 2012-08-14
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                                            
0
Comment
Question by:vsarma50
  • 2
  • 2
5 Comments
 

Author Comment

by:vsarma50
ID: 36586567
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 36586712
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
 

Author Comment

by:vsarma50
ID: 36586947
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36587571
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
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 36587636
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now