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 developer is 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;
