title: Oracle INSERT ... SELECT inserts 0 rows, but the select does return more than 0 rows
body: <p>I have an Insert ... Select statement to insert some rows into a table in a PL/SQL procedure. At the end of the statement, I use SQL%ROWCOUNT to capture how many rows have been inserted, and put it into a log table.</p>
  
  <p>I have a case here, where the log showed 0 row has been inserted and I have verified in the target table as well that nothing has been inserted, but when I ran the select statement, it did return more than 0 rows.</p>
  
  <p>here is the code,</p>
  
  <pre><code>INSERT INTO J2_TRACK_PRICES 
  
  (pgr_price_group_no,pgr_dep_dept_no,
                           track_no,
                           track_colour,
                           cost_price,
                           retail_price,
                          selling_price,
                            buying_price,
                           ticket_retail_price,
                                       ticket_selling_price,
                                       cost_gst_rate,
                                       retail_gst_rate)
  
             SELECT DISTINCT
                    pgr.price_group_no,
                    pgr.dep_dept_no,
                    bpd.track_no,
                    bpd.track_colour,
                    bpd.cost_price,
                    bpd.cost_price,
                    bpd.retail_price,
                    bpd.retail_price
  
  FROM B2_TMP_PURCHASE_ORDER_DET bpd, J2_PRICE_GROUPS pgr
  
              WHERE     bpd.bph_po_no = dbt_po_no
                    AND pgr.dep_dept_no = dbt_dept_no
                    AND bpd.line_no IN (SELECT MIN (bpd2.line_no)
                                          FROM B2_TMP_PURCHASE_ORDER_DET bpd2
                                         WHERE bpd2.bph_po_no = bpd.bph_po_no
                                        GROUP BY bpd2.track_no, bpd2.track_colour)
                    AND NOT EXISTS
                           (SELECT 1
                              FROM J2_TRACK_PRICES trp
                             WHERE     trp.pgr_price_group_no = pgr.price_group_no
                                   AND trp.pgr_dep_dept_no = pgr.dep_dept_no
                                   AND trp.track_no = bpd.track_no
                                   AND trp.track_colour = bpd.track_colour);
  </code></pre>
  
  <p>Have you guys encountered this before?</p>
  
  <p>Thanks a lot</p>