?
Solved

reading from textfile and load into a temp table

Posted on 2011-09-23
5
Medium Priority
?
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 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