in reply to Handling conditions DBI in Oracle 8.1

Another way of doing this is getting the resultset count before firing the required query i.e.
firing a select count(*) ROWCOUNT from table where id = 1 If ROWCOUNT is 0 , then u dont need to fire the second SQL (which is  select * from table where id = 1) u need to fire the second SQL only if the first SQL returns a value greater than 0 )
For example, 
my $sql = <<EOF;
    select count(*) ROWCOUNT from EMPLOYEE where employee_id = 1
EOF
    ;
my $sth = $dbh->prepare($sql);
$sth->execute;
my $employee_count_row = $sth->fetchrow_hashref;
$sth->finish;

print "NO RECORDS FOUND" unless $auth_row->{'ROWCOUNT'} > 0;
 ...fire the second SQL if ROWCOUNT > 0

[download]
Hope this helps :)

Replies are listed 'Best First'.
Re: Re: Handling conditions DBI in Oracle 8.1
by data67 (Monk) on Jan 16, 2002 at 00:10 UTC
    You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count. For you to get an accurate count you will have to ANALYZE. Oracle is little different in this than let say MySQL.
    And then there is a matter where Oracle recommends that you use the DBMS_STATS package rather than ANALYZE for most statistics collection.
[reply]
[d/l]
[select]
      You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count.

      If I get the wrong count, I'll submit a bug report to Oracle (is there any documentation on this? I don't see anything to that effect under the docs for 'COUNT'). ANALYZE generates statistics for query plans, and shouldn't affect the results of a statement like 'select count(*) ...'.

[reply]
      You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count. For you to get an accurate count you will have to ANALYZE.

      I am 100% sure this is not true.

      ANALYZE collect statistics for the cost based optimizer and is used to improve (hopefully) the speed at which a query completes. It in no way impacts the results of the query... only the speed.

      Update:

      Just to confirm the above, I threw together a sample SQL script. Its output is below.

      Update #2:

      Now I think I know about what you are thinking -- the column 'num_rows' in the 'dba_tables' view is not populated until you analyze the table. This is not the same thing as doing a COUNT(*), but is similar. 

      ---------------------------
----- Test SQL Script -----
---------------------------

  1  SELECT version
  2*   FROM v$instance

VERSION
-----------------
8.1.7.2.0


  1* DROP TABLE oracle.bobtest
DROP TABLE oracle.bobtest
                  *
ERROR at line 1:
ORA-00942: table or view does not exist 


  1  CREATE TABLE oracle.bobtest
  2*   ( junk VARCHAR(1) )

Table created.


  1  INSERT INTO oracle.bobtest
  2*   VALUES ( 'A' )

1 row created.

1 row created.

1 row created.


  1* COMMIT

Commit complete.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
---------- 
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   COMPUTE STATISTICS

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   DELETE STATISTICS

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   ESTIMATE STATISTICS SAMPLE 20 PERCENT

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1* DROP TABLE oracle.bobtest

Table dropped.

      [download]
[reply]
[d/l]
        I have been enlightened, thanks guys for the input. I guess i need to read up more on Oracle.

        Data     ;-}

[reply]

In Section Seekers of Perl Wisdom