Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

reading from textfile and load into a temp table

Posted on 2011-09-23
5
Medium Priority
?
500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

597 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