SQL & PL/SQL :: Mutating Error?

Oct 20, 2013

1.I have created table emp_log using structure of emp table

create table emp_log
as select * from emp
where 1=2;

2.Now I have added some new fields i.e
new_sal(updated salary) ,
upd_by (who updated),
upd_date (Salary update date)
alter table emp_log
add (new_sal number, upd_by varchar2(20),upd_date date);

3. I have made following trigger to insert old and new values (for salary,user and date) in emp_log table whenever I update sal for particular employee in emp table and at the same time i am selecting the updated value of sal into lv_sal variable which will cause mutating error.

create or replace trigger emp_trg
after update of sal on emp
for each row
declare

[code]...

4. Suppose sal of emp is 1000:-
update emp
set sal=sal+100
where empno=7369;
>select * from emp_log;
output:- sal= 1000 new_sal=1100

[code]...

Now the question starts if i commment pragma autonomous_tansaction and commit statement then it gives mutating error because at the same time i am updating and selecting value of sal from emp table.

create or replace trigger emp_trg
after update of sal on emp
for each row
declare

[code]...

Questions:-
1.Is pragma autonomous_transaction handling the mutating error and if yes then how?
2.How many ways are there to handle mutating error.
3.What is the exact definition of Mutating Error.
4.An example or query to use lv_sal variable for printing new updated sal.
5.What is Mutating Table , Is it similar to mutating error (Found this topic while searching for mutating error).

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: What Is Mutating Error

Sep 2, 2005

What is Mutating error,have u faced it, I have faced this question so many time in interviews but i have not facing this problem till now.

View 15 Replies View Related

SQL & PL/SQL :: Mutating Trigger Error

Jun 13, 2012

I have created a trigger which gets executed whenever there is DML operation happens on it. I am getting the below error. ORA-04091: table RTS_SCHEMA.TBL1 is mutating, trigger/function may not see it...the tigger is on table tbl1 and the structure of the table is

CREATE TABLE TBL1
(
SLNO NUMBER NULL,
DES VARCHAR2(20 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Avoid Mutating Table Error?

May 16, 2011

Best solution to avoid mutating table error?

View 11 Replies View Related

PL/SQL :: Oracle 10g Express - Mutating Table Error

Apr 11, 2013

Oracle version: 10g express.

case study: one class has many students, only one student has reward.

original design:
drop table student;
drop table class;

[Code]...

questions:

1. If I want insert into student values('stu2','cls1','yes'); I can put a trigger on student to check whether it has only one student in class has reward. This trigger should tell me error: I can not let 'stu2' has 'yes' on reward column. But it will lead Oracle error: ora-04091: mutating table. It seems use "after" or "instead of" trigger can solve this problem, so how to do it?

2. Another way to make sure only one student in a class has reward is change design as:

drop table student;
drop table class;
create table class(
clsid varchar2(9) primary key,
reward_stuid varchar2(9));
create table student(
stuid varchar2(9) primary key,
clsid varchar2(9)

not null references class(cid) on delete cascade);But the question is these two tables has foreign key from each other, is it a good design?.

View 7 Replies View Related

SQL & PL/SQL :: Mutating Trigger Error Using Global Temporary Table?

Jun 21, 2010

How can we overcome mutating trigger error using global temporary table.

Suppose if we use the following trigger we will get mutating trigger error.

CREATE OR REPLACE TRIGGER t1
AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
UPDATE emp SET sal=sal+100;
END;
/

View 27 Replies View Related

SQL & PL/SQL :: AFTER INSERT Or UPDATE Trigger Failed - Mutating Table Error

Jul 4, 2013

Table A basically has 4 rows of interest, an outside event changes/inserts let's say row 1.

Row 2 and 3 have to be changed in a trigger depending on the new value of 1.(or not if the value stays the same)

Row 4 is the reference for the update and will not be changed.

My first simple AFTER INSERT or UPDATE trigger obviously failed because of the mutating table error.

View 19 Replies View Related

SQL & PL/SQL :: Mutating Tables And Row Level Triggers?

Feb 24, 2010

I am unable to understand why row level triggers cant be used in mutating tables.

If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.

View 1 Replies View Related

SQL & PL/SQL :: Mutating Trigger While Updating Original Table

Feb 18, 2013

I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.

Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".

But however I am still facing the same issue.

Test case:

CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR2(1),
STATUS NUMBER(9)
);

INSERT INTO EMPLOYEE VALUES ('A',1);
INSERT INTO EMPLOYEE VALUES ('B',1);
commit;
[code]....

Also, any alternate options (rather than using 2 triggers with temp table).

View 7 Replies View Related

Error - ORA-06521 - PL/SQL Error Mapping Function

Nov 15, 2010

Oracle version - 10.1.0.4 OS - HP-UX

In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.

CODEORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6

View 4 Replies View Related

Error While Exporting A Job

Aug 6, 2010

error while exporting a job

ORA-39171- job is experiencing a resumable wait
ORA-01652-UNABLE TO EXTEND TEMP SEGMENT BY 128 IN TABLESPACE TEMP.

View 2 Replies View Related

Getting Error - Too Many Values

Jul 23, 2010

im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query

select count(*) from table1
where (
select
( (extract(day from column2)-extract(day from column1))*24*60 +
(extract(hour from column2)-extract(hour from column1))*60 +
extract(minute from column2)-extract(minute from column1)) as Processed_time from table1
) IN (1,5)

getting an error TOO MANY VALUES

View 2 Replies View Related

SQL & PL/SQL :: Compilation Error In 11.2.0.3

Jun 6, 2011

In oracle 11.2.0.3 I am getting, PL/SQL:ORA-00904 TR_STATE_NAME invalid identifier

but the same code compiled without any compilation error (though column name specified was wrong).I corrected the column name and its working fine in 11.2.0.3 now. Is there any enhancement in Oracle Version 11.2.0.3 in terms of compilation error?

View 6 Replies View Related

SQL & PL/SQL :: Error While Using Cursor

Apr 10, 2013

My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.

CURSOR c_arch_trk (p_run_date DATE,
p_nbr_days_arch1 NUMBER,
p_nbr_days_arch2 NUMBER )
IS
SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR,
a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Constraint - Getting Error?

Aug 14, 2013

Create table students
(
Sid number(3),
Sname varchar(20) not null,
Email varchar(20) not null,
Doj Timestamp default sysdate not null,
Dob date not null,

[code]....

when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..

Error:
SQL>
SQL> Create table students
2 (
3 Sid number(3),
4 Sname varchar(20) not null,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Error In Execute Immediate?

May 10, 2013

I have attached my Code and log records, here while calling Execute Immediate the program throws an error.

View 4 Replies View Related

SQL & PL/SQL :: Error With Creating Job

Sep 18, 2010

I am writing the following PLSQL code-

BEGIN

DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........

The new table used above has the following structure

Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE

However I get the following error on execution and am not able to make out why?

BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3

View 4 Replies View Related

SQL & PL/SQL :: Getting Error With Trigger

Jul 20, 2011

I just create a trigger to fire whenever the quantity on hand for a books table has been updated.when the quantity on hand become zero ,it should call 'insert_reorder' procedure and pass the ISBN for the books. This is my code

CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN
IF :NEW.on_hand_quantity = 0 THEN
insert_reorde_pp (:NEW.isbn)
END IF;
END;

But i m getting this error

LINE/COL ERROR
4/3 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.

View 5 Replies View Related

SQL & PL/SQL :: Getting PLS-00201 Error?

May 21, 2012

I have a job to run 'scott.product_pkg' daily, but I am getting this following error.

Job failure message:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'scott.product_pkg' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

View 4 Replies View Related

SQL & PL/SQL :: Getting Error By Using NVL() And Loop

Sep 22, 2012

Declare
x number;
Begin
Loop
if NVL(x,1)>=1 then
[code].......

May be this code is going into an infinite loop. I'm unable to understand where the problem is?

View 6 Replies View Related

SQL & PL/SQL :: Error Using Dbms-lob?

Mar 6, 2013

I am begginer programing oracle and I have a issue to resolve but I can't resolve it.I have a procedure that upload a image from a directory. But when I pass the path, the function bfilename put a slash "/" in the path. I don't know why.Here my code.

CREATE OR REPLACE PROCEDURE p_grava_assinatura_gestor AS
---------------------------------------------------------
-- Crio o Cursor dos Nomes dos Arquivos JPG. --
---------------------------------------------------------
CURSOR cursor_nome_arquivo IS
SELECT p.id, (p.empresa || '_' || p.chapa || '.JPG') AS noarq

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Error While Execution

Jul 23, 2010

When i am trying to execute the below in sql. i am getting the error.

create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared

View 2 Replies View Related

SQL & PL/SQL :: Without Error - Keep RowID?

Oct 9, 2013

CREATE TABLE CD_TRANS_GLIDE
( "TRANS_GLIDE_ID" CHAR(32) NOT NULL ENABLE,
"CARD_SEQ_NO" CHAR(10),
"SUBACCOUNT_NO" VARCHAR2(10),
"CASH_FLAG" CHAR(1),

[code]....

View 7 Replies View Related

PL/SQL :: Getting Ora-12170 Error?

Aug 20, 2012

In my database while querying the below sql statement i am getting the ora-12170 error.

select AVAIL_QTY from CCH_AVAILABLE_QUANTITY;

when i used the describe command

DESC CCH_AVAILABLE_QUANTITY; it is executing and showing the 'AVAIL_QTY' column in the output.

i tried using all_tab_columns, all_tables to identify the schema of this table. the strange thing i found is

select * from all_tab_columns where column_name like '%AVAIL_QTY%'; this line is giving showing me the table name in the output.

but,

select * from all_tables where table_name like 'CCH_AVAILABLE_QUANTITY'; this line is executing but not giving me the out put.

View 2 Replies View Related

SQL & PL/SQL :: Sequence - Getting Error?

May 13, 2011

i have create one sequece using this syntax

CREATE SEQUENCE seq_emp_id START WITH 100;

after that when i create a table using this syntax

CREATE TABLE employee
(
emp_id integer default NEXTVAL ('seq_emp_id'),
name text
);

i got an error like this
*
ERROR at line 2:
ORA-04044: procedure, function, package, or type is not allowed here

so how can i resolve my this error

View 3 Replies View Related

Getting OEM Access Error?

Apr 8, 2011

Successfull installation of my Oracle Client was done.When i opened Enterprise Manage Console and i try to login to a database, the following error occurs

" The procedure entry point kpufhndl() could not be located in the dynamic link library OCI.dll "

What can be done to resovle this error?

View 1 Replies View Related

PL/SQL :: SQL Error - ORA-00904?

Nov 11, 2012

I'm getting the flowing error:

     Error starting at line 1 in command:
    INSERT INTO driver (registration, make, model, gvw, year, body) VALUES('4585 AW','ALBION','RIEVER',20321,1963, ' ');
    Error at Command Line:1 Column:53
    Error report:
   
[code]...

   Error starting at line 1 in command:
    INSERT INTO driver (registration, make, model, gvw, year) VALUES('4585 AW','ALBION','RIEVER',20321,1963)
    Error at Command Line:1 Column:53
    Error report:
    SQL Error: ORA-00904: "YEAR": invalid identifier
    00904. 00000 -  "%s: invalid identifier"

i have columns both called year and body, yet I'm getting errors

View 3 Replies View Related

Error During BULK Insert?

Sep 28, 2011

I made two runs for bulk insertion

In first run, 16,36,897 were inserted successfully in around 38 seconds.But in second run, 54,62,952 records had to be inserted, but process failed after 708 seconds with following error :

Error report:
ORA-04030: out of process memory when trying to allocate 980248 bytes (PLS non-lib hp,DARWIN)
ORA-06512: at line 21
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)"
*Cause: Operating system process private memory has been exhausted
*Action:

Here is my code snippet :
.......
FORALL i in products_tab.first .. products_tab.last
INSERT INTO tab1 VALUES products_tab(i);
COMMIT;
.........

I think that there should not have been any problem in getting it completed successfully.

View 4 Replies View Related

Error While Trying To Import Database?

Jul 3, 2012

i try to import MyData.DMP to my oracle using PL/SQL developer

i try this: Tools -> Import tables -> i choos MyData.DMP -> import

but i can see the new database in the log file i see this:

Import started on 03/07/2012 13:43:23
C:\app\ME\product\11.1.0\db_1\bin\imp.exe log=C:\DOCUME~1\ME\LOCALS~1\Temp\plsimp.log file=C:\DOCUME~1\ME\lab.DMP userid="scott/tiger@ORCL as sysdba" buffer=30720 commit=no full=yes grants=yes ignore=yes indexes=yes rows=yes show=no constraints=yes
Import finished on 03/07/2012 13:43:23

View 4 Replies View Related

Error During Execution Trigger

Jan 17, 2011

I have a trigger that is called from an update on the table, this trigger performs the procedure and this procedure update the Same record in the table That shot the trigger. this situation returns error ORA-0060 - DEADLOCK DETECTED WHILE WAITING FOR RESOURCE. Is there any way that this works?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved