Global Temp Table (PRESERVE ROWS) truncating unexpectedly

From: <pmmgpgp_at_gmail.com>
Date: Sat, 22 Sep 2007 19:16:51 -0700
Message-ID: <1190513811.139393.140160@50g2000hsm.googlegroups.com>

I love "GLOBAL TEMPORARY TABLEs" (GTT). I have been using them for awhile, and find them to be a great tool.

We are rac'ed but I am logging into one specific instance.

My IDE is pl/sql developer version 7.1.2 and is configured for "Dual Sessions".

I am working on a new package that utilizes three GTTs and all are declared to "PRESERVE ROWS".

CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS
'A' gets loaded with data and 'B' utilizes 'A'.
'C' is not populated with data at the time the
problem occurs.

Before you make an assumptions please read at least the next 3 paragraphs.

I can manually debug the proc that loads 'A' and 'A' stays rock solid as it should.

When I subsiquently manually debug the proc that builds 'B' from 'A', it runs fine but 'B' is empty when it is done. If I look at 'A', it is empty now (been truncated?).

I have created another proc that simply debugs the two procs mentioned above sequentially. After running it I still have my 'A' data and my 'B' has the data that it should (all is well).

As a test I populated the third GTT 'C' with data. With
'A' and 'C' both populated and I run my debug to try to
populate 'B', the data is lost from 'A' and 'C'.

I have included the proc that causes the problem

Any ideas on what might be happening ( and what to do to get around whatever it is ).

Thanks
Dave

---

---

---

procedure get_tech_data_to_report(p_list varchar2, p_type varchar2) is sqlstr varchar2(8000);
sqlstrwho varchar2(8000);
v_list varchar2(2000);

begin

if p_type = 'M'then

if p_list = 'TEST' then -- a test with a large # of names

sqlstrwho:='
where o.ONX_HIERARCHY_CODE in
(select m.ONX_HIERARCHY_CODE from CR_ONYX_SHAPSHOT m where m.ONX_FULL_NAME in
('|| Test_mgrs ||'))';
elsif p_list = 'ALL_SE_OPS' then -- all se ops sqlstrwho:='
where
o.ONX_BCS_REPORT_GRP = ''SE_OPS''
';

elsif p_list = 'ALL_SE_FLD' then -- all se support sqlstrwho:='
where
o.ONX_BCS_REPORT_GRP = ''SE_FLD''
';

elsif instr(p_list,',') = 0 then -- a manager sqlstrwho:='
where
o.ONX_BCS_REPORT_GRP = ''SE_FLD''
';

else

---------------------------------- a list of managers

v_list:= replace(p_list,',',chr(39)||','||chr(30)); sqlstrwho:='
where o.ONX_HIERARCHY_CODE in
(select m.ONX_HIERARCHY_CODE from CR_ONYX_SHAPSHOT m --------- this it
'A'

where m.ONX_FULL_NAME in '||chr(39)||v_list || chr(39) || '))'; end if;
end if;

---

• end of mgr

  ---

if p_type = 'T' then
if instr(p_list,',') = 0 then

---------------------------------- a tech

sqlstrwho:='
where
o.ONX_FULL_NAME = ' || chr(39) || p_list || chr(39); else

---------------------------------- a list of techs

v_list:= replace(p_list,',',chr(39)||','||chr(30)); sqlstrwho:='
where
o.ONX_FULL_NAME in ' || chr(39) || p_list || chr(39); end if;
end if;

---

sqlstr:='
insert into cr_onyx_techs_to_report ---------- this is 'B' select

o.ONX_FIRST_NAME
,o.ONX_LAST_NAME
,o.ONX_FULL_NAME
,o.ONX_JOB_DESCRIPTION
,o.ONX_BLS_UID
,o.ONX_SBC_UID
,o.ONX_SUPERVISOR_UID
,o.ONX_HIERARCHY_CODE
,o.ONX_HIERARCHY_CODE_OWNER
,o.ONX_EMP_STATUS
,o.ONX_LEVEL
,o.ONX_MGR1
,o.ONX_MGR1_SBC_UID
,o.ONX_MGR1_title
,o.ONX_MGR2
,o.ONX_MGR2_SBC_UID
,o.ONX_MGR2_title
,o.ONX_MGR3
,o.ONX_MGR3_SBC_UID
,o.ONX_MGR3_title
,o.ONX_MGR4
,o.ONX_MGR4_SBC_UID
,o.ONX_MGR4_title
,o.ONX_MGR5

,o.ONX_MGR5_SBC_UID
,o.ONX_MGR5_title

from
CR_ONYX_SHAPSHOT o '; --------- this it 'A' sqlstr:=sqlstr||sqlstrwho;

---

PKG_CR_UTILS.CR_Das_Print_Sql
(SqlStr

,'ONYX_Testing' -- report name
,'PKG_CR_ONYX_Testing' -- package name
,'CR_ONYX_Testing' -- procedure
,'xxxxxx' --Calling_cuid
,'zxzxzxzx' --Calling_from

);

execute immediate SqlStr;
execute immediate 'commit';

end get_tech_data_to_report; Received on Sat Sep 22 2007 - 21:16:51 CDT