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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
> Sent: Tuesday, February 17, 2004 4:15 PM
> To: oracle-l_at_freelists.org
> 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.
> > --------------------------------------------------------
> >
> > If you are not an intended recipient of this e-mail, please notify
> > the
> > sender, delete it and do not read, act upon, print, disclose, copy,
> > retain or redistribute it. Click here for important additional
> terms
> > relating to this e-mail. http://www.ml.com/email_terms/
> > --------------------------------------------------------
> >
> >
> ============================================================================
> ==
> >
> > If you are not an intended recipient of this e-mail, please notify
> > the sender, delete it and do not read, act upon, print, disclose,
> > copy, retain or redistribute it.
> >
> > Click here for important additional terms relating to this e-mail.
> > <http://www.ml.com/email_terms/>
> >
> >
> ============================================================================
> ==
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Feb 17 2004 - 16:26:36 CST
