| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Counting number of rows, sequences with no sequences
Mladen Gogala wrote:
> Jan, function can write to the database, but not in the same
> transaction.
Or if it is used in subselect.
> Here is what I have in mind:
I know it and I hate it. This is a typical duhveloper feature. :-(
> $ sqlplus scott/tiger
>
> SQL*Plus: Release 10.1.0.2.0 - Production on Tue Feb 17 19:08:43 2004
>
> Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> create or replace function count_rec(tbl in varchar2) return
> integer
> 2 as
> 3 PRAGMA AUTONOMOUS_TRANSACTION;
> 4 trunc varchar2(256):='truncate table '||tbl;
> 5 begin
> 6 execute immediate trunc;
> 7 return(0);
> 8 end;
> 9 /
>
> Function created.
>
> SQL> create table emp1 as select * from emp;
>
> Table created.
>
> SQL> select count_rec('EMP1') from dual;
>
> COUNT_REC('EMP1')
> -----------------
> 0
>
> SQL>
>
> On 02/17/2004 06:07:26 PM, Jan Pruner wrote:
>
>>Wrong. >>1. RTFM - Function cannot write to the database, if the function is >>being called from a SELECT statement. >> >>2. your script is good for single user environment, but in multiuser >>environment (like Oracle) will produce a BIG problem!!! >> >>JP >> >>Juan Cachito Reyes Pacheco wrote: >> >> >>>What you can do is to create a one record table, the if the >> >>transaction >> >>>rollbacks it will rollback >>> >>>create table sequence( value number); >>> >>>function getnewsequence >>>select value+1 into variable from sequence; >>>update table sequence value=variable ; >>>return variable ; >>>end; >>> >>>----- Original Message ----- >>>From: "Mladen Gogala" <mladen_at_wangtrading.com> >>>To: <oracle-l_at_freelists.org> >>>Sent: Tuesday, February 17, 2004 6:13 PM >>>Subject: Re: Counting number of rows, sequences >>> >>> >>>What happens if the transaction is rolled back? >>> >>>On 02/17/2004 05:03:14 PM, Juan Cachito Reyes Pacheco wrote: >>> >>> >>>>The other choice if you have to frecuently do counts to that table >> >>is >> >>>>a >>>>sequence >>>> >>>>You create an addiatoinal field with a sequence >>>> >>>>CREATE SEQUENCE, etc. >>>> >>>>row sequence >>>> >>>>1 1 >>>>2 2 >>>>.... >>>>1234 1234 >>>> >>>>Then you select from the sequence the currval, and this is >> >>immediate. ----------------------------------------------------------------Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 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 - 18:17:26 CST
 |
 |