Mladen, to say I'm disappointed in you is an understatement, putting out code that has bugs in it. do you secretly work for oracle corp? :) Joe Mladen Gogala wrote: >Jan, I am deeply shocked! I surrounded it with <PRANK> >tags and I said that it has a "small shortcoming"! >Yes, it destroys all the data in the table. That "0" >that the function returns becomes the record count. I >didn't think that somebody would actually try to destroy >his own data! I'm not worried about Jonathan or Waleed, >they're both extremely smart and started doing oracle >approximately when the dinosaurs started evolving from >lizards. But this guy is something else! > >On 02/17/2004 05:38:34 PM, Jan Pruner wrote: > > >>Looks like you posted a too much sophisticated joke, Mladen. >> >>:-) >> >> >>Mladen Gogala wrote: >> >> >> >>>Oh boy! And I was considering putting PRAGMA >>> >>> >>AUTONOMOUS_TRANSACTION; >> >> >>>into the code. DON'T RUN IT! >>> >>>On 02/17/2004 04:16:37 PM, TNguyen wrote: >>> >>> >>> >>>>the function created, but failed to run. What is wrong? >>>> >>>>----------------------------- >>>>SQL> connect scott/tiger >>>>SQL> select count(*) from emp; >>>> >>>> COUNT(*) >>>>---------- >>>> 14 >>>>SQL> >>>>SQL> create or replace >>>> 2 function count_rows(tbl in varchar2) return number >>>> >>>> >>deterministic >> >> >>>> 3 as >>>> 4 TRUNC VARCHAR2(128):='truncate table '||tbl; >>>> 5 begin >>>> 6 execute immediate trunc; >>>> 7 return(0); >>>> 8 end; >>>> 9 / >>>> >>>>Function created. >>>> >>>>SQL> >>>>SQL> select count_rows('emp') from dual; >>>>select count_rows('emp') from dual >>>> * >>>>ERROR at line 1: >>>>ORA-14552: cannot perform a DDL, commit or rollback inside a query >>>> >>>> >>or >> >> >>>>DML >>>>ORA-06512: at "SCOTT.COUNT_ROWS", line 5 >>>> >>>> >>>> >>>>-----Original Message----- >>>>From: oracle-l-bounce@xxxxxxxxxxxxx >>>>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala >>>>Sent: Tuesday, February 17, 2004 4:15 PM >>>>To: oracle-l@xxxxxxxxxxxxx >>>>Subject: Re: Counting number of rows >>>> >>>> >>>>The function below would count rows extremely quickly, and is >>>>independent of the table size. Even better, it is completely >>>> >>>> >>accurate >> >> >>>>and doesn't rely on the underlying statistics. The table >>>>in question may not even be analyzed. It does have one minor >>>>shortcoming, however. >>>> >>>><PRANK> >>>>create or replace >>>>function count_rows(tbl in varchar2) return number deterministic >>>>as >>>>TRUNC VARCHAR2(128):='truncate table '||tbl; >>>>begin >>>>execute immediate trunc; >>>>return(0); >>>>end; >>>>/ >>>></PRANK> >>>>On 02/17/2004 03:27:31 PM, "Potluri, Venu (CT Appl Suppt)" wrote: >>>> >>>> >>>> >>>>>Is there a quick way to count number of rows in a table? Don't >>>>> >>>>> >>want >> >> >>>>>to >>>>>do select count(*) from..... on a table (such as GL_BALANCES) with >>>>>more than 250 million rows. >>>>> >>>>> >>---------------------------------------------------------------- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>---------------------------------------------------------------- >>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx >>put 'unsubscribe' in the subject line. >>-- >>Archives are at //www.freelists.org/archives/oracle-l/ >>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >>----------------------------------------------------------------- >> >> >> > > > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------