Answer Posted / purushottam
All the above suggested point is fine but addition on it is
when you are going to use the aggregate function for same
table then also be the mutating table error will occur.
example:
create table t(x numner);
/
create or replace trigger t_af_trigg
before insert into t
for each row
declare
n integer;
begin
select count(*) into n from t;
dbms_output.put_line('there are ' || n || ' rows in t');
end;
/
insert into t values(1);
error:
ORA-04091: table MISC.T is mutating, trigger/function may
not see it
ORA-06512: at "MISC.T_AF_TRIGG", line 5
ORA-04088: error during execution of trigger 'MISC.T_AF_TRIGG'
This example value addition of Vivek's suggession
"Mutating means that some one is trying to access the table
currently being held by some other non-committing
transaction(i.e. in Locked state)"
| Is This Answer Correct ? | 20 Yes | 9 No |
Post New Answer View All Answers
Can you rollback after commit?
How to read/write files from pl/sql?
Is clustered index a primary key?
What is the use of stored procedures?
What is the life of an sql statement?
Explain the uses of control file.
What is a mutating table and a constraining table?
How many scalar data types are supported in pl/sql?
How would you convert date into julian date format?
What are commit, rollback, and savepoint?
What is sql key?
What is a null value?
How can I get the number of records affected by a stored procedure?
what is a scheduled jobs or what is a scheduled tasks? : Sql dba
Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin
