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


Oracle LazyDBA home page