[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

Difference in record count, using collection%rowcount, and the numbering against records in vi editor.

I have the following procedure to write to a file.
I am checking the number of records written to a file.
This record count got through tbl_out_str.COUNT or ref_cursor%rowcount differs from what the unix vi editor shows me.
The difference is as much as 500 records, why?
0
gram77
Asked:
gram77
  • 7
  • 7
  • 2
  • +1
1 Solution
 
gram77Author Commented:
last few records shown using vi.
184530 20100830|xxxxx||yyyyy
184531 20100830|xxxxx||yyyyy
184532 20100830|xxxxx||yyyyy
184533 records written to file: 184163 or 184163  <--vi shows 184532 records, while oracle shows me 184163 records written)



create or replace procedure write_file
IS
TYPE my_typ IS TABLE OF VARCHAR2 (3900);
tbl_out_str    my_typ;
v_rec_count    NUMBER:= 0;
BEGIN
OPEN ref_cursor FOR v_sql_stmt;
LOOP
    FETCH ref_cursor
    BULK COLLECT INTO tbl_out_str LIMIT 1000;

          FOR i IN 1 .. tbl_out_str.COUNT
          LOOP
             BEGIN
                    UTL_FILE.put_line (f_ext, tbl_out_str (i));
              END;
         END LOOP;

         v_rec_count := v_rec_count + tbl_out_str.COUNT;
         UTL_FILE.put_line (f_ext, 'records written to file: '||v_rec_count||' or '||ref_cursor%rowcount);                    

  EXIT WHEN ref_cursor%NOTFOUND;
END LOOP;
END write_file;
0
 
gram77Author Commented:
line        col1          col2    col3
184530 20100830|xxxxx||yyyyy
184531 20100830|xxxxx||yyyyy
184532 20100830|xxxxx||yyyyy <--vi shows 184532 records
184533 records written to file: 184163 or 184163  <--oracle shows me 184163 records written
0
 
Amitkumar PSr. ConsultantCommented:
gram77,

Following details are not found in the code provided by you.

1. v_sql_stmt : What is the query?
2. f_ext : Where this file handle is defined? In which mode it is opened?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
you are printing a count for every batch of 1000 rows you pull from the cursor

so, you're getting extra "records written to file"  lines which skews your results


here's a smaller example to help illustrate (collecting in batches of 2 rows)
DECLARE
    TYPE my_typ IS TABLE OF VARCHAR2(3900);

    ref_cursor    SYS_REFCURSOR;

    tbl_out_str   my_typ;
    v_rec_count   NUMBER := 0;
    v_sql_stmt    VARCHAR2(10000) := 'select object_name || ''|'' || object_type from user_objects';
BEGIN
    OPEN ref_cursor FOR v_sql_stmt;

    LOOP
        FETCH ref_cursor
        BULK COLLECT INTO tbl_out_str
        LIMIT 2;

        FOR i IN 1 .. tbl_out_str.COUNT
        LOOP
            BEGIN
                DBMS_OUTPUT.put_line(tbl_out_str(i));               
            END;
        END LOOP;

        v_rec_count  := v_rec_count + tbl_out_str.COUNT;
        DBMS_OUTPUT.put_line(
            'records written to file: ' || v_rec_count || ' or ' || ref_cursor%ROWCOUNT);

        EXIT WHEN ref_cursor%NOTFOUND;
    END LOOP;
END write_file;

Open in new window

0
 
sdstuberCommented:
using the block above I get 494 lines of output with the last line being

records written to file: 329 or 329

which is correct, because every 3rd line is the summary.

put the summary line outside the loop
0
 
Walter RitzelSenior Software EngineerCommented:
This would be your procedure slightly changed to fix this:
CREATE OR REPLACE PROCEDURE write_file
IS
   TYPE my_typ IS TABLE OF VARCHAR2 (3900);
   tbl_out_str   my_typ;
   v_rec_count   NUMBER := 0;
BEGIN
   v_rec_count := 0;
   OPEN ref_cursor FOR v_sql_stmt;

   LOOP
      FETCH ref_cursor
      BULK COLLECT INTO tbl_out_str
      LIMIT 1000;

      FOR i IN 1 .. tbl_out_str.COUNT
      LOOP
         BEGIN
            UTL_FILE.put_line (f_ext, tbl_out_str (i));
            v_rec_count := v_rec_count + 1;
         END;
      END LOOP;
      EXIT WHEN ref_cursor%NOTFOUND;
   END LOOP;
   UTL_FILE.put_line (f_ext, 'records written to file: ' || v_rec_count || ' or ' || ref_cursor%ROWCOUNT);
END write_file;
0
 
sdstuberCommented:
>>>  END LOOP;
>>>   UTL_FILE.put_line (f_ext, 'records written to file: ' || v_rec_count || ' or ' >>> || ref_cursor%ROWCOUNT);


yep, that's what I said

put the summary line outside the loop
0
 
gram77Author Commented:
I have tried putting the summary line outside END LOOP; still both counts show same result which differ from vi results.


create or replace procedure write_file
IS
TYPE my_typ IS TABLE OF VARCHAR2 (3900);
tbl_out_str    my_typ;
v_rec_count    NUMBER:= 0;
BEGIN
OPEN ref_cursor FOR v_sql_stmt;
LOOP
    FETCH ref_cursor
    BULK COLLECT INTO tbl_out_str LIMIT 1000;

          FOR i IN 1 .. tbl_out_str.COUNT
          LOOP
             BEGIN
                    UTL_FILE.put_line (f_ext, tbl_out_str (i));
              END;
         END LOOP;

         v_rec_count := v_rec_count + tbl_out_str.COUNT;
         UTL_FILE.put_line (f_ext, 'records written to file: '||v_rec_count);                    

  EXIT WHEN ref_cursor%NOTFOUND;
END LOOP;
         UTL_FILE.put_line (f_ext, 'records written to file: '||ref_cursor%rowcount);                    
END write_file;
0
 
Walter RitzelSenior Software EngineerCommented:
Pleasu use my version of the procedure of my previous post.
0
 
gram77Author Commented:
wpcortes:
LOOP
         BEGIN
            UTL_FILE.put_line (f_ext, tbl_out_str (i));
            v_rec_count := v_rec_count + 1;
         END;
      END LOOP;

gives same result as
..
..
END LOOP;
         UTL_FILE.put_line (f_ext, 'records written to file: '||ref_cursor%rowcount);                    
END write_file;
0
 
sdstuberCommented:
the procedures above won't compile but here is a working anonymous block of the same form as what I had above
but with the summary line moved outside the loop


if your procedure doesn't work, please post an example of what it does or does not do correctly
DECLARE
    TYPE my_typ IS TABLE OF VARCHAR2(3900);

    ref_cursor    SYS_REFCURSOR;

    tbl_out_str   my_typ;
    v_rec_count   NUMBER := 0;
    v_sql_stmt    VARCHAR2(10000) := 'select object_name || ''|'' || object_type from user_objects';
BEGIN
    OPEN ref_cursor FOR v_sql_stmt;

    LOOP
        FETCH ref_cursor
        BULK COLLECT INTO tbl_out_str
        LIMIT 2;

        FOR i IN 1 .. tbl_out_str.COUNT
        LOOP
            BEGIN
                DBMS_OUTPUT.put_line(tbl_out_str(i));               
            END;
        END LOOP;

        v_rec_count  := v_rec_count + tbl_out_str.COUNT;
       

        EXIT WHEN ref_cursor%NOTFOUND;
    END LOOP;
     DBMS_OUTPUT.put_line(
            'records written to file: ' || v_rec_count || ' or ' || ref_cursor%ROWCOUNT);
END write_file;

Open in new window

0
 
sdstuberCommented:
simple replace the dbms_output  calls with utl_file calls
0
 
gram77Author Commented:

BEGIN
LOOP
         BEGIN
            UTL_FILE.put_line (f_ext, tbl_out_str (i));
            v_rec_count := v_rec_count + 1;
         END;
      END LOOP;

         UTL_FILE.put_line (f_ext, 'records written to file: '||v_rec_count);  <-this gives same result as below                  
END LOOP;

         UTL_FILE.put_line (f_ext, 'records written to file: '||ref_cursor%rowcount);   <-this gives same result as above                  
END;
0
 
gram77Author Commented:
sdstuber:
all code works. just that i tried having record count first ouside the first loop then outside the second loop, and both give the same output.
which differs from the recordcount of unix

BEGIN
LOOP
         BEGIN
            UTL_FILE.put_line (f_ext, tbl_out_str (i));
            v_rec_count := v_rec_count + 1;
         END;
      END LOOP;

         UTL_FILE.put_line (f_ext, 'records written to file: '||v_rec_count);  <-this gives same result as below                  
END LOOP;

         UTL_FILE.put_line (f_ext, 'records written to file: '||ref_cursor%rowcount);   <-this gives same result as above                  
END;
0
 
sdstuberCommented:
can you post a working example that produces the error?
0
 
gram77Author Commented:
sdstuber:
the extract given above is taken from the proc.
0
 
sdstuberCommented:
yes, but your extract won't compile, and doesn't even have a query we can test or examine.

Can you post an example that we can test, as I did?

if the example code I've provided doesn't replicate your needs well enough, alter it (but make sure it will still run) and repost it.

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now