CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Nick
Nick

Reputation: 127

how to get the 2nd row and the last row of a file using UTL_FILE

I have a file which consists of thousand rows and I need to get a portion of the 2nd row (about 50 characters) and the last row of the file. Please advise. Thank you.

Im trying to do something like UTL_FILE.READLINE(fileloc, filename, nrow, lastrow).

SAMPLE: Filename: CLOSED_SO_20190124.txt

DATA in the FILE:
    0246608377|22795124004|
    650930363|1-8IGO3S82920|
    0245563264|22669075004|
    0245563264|22669075004|
    164260364|1-2DFE-6573219|
    650821459|1-6HWQUF11209|

EXPECTED OUTPUT:

    650930363|1-8IGO3S82920|
    650821459|1-6HWQUF11209|

Upvotes: 1

Views: 1251

Answers (2)

Littlefoot
Littlefoot

Reputation: 143063

Here's an example.

Directory name & its location, as well as sample file contents:

SQL> select directory_name, directory_path from all_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------
EXT_DIR                        c:\temp

SQL> $type c:\temp\sofile.txt
0246608377|22795124004|
650930363|1-8IGO3S82920|
0245563264|22669075004|
0245563264|22669075004|
164260364|1-2DFE-6573219|
650821459|1-6HWQUF11209|
SQL>

The procedure: a local counter (l_cnt) knows line number; if it is equal to 2, display that line. Also, when nothing's being found (so exception handler section is executed), I've reached the end of the file so I'm displaying the last line as well.

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_file utl_file.file_type;
  3    l_dir  varchar2(20) := 'EXT_DIR';
  4    l_name varchar2(20) := 'sofile.txt';
  5    l_line varchar2(50);
  6    l_cnt  number := 0;
  7  begin
  8    l_file := utl_file.fopen (l_dir, l_name, 'R');
  9    loop
 10      begin
 11        utl_file.get_line(l_file, l_line);
 12        l_cnt := l_cnt + 1;
 13        if l_cnt = 2 then
 14           dbms_output.put_line('2nd : ' || l_line);
 15        end if;
 16      exception
 17        when no_data_found then
 18          dbms_output.put_line('last: ' || l_line);
 19          exit;
 20      end;
 21    end loop;
 22    utl_file.fclose(l_file);
 23  end;
 24  /
2nd : 650930363|1-8IGO3S82920|
last: 650821459|1-6HWQUF11209|

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30655

In Oracle 11g

select col 
from 
(
  select 
    rownum AS rnum, 
    LEFT(myCol, 50) col
  from Table1
  where Rownum < 3
)
WHERE rnum = 2

In Oracle 12c

select LEFT(mycol, 50) col
from Table1
ORDER BY val
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

For the last row

select LEFT(mycol, 50) col
from my_table
where pk = ( select max(pk) from my_table )

then you can union them

Upvotes: 0

Related Questions