Re: Counting number of rows, sequences with no sequences

Jan, function can write to the database, but not in the same transaction. Here is what I have in mind: $ 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
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------