Number of rows inserted/updated in utl_file

Question

How do i print number of rows in utl_file.

1. If i am using dbms_output.put_line('Total record'||'|'||SQL%ROWCOUNT);

and

2. If i am ​using dbms_output.put_line('Total record'||'|'||To_char(SQL%ROWCOUNT));

Compiler saying wrong argument is passed. nothing is reflecting

and

3. utl_file.put(file_handele,'total roecord' ||'|'|| SQL%ROWCOUNT);

only total record is reflecting.

please help new to psql

Answer 1

What do you call "number of rows in utl_file"?

---

You said that 1st and 2nd statements are invalid because compiler is complaining. It is not for me, so I kind of doubt what you claim:

SQL> begin
  2    -- your 1st statement:
  3    dbms_output.put_line('Total record'||'|'||SQL%ROWCOUNT);
  4
  5    -- your 2nd statement:
  6    dbms_output.put_line('Total record'||'|'||To_char(SQL%ROWCOUNT));
  7  end;
  8  /
Total record|
Total record|

PL/SQL procedure successfully completed.

As of the 3rd statement:

SQL> declare
  2    file_handele  utl_file.file_type;
  3  begin
  4    file_handele := utl_file.fopen('EXT_DIR', 'test.txt', 'w');
  5
  6    -- your 3rd statement, literally, with typos:
  7    utl_file.put(file_handele,'total roecord' ||'|'|| SQL%ROWCOUNT);
  8
  9    utl_file.fclose(file_handele);
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> $type c:\temp\test.txt
total roecord|

SQL>

---

All 3 statements are OK (i.e. they don't fail, they don't raise an error), but the question is: which problem are you trying to solve? Display number of rows written into a file using UTL_FILE package? If so, you should have posted that piece of code as well.

Anyway: one option is to literally count them. For example:

SQL> declare
  2    i             number := 0;      -- this is the counter
  3    file_handele  utl_file.file_type;
  4  begin
  5    file_handele := utl_file.fopen('EXT_DIR', 'test.txt', 'w');
  6
  7    for cur_r in (select dname from dept) loop
  8      utl_file.put(file_handele, cur_r.dname || utl_tcp.crlf);
  9      i := i + 1;      -- increment the counter
 10    end loop;
 11
 12    -- your 3rd statement, literally, with typos:
 13    utl_file.put(file_handele,'total roecord' ||'|'|| i);
 14
 15    utl_file.fclose(file_handele);
 16  end;
 17  /

PL/SQL procedure successfully completed.

The result:

SQL> $type c:\temp\test.txt
ACCOUNTING
RESEARCH
SALES
OPERATIONS
total roecord|4         --> here's the total number of lines written into the file

SQL>