I have a table TEST_TABLE in DB and I want to insert some values in it using a stored procedure. How do i print the number of rows that are inserted in TEST_TABLE after the SP has completed ??...I want to do this using UTL_FILE package and want a text file to be generated to monitor the data load.
This is an example based on HR schema - I'm inserting some rows from DEPARTMENTS table into DEPT_TEST. Number of rows being inserted can be retrieved through SQL%ROWCOUNT which returns number of rows processed by the last DML statement (the last executed before you call SQL%ROWCOUNT).
The procedure itself is kind of stupid - it "simulates" what I think yours does - inserts some rows, then inserts some more, etc. UTL_FILE usage is understandable, I suppose (gee, what would FOPEN or FWRITE do?). I'm writing values into TEST.TXT located in my EXT_DIR directory (which is, actually, C:\TEMP).
Note that - if you don't already have it - you should first create directory (connected as SYS) and grant READ/WRITE privileges to user which is going to use it (HR in my case).
OK then, here we go:
SQL> show user
USER is "SYS"
SQL> create directory ext_dir as 'c:\temp';
Directory created.
SQL> grant read, write on directory ext_dir to hr;
Grant succeeded.
SQL> connect hr/hr@xe
Connected.
SQL> create table dept_test as select * From departments where 1 = 2;
Table created.
SQL> declare
2 l_file utl_file.file_type;
3 begin
4 l_file := utl_file.fopen('EXT_DIR', 'test.txt', 'w');
5
6 insert into dept_test (department_id, department_name, manager_id, location_id)
7 select department_id, department_name, manager_id, location_id
8 from departments
9 where department_id between 0 and 150;
10 utl_file.put_line(l_file, to_char(sysdate, 'hh24:mi:ss') ||
11 ': Inserted ' || sql%rowcount || ' rows');
12
13 insert into dept_test (department_id, department_name, manager_id, location_id)
14 select department_id, department_name, manager_id, location_id
15 from departments
16 where department_id between 151 and 300;
17 utl_file.put_line(l_file, to_char(sysdate, 'hh24:mi:ss') ||
18 ': Inserted ' || sql%rowcount || ' rows');
19
20 utl_file.fclose(l_file);
21 end;
22 /
PL/SQL procedure successfully completed.
SQL> $type c:\temp\test.txt
12:32:51: Inserted 15 rows
12:32:51: Inserted 12 rows
SQL>
However, if I may, I'd suggest another approach - logging into a database. In order to do that, you need a log table and a procedure which utilizes autonomous_transaction pragma (so that you could COMMIT inserts, without affecting transaction performed by a caller).
SQL> CREATE TABLE a1_log
2 (
3 id NUMBER,
4 datum DATE,
5 desc VARCHAR2 (200)
6 );
Table created.
SQL> CREATE SEQUENCE seqlog START WITH 1 INCREMENT BY 1;
Sequence created.
SQL> CREATE OR REPLACE PROCEDURE a1_p_log (par_description IN VARCHAR2)
2 IS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 INSERT INTO a1_log
6 SELECT seqlog.NEXTVAL, SYSDATE, par_description FROM DUAL;
7
8 COMMIT;
9 END a1_p_log;
10 /
Procedure created.
Now, the initial "inserting" procedure would look like this:
SQL> begin
2 insert into dept_test (department_id, department_name, manager_id, location_id)
3 select department_id, department_name, manager_id, location_id
4 from departments
5 where department_id between 0 and 150;
6 a1_p_log('Inserted ' || sql%rowcount || ' rows');
7
8 insert into dept_test (department_id, department_name, manager_id, location_id)
9 select department_id, department_name, manager_id, location_id
10 from departments
11 where department_id between 151 and 300;
12 a1_p_log('Inserted ' || sql%rowcount || ' rows');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> col description format a40
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select * From a1_log;
ID DATUM DESCRIPTION
---------- -------- ----------------------------------------
1 12:52:08 Inserted 15 rows
2 12:52:08 Inserted 12 rows
SQL>
In my opinion, this is a simpler option and I'd suggest you to use it instead of the UTL_FILE option, but - at the end, it's up to you.