Aubrey,
The first thing that comes to my mind, is that your application table
TROPICAL.ZIP_CODE has an INSERT trigger.
Did you check what logic you have in that trigger ? (if it exists)
Even if the trigger has just a SELECT against TROPICAL.ZIPCODE, you will
get this error. The trigger function has to complete before you can
SELECT.
Thanks...
Mohan
-----Original Message-----
From: Aubrey Truex
[mailto:oracledba-ezmlmshield-x14960629.[Email address protected]
Sent: Thursday, March 30, 2006 2:54 PM
To: LazyDBA Discussion
Subject: Using variables during an insert
Hi,
I am trying to use a function in my SQL statement, but it is failing. My
goal is to insert a record into my zip_code table, and during the
insert, I want the function return_zipcode_CSR to return a value to use
in the creation of the new record. My problem is that when I process my
insert, then the function scans the same table to find my value, I get
this error message:
return_zipcode_CSR(zcu.STATE, zcu.COUNTYFIPS),
*
ERROR at line 4:
ORA-04091: table TROPICS.ZIP_CODE is mutating, trigger/function may not
see it
ORA-06512: at "FFVA.RETURN_ZIPCODE_CSR", line 7
ORA-06512: at "FFVA.RETURN_ZIPCODE_CSR", line 12 So, is there anyway I
can setup my query to get the value beforehand, then input that value
into the new record during the insert ?
This is what my insert statement looks like now:
insert into tropics.zip_code(ZIP, CUSTOMER_SERVICE_REP) select
zcu.ZIPCODE, return_zipcode_CSR(zcu.STATE, zcu.COUNTYFIPS) from
ZIPCODE_UNIQUE zcu left join TROPICS.ZIP_CODE z on (zcu.ZIPCODE = z.ZIP)
group by zcu.ZIPCODE, zcu.STATE, zcu.COUNTYFIPS, z.ZIP having ((z.ZIP)
is NULL); The data in ZIPCODE_UNIQUE is from a website with current
zipcodes. The TROPICS.ZIP_CODE table is my application table I want to
update. If a zipcode in ZIPCODE_UNIQUE exists, and it does not exist in
TROPICS.ZIP_CODE, I want to add a record. Function return_zipcode_CSR
goes to an existing record in TROPICS.ZIP_CODE with the same state and
county code of the new zip code, and returns a value for the customer
service rep for that state/county code.
Any suggestions are appreciated.
Thanks, Aubrey
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page