I am inserting a record within a transaction and then later I am retrieving the same record via an Oracle function. The oracle function is returning no records. My DBA told me that Oracle functions do not operate inside a transaction. How do I get around this?
Example: Begin transaction using the oracle provider
Execute some SQL:
INSERT OWNER (FIRST_NAME, LAST_NAME) VALUES ('JOHN', 'SMITH')
Get the record back within the transaction from a function (71 is an example ID): select * from table (GET_OWNER_DETAILS_FNC (71) )
Here is the Oracle Function:
CREATE OR REPLACE FUNCTION "AROH"."GET_OWNER_DETAILS_FNC" (p_owner_information_oid in aroh_owner_information.owner_information_oid%type )
return OWNER_DETAILS_TABLE_TYPE_FNC
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_owner_details_set OWNER_DETAILS_TABLE_TYPE_FNC := OWNER_DETAILS_TABLE_TYPE_FNC();
CURSOR c_owner_dtls IS
select oi.owner_information_oid,
oi.first_name,
oi.last_name,
oi.company_name,
oi.license_information,
oi.company_ind,
oi.middle_initial,
oi.title_type_oid,
oi.suffix,
oi.status_type_code,
oi.primary_phone,
oi.secondary_phone,
oi.secondary_phone_type_code,
oi.primary_phone_type_code,
oi.email_address,
oi.comments,
oi.primary_phone_extension,
oi.secondary_phone_extension,
poa.owner_address_oid as primaryaddressid,
poa.address_type_code as primaryaddresscode,
poa.address1 as primaryaddress1,
poa.address2 as primaryaddress2,
poa.city as primarycity,
poa.state as primarystate,
poa.postal_code as primaryzip,
poa.current_ind as primarycurrent,
soa.owner_address_oid as secondaryaddressid,
soa.address_type_code as secondaryaddresscode,
soa.address1 as secondaryaddress1,
soa.address2 as secondaryaddress2,
soa.city as secondarycity,
soa.state as secondarystate,
soa.postal_code as secondaryzip,
soa.current_ind as secondarycurrent,
( select
( select oa2.owner_information_oid
from aroh_owner_aircraft_rlshp oa2
where upper(primary_owner) like '%PRIMARY%'
and oa2.aircraft_oid = oa1.aircraft_oid
and rownum = 1) as prim_owner_oid
from aroh_owner_aircraft_rlshp oa1
where oa1.owner_information_oid = p_owner_information_oid
and rownum = 1
) as primary_owner_oid,
( select
case when (upper(primary_owner) like '%PRIMARY%')
then 'Y'
else 'N' end as isprimary
from aroh_owner_aircraft_rlshp
where owner_information_oid = p_owner_information_oid
and rownum = 1
) as is_primary
from aroh_owner_information oi
inner join (select *
from aroh_owner_address
where upper(current_ind) = 'Y'
and address_type_code = 'OPRIM') poa
on oi.owner_information_oid = poa.owner_information_oid
left outer join (select *
from aroh_owner_address
where upper(current_ind) = 'Y'
and address_type_code = 'OSEC') soa
on oi.owner_information_oid = soa.owner_information_oid
where oi.owner_information_oid = p_owner_information_oid;
begin
For main_row in c_owner_dtls
loop
v_owner_details_set.EXTEND;
v_owner_details_set(v_owner_details_set.LAST) := OWNER_DETAILS_TYPE (main_row.owner_information_oid , main_row.first_name , main_row.last_name , main_row.company_name
, main_row.license_information , main_row.company_ind , main_row.middle_initial , main_row.title_type_oid , main_row.suffix , main_row.status_type_code , main_row.primary_phone
, main_row.secondary_phone , main_row.secondary_phone_type_code , main_row.primary_phone_type_code , main_row.email_address , main_row.comments , main_row.primary_phone_extension
, main_row.secondary_phone_extension , main_row.primaryaddressid , main_row.primaryaddresscode , main_row.primaryaddress1 , main_row.primaryaddress2 , main_row.primarycity
, main_row.primarystate , main_row.primaryzip , main_row.primarycurrent , main_row.secondaryaddressid , main_row.secondaryaddresscode , main_row.secondaryaddress1
, main_row.secondaryaddress2 , main_row.secondarycity , main_row.secondarystate , main_row.secondaryzip , main_row.secondarycurrent , main_row.primary_owner_oid , main_row.is_primary );
end loop;
return v_owner_details_set;
EXCEPTION
When others
then dbms_output.put_line ('Oracle error: '||SQLERRM);
end;
The AUTONOMOUS_TRANSACTION pragma means that the function operates in the context of a separate transaction. By default (see http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm#sthref1972) this uses the "read committed" isolation level, meaning that, when the transaction queries data, it sees only data that was committed before the query began. Since the data you inserted has not been committed, this means that the function can't see it.