Probably he will need a big "disclaimer" in all his future messages, it's
scary!
-----Original Message-----
From: Jan Pruner [mailto:JPruner_at_email.cz]
Sent: Tue 2/17/2004 5:38 PM
To: oracle-l_at_freelists.org
Cc:
Subject: Re: Counting number of rows
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_at_freelists.org
>>[mailto: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.
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
<
https://access0.fidelity.com/,DanaInfo=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/
<https://access0.fidelity.com/archives/oracle-l/,DanaInfo=www.freelists.org+
>
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
<https://access0.fidelity.com/help/fom-serve/cache/1.html,DanaInfo=www.freel
ists.org+>
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/ms-tnef
----------------------------------------------------------------
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 - 21:44:20 CST
