Home » SQL & PL/SQL » SQL & PL/SQL » getting different COUNT with insert and dbms_output (Windows , Oracle 9.2.0.1.0)
getting different COUNT with insert and dbms_output [message #294499] Thu, 17 January 2008 16:26 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hi guys,

I have a problem with following insert and DBMS_OUTPUT.

Please help.

The Problem : When I put my DBMS_OUTPUT.PUT_LINE(l_array.COUNT);
after process my data it give me 692,088 records,
but when I insert into a table via this insert statement
it insert only 692,065 , 23 records are missing from the count.

or I try to print on a file with the folloing DBMS_OUTPUT ( pl ) also I
get only 692,065 , 23 records are missing from the count.

Any idea what am I missing here?

This just an extraction of main program.


...............
   


   DBMS_OUTPUT.PUT_LINE(l_array.COUNT); <----at this point count gives 692,088 
                  
  INSERT INTO ADADANI.PHARM_DATA_Q3b@ODSDEV (   <----here it insert only 692,065
	    PLANID, CLAIM_REF_NUMBER, NDC_NUMBER, 
	    DOS, MEDICAID_NO, PROCDATE, 
	    PRODUCT, PLAN_CODE, SEQ_MEMB_ID, 
	    FUNDING_COUNTY) 
	 VALUES (rec.planid ,l_array (l_row).claim_ref_number ,l_array (l_row).ndc_number ,
	     l_array (l_row).dos,l_array (l_row).medicaid_no ,l_array (l_row).procdate,
          rec.product ,l_array (l_row).plan_code,l_array (l_row).seq_memb_id,l_array (l_row).funding_county );

         pl (   RPAD (rec.planid, 9, ' ')  <----here it print  only 692,065   

             || ' '
             || RPAD (' ', 13, ' ')
             || ' '
             || RPAD (l_array (l_row).claim_ref_number, 24, ' ')
             || ' '
             || RPAD (l_array (l_row).ndc_number, 11, ' ')
             || ' '
             || RPAD (' ', 6, ' ')
             || ' '
             || RPAD (l_array (l_row).dos, 8, ' ')
             || RPAD (l_array (l_row).medicaid_no, 10, ' ')
             || ' '
             || RPAD (' ', 8, ' ')
             || ' '
             || RPAD (l_array (l_row).procdate, 8, ' ')
            );

      END IF;

    
      l_row := l_array.NEXT (l_row);
   END LOOP;
   CLOSE lkp_county_cur;

   COMMIT;



Thanking you for all you help in advance.


[Updated on: Thu, 17 January 2008 21:11]

Report message to a moderator

Re: getting different COUNT with insert and dbms_output [message #294549 is a reply to message #294499] Thu, 17 January 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the actual code removing all not necessary part but a code that can work.
With what you posted we can't say anything.

In addition, why don't you use a bulk insert (FORALL INSERT) as you have the data in a table?

Regards
Michel
Re: getting different COUNT with insert and dbms_output [message #295159 is a reply to message #294549] Mon, 21 January 2008 08:49 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hi Michel,

Thanks for you reply.

I am not using insert so I don't need bulk collect here, this insert is just test purpose only. Here is the actual code; I have tried to shrink it as much as possible.


SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET LINE 104
SET VERIFY OFF
SET PAGESIZE 0
SET FEEDBACK OFF


col filename new_value filename

select 'RX_'||TO_CHAR(SYSDATE,'MMDDYYYY')||'_Q'||TO_CHAR(ADD_MONTHS(SYSDATE, -3),'Q')||'.txt' filename from dual;


spool  &&filename



DECLARE
   TYPE rec_type IS RECORD (
      id                         VARCHAR2 (10 BYTE)
    , ref_number               NUMBER   (14)
    , ndc_number                     VARCHAR2 (11 BYTE)
    , dos                            VARCHAR2 (8  BYTE)
    , caid_no                    VARCHAR2 (16 BYTE)
    , procdate                       VARCHAR2 (8  BYTE)
    , product                        VARCHAR2 (3  BYTE)
    , code                      VARCHAR2 (10 BYTE)
    , memb_id                    NUMBER   (9)
    , funding                 VARCHAR2 (260 BYTE)
   );

   rec                      rec_type;

   TYPE data_aat IS TABLE OF rec_type INDEX BY PLS_INTEGER;

   l_array                 data_aat;
   
   l_row                    PLS_INTEGER;
   s_date                   VARCHAR2 (100);
   e_date                   VARCHAR2 (100);

   CURSOR lkp_cur (id_in IN VARCHAR2, funding_in IN VARCHAR2)
   IS
      SELECT *
        FROM  lookup
       WHERE id = id_in AND funding = funding_in;

   rec_county               lookup%ROWTYPE;

   FUNCTION get_data (s_date IN DATE, e_date IN DATE)
      RETURN data_aat
   IS
      retval               data_aat;
   BEGIN
      EXECUTE IMMEDIATE '      SELECT DISTINCT * FROM ANYTABLE '
      BULK COLLECT INTO retval USING s_date, e_date;

      RETURN retval;
   END get_pharm_data;
 
BEGIN


SELECT TRUNC(ADD_MONTHS (bill_date, -3),'Q') start_prev_qtr
     , TRUNC (bill_date, 'Q') - 1            end_prev_qtr
  INTO s_date , e_date   
FROM (SELECT ADD_MONTHS (SYSDATE,  ROWNUM - 1  ) bill_date
               FROM DUAL CONNECT BY ROWNUM <= 1);

   l_array := get_data (s_date, e_date);
   l_row := l_array.FIRST;

   WHILE (l_row IS NOT NULL)
   LOOP
      OPEN lkp_cur (l_array (l_row).id
                         , l_array (l_row).funding
                          );

      FETCH lkp_cur
       INTO rec_county;

      IF     lkp_county_cur%FOUND
         AND rec_county.funding = l_array (l_row).funding
      THEN
         CASE 
          WHEN 
                      --- DOING LOGIC PROCESS TO UPDATE REC....................
         END CASE;
    
     DBMS_OUTPUT.PUT_LINE(l_array.COUNT);    <----at this point count gives 692,088 


     INSERT INTO anytable values (......      <----here it insert only 692,065



             pl (   RPAD (rec.id, 9, ' ')     <----here it print  only 692,065   
       	                    || ' '
       	                    || RPAD (' ', 13, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).ref_number, 24, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).ndc, 11, ' ')
       	                    || ' '
       	                    || RPAD (' ', 6, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).dos, 8, ' ')
       	                    || RPAD (l_array (l_row).caid_no, 10, ' ')
       	                    || ' '
       	                    || RPAD (' ', 8, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).procdate, 8, ' ')
       	                   );


      END IF;

      CLOSE lkp_county_cur;

      l_row := l_array.NEXT (l_row);
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
     THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/


SPOOL OFF




Thanking you for your help.

Re: getting different COUNT with insert and dbms_output [message #295197 is a reply to message #295159] Mon, 21 January 2008 12:10 Go to previous message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Okay Guys I have found the solutions;

I have just moved my print command out of END IF:


      END IF;


pl (   RPAD (rec.id, 9, ' ')     <----here it print  only 692,065   
       	                    || ' '
       	                    || RPAD (' ', 13, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).ref_number, 24, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).ndc, 11, ' ')
       	                    || ' '
       	                    || RPAD (' ', 6, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).dos, 8, ' ')
       	                    || RPAD (l_array (l_row).caid_no, 10, ' ')
       	                    || ' '
       	                    || RPAD (' ', 8, ' ')
       	                    || ' '
       	                    || RPAD (l_array (l_row).procdate, 8, ' ')
       	                   );



I am getting expected row numbers.

Thanking you for all your support.

Previous Topic: dbms_aq-package state invalidated
Next Topic: Searching by dates
Goto Forum:
  


Current Time: Sat Dec 10 09:14:04 CST 2016

Total time taken to generate the page: 0.04495 seconds