Home » SQL & PL/SQL » SQL & PL/SQL » Execute immediate + Tuning this piece of code (merged)
Execute immediate + Tuning this piece of code (merged) [message #321687] |
Wed, 21 May 2008 01:25  |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
I am creating a table using execute immediate inside a procedure, and i need to access the inside the procedure in a cursor, but the compiler throws up the "ORA-00942: table or view does not exist" . I know that the table does not exist unless the execute immediate statement runs, so is there ANY way how i can do this ?(create a table using execute immediate inside the package and then access it within a cursor definiton in the package)
PROCEDURE GET_MEMO_TYPE IS
create_DUP_TESTSUB_COAEXP_NRM varchar2(1000):=
'create table ADM.DUP_TESTSUB_COAEXP_NRM as
(SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
max(expiration_date) expiration_date
FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM
GROUP BY DUP_TESTSUB.NORMALIZE_DASH(test_substance_code))';
create_COAEX_OBJID_NRM varchar2(1000):=
'create table ADM.OBJID_NRM as
(SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
expiration_date, R_OBJECT_ID
FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM)';
drop_DUP_TESTSUB_COAEXP_NRM varchar2(500) := 'drop table ADM.DUP_TESTSUB_COAEXP_NRM';
drop_OBJID_NRM varchar2(500) := 'drop table ADM.OBJID_NRM';
CURSOR c3_coa_link(tst av_activity.uc06%type ) is
select distinct coa.expiration_date Expiration_Date
from ADM.DUP_TESTSUB_COAEXP_NRM coa
where NORMALIZE_DASH(tst) = test_substance_code(+);
BEGIN
-- Creating the DUP_TESTSUB_COAEXP_NRM table in ADM schema from TST schema
begin
execute immediate create_DUP_TESTSUB_COAEXP_NRM;
exception when others then
insert into adm.memo_log(error_text, log_date,entry_id)
values ('Error in create_DUP_TESTSUB_COAEXP_NRM '||SQLERRM,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
-- Creating the DUP_TESTSUB_COAEX_OBJID_NRM table in ADM schema from TST schema
begin
execute immediate create_COAEX_OBJID_NRM;
exception when others then
insert into adm.memo_log(error_text, log_date,entry_id)
values ('Error in create_COAEX_OBJID_NRM '||SQLERRM,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
The code goes on.
.
.
.
|
|
|
|
Re: Execute immediate [message #321692 is a reply to message #321687] |
Wed, 21 May 2008 01:31   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
A far superior way would be to not create the table dynamically.
Oracle isn't really fond of creating and dropping objects at runtime. DDL is kinda expensive.
Since you already know the structure of your table, you might as well create it beforehand and leave it be.
|
|
|
Re: Execute immediate [message #321694 is a reply to message #321687] |
Wed, 21 May 2008 01:34   |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
Well, I am trying out the 1st solution,
But if it does not work out well, then i would pretty go ahead with the second one...it will take me around 20 minutes to implement, please wait for my response and thanks
|
|
|
Re: Execute immediate [message #321712 is a reply to message #321687] |
Wed, 21 May 2008 02:08   |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
Here is what i did.
Since i knew the structure of the table before hand, i created an empty table of that structure.
Then i use execute immediate, and then do a delete followed by an insert.
PROCEDURE GET_MEMO_TYPE IS
insert_DUP_TESTSUB_COAEXP_NRM varchar2(1000):=
'insert into ADM.DUP_TESTSUB_COAEXP_NRM
(SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
max(expiration_date) expiration_date
FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM
GROUP BY DUP_TESTSUB.NORMALIZE_DASH(test_substance_code))';
insert_COAEX_OBJID_NRM varchar2(1000):=
'insert into ADM.OBJID_NRM
(SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
expiration_date, R_OBJECT_ID
FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM)';
delete_DUP_TESTSUB_COAEXP_NRM varchar2(500) := 'delete from ADM.DUP_TESTSUB_COAEXP_NRM';
delete_OBJID_NRM varchar2(500) := 'delete from ADM.OBJID_NRM';
CURSOR c3_coa_link(tst av_activity.uc06%type ) is
select distinct coa.expiration_date Expiration_Date
from ADM.DUP_TESTSUB_COAEXP_NRM coa
where NORMALIZE_DASH(tst) = test_substance_code(+);
BEGIN
-- Deleting from temporary table ADM.DUP_TESTSUB_COAEXP_NRM
begin
execute immediate delete_DUP_TESTSUB_COAEXP_NRM;
exception when others then
v_error_text := 'Error in delete_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
insert into adm.memo_log(error_text,log_date,entry_id)
values( v_error_text,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
-- Deleteing from temporary table ADM.OBJID_NRM
begin
execute immediate delete_OBJID_NRM;
exception when others then
v_error_text := 'Error in delete_OBJID_NRM '||SQLERRM;
insert into adm.memo_log(error_text,log_date,entry_id)
values( v_error_text,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
-- inserting into ADM.DUP_TESTSUB_COAEXP_NRM table from TST schema
begin
execute immediate insert_DUP_TESTSUB_COAEXP_NRM;
exception when others then
v_error_text := 'Error in insert_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
insert into adm.memo_log(error_text, log_date,entry_id)
values( v_error_text,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
-- Inserting into ADM.DUP_TESTSUB_COAEX_OBJID_NRM table from TST schema
begin
execute immediate insert_COAEX_OBJID_NRM;
exception when others then
v_error_text := 'Error in insert_COAEX_OBJID_NRM '||SQLERRM;
insert into adm.memo_log(error_text, log_date,entry_id)
values( v_error_text,
to_date(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
memo_entry_id.nextval);
end;
Thanks for the help guys
|
|
|
|
Re: Execute immediate [message #321774 is a reply to message #321687] |
Wed, 21 May 2008 05:15   |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
I need to tune this bit of code, it is taking a whopping 55 MINUTES to execute, can anyone please help.
begin
select distinct
dma.memo_type,
dma.memo_alert_id,
dup.Project_Name,
dup.Project_Description,
dup.Activity_Name,
dup.Activity_Description,
dup.Report_or_Study_No,
dup.Start_Date,
dup.End_Date,
dup.Reg_Due_Date,
dup.Activity_status,
dup.Activity_OBS,
dup.Memo_sent,
dup.ver,
GET_FULL_NAME(e2.resp1) as Functional_mgr,
GET_FULL_NAME(dup.Responsible_person) as Responsible_person,
GET_FULL_NAME(rq.rn) as Resource_name,
dup.Tst_Subst_code,
nvl(doc.DREQ_OUT2,'NO SIF_LINK'),
-- max(coa.expiration_date)
nvl(PKG_MEMO10.coa_url_new(dup.Tst_Subst_code, coa.expiration_date),'NO COA_LINK')
BULK COLLECT INTO memo_types, memo_alert_ids, Project_Names, Project_Descriptions,
Activity_Names, Activity_Descriptions, Report_or_Study_Nos, Start_Dates,
End_Dates, Reg_Due_Dates, Activity_statuss, Activity_OBSs, Memo_sents,
vers, resp1s, Responsible_persons, rns, Tst_Subst_codes,
sif_links, coa_links --exp_dates
from ADM.DUP_MEMO_STATUS_10 dup,
av_element e1,
av_element e2,
av_resreq rq,
adm.new_dup_memo_alerts dma,
adm.dup_docbase_requests doc,
ADM.dup_testsub_coaexp_nrm coa
where dup.Project_Name = dma.project
and dup.Activity_Name = dma.activity
and dup.Project_Name = rq.proj
and dup.Activity_Name = rq.act
and rq.ver in ( 0,97 )
and e1.resp1<>e2.resp1
and (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
and nvl(dma.sent_flag,'N') = 'N'
and doc.DREQ_IN7(+) = dup.Project_Name
and doc.DREQ_IN8(+) = dup.Activity_Name
and dma.memo_type = memo_assigned
and dup.memo_sent = memo_assigned
and NORMALIZE_DASH(dup.Tst_Subst_code) = test_substance_code(+);
if memo_alert_ids.count > 0 then
forall av in memo_alert_ids.FIRST..memo_alert_ids.LAST
insert into ADM.dup_memo_assigned(
memo_type, memo_alert_id, project_name, project_description, activity_name,
activity_description, ver, study_no, start_date, end_date, reg_due_date,
act_res_status, obs, func_mgr, uc04, rn, uc06, memo_sent, sif_link,
coa_link, /*exp_date,*/ time_stamp)
values
( memo_types(av), memo_alert_ids(av), Project_Names(av), Project_Descriptions(av),
Activity_Names(av), Activity_Descriptions(av), vers(av), Report_or_Study_Nos(av),
start_dates(av), End_Dates(av), Reg_Due_Dates(av), Activity_statuss(av),
Activity_OBSs(av), resp1s(av), Responsible_persons(av),
rns(av), Tst_Subst_codes(av), memo_sents(av), sif_links(av), coa_links(av),
/*exp_dates(av),*/
to_date(to_char(sysdate,'DD-MON-YYYY'), 'DD-MON-YYYY'));
end if;
end;
I have analysed the part taking the most time, it is in this function
FUNCTION coa_url_new( p_IN_DASH in Varchar2 default null,
p_Expiration_Date in Date default null)
return varchar2
is
url varchar2(2000);
coaurl varchar2(2000);
objid varchar2(100);
v_ascii_0 PLS_INTEGER := ascii('0');
v_ascii_9 PLS_INTEGER := ascii('9');
v_last_dash PLS_INTEGER; -- location of last dash in string
v_new_subst varchar2(35); -- normalized version of v_subst; need more than 30 chars because we're lengthening it
i PLS_INTEGER; -- loop index over length of v_new_subst
v_num_digits PLS_INTEGER; -- number of digits following last dash
IN_DASH varchar2(30);
begin
if p_IN_DASH is null then
IN_DASH := IN_DASH;
-- return p_IN_DASH;
else
v_new_subst := UPPER(RTRIM(p_IN_DASH,' '));
if substr( v_new_subst, 1, 3) = 'IN-' then
v_new_subst := substr( v_new_subst, 4);
elsif substr( v_new_subst, 1, 4) = 'DPX-' then
v_new_subst := substr( v_new_subst, 5);
end if;
i := LENGTH( v_new_subst);
-- traverse string from end to beginning to find last dash
v_last_dash := 0;
for i in reverse 1..length(v_new_subst)
loop
if substr(v_new_subst,i,1) = '-' then
v_last_dash := i;
exit;
else NULL; -- it is not a dash, so keep checking
end if;
end loop;
if v_last_dash <= 0 then
IN_DASH := v_new_subst;
-- return v_new_subst;
else
-- count number of digits immediately following dash
v_num_digits := 0;
for i in v_last_dash+1..length( v_new_subst)
loop
if ascii( substr( v_new_subst, i, 1)) between v_ascii_0 and v_ascii_9 then
v_num_digits := v_num_digits + 1;
else exit;
end if;
end loop;
if v_num_digits = 1 then
v_new_subst := substr( v_new_subst, 1, v_last_dash) || '00'
|| substr( v_new_subst, v_last_dash+1);
elsif v_num_digits = 2 then
v_new_subst := substr( v_new_subst, 1, v_last_dash)
|| '0' || substr( v_new_subst, v_last_dash+1);
else null; -- either no digits or 3 or more digits after dash; return as-is
end if;
end if;
IN_DASH := v_new_subst;
-- return v_new_subst;
end if;
select documentum_link into coaurl
from adm.artemis_portal_setup;
select a.r_object_id into objid
from ADM.OBJID_NRM a
where test_substance_code = IN_DASH
and to_char(expiration_date,'DD-MON-YYYY') = To_Char(p_expiration_date,'DD-MON-YYYY');
-- select coa_url
-- into objid
-- from stock_items
-- where stock_item_id = p_stock_item_id;
if (objid is not null) then
-- url := coaurl||''''||objid||'''';
url := coaurl||objid;
return(url);
else return(null);
end if;
exception when others then
return(null);
end;
This line below is the culprit
select a.r_object_id into objid
from ADM.OBJID_NRM a
where test_substance_code = IN_DASH
and to_char(expiration_date,'DD-MON-YYYY') = To_Char(p_expiration_date,'DD-MON-YYYY');
|
|
|
Tuning this piece of code [message #321795 is a reply to message #321687] |
Wed, 21 May 2008 06:20   |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
I need to tune this bit of code, it is taking a whopping 55 MINUTES to execute, can anyone please help.
begin
select distinct
dma.memo_type,
dma.memo_alert_id,
dup.Project_Name,
dup.Project_Description,
dup.Activity_Name,
dup.Activity_Description,
dup.Report_or_Study_No,
dup.Start_Date,
dup.End_Date,
dup.Reg_Due_Date,
dup.Activity_status,
dup.Activity_OBS,
dup.Memo_sent,
dup.ver,
GET_FULL_NAME(e2.resp1) as Functional_mgr,
GET_FULL_NAME(dup.Responsible_person) as Responsible_person,
GET_FULL_NAME(rq.rn) as Resource_name,
dup.Tst_Subst_code,
nvl(doc.DREQ_OUT2,'NO SIF_LINK'),
-- max(coa.expiration_date)
nvl(PKG_MEMO10.coa_url_new(dup.Tst_Subst_code, coa.expiration_date),'NO COA_LINK')
BULK COLLECT INTO memo_types, memo_alert_ids, Project_Names, Project_Descriptions,
Activity_Names, Activity_Descriptions, Report_or_Study_Nos, Start_Dates,
End_Dates, Reg_Due_Dates, Activity_statuss, Activity_OBSs, Memo_sents,
vers, resp1s, Responsible_persons, rns, Tst_Subst_codes,
sif_links, coa_links --exp_dates
from ADM.DUP_MEMO_STATUS_10 dup,
av_element e1,
av_element e2,
av_resreq rq,
adm.new_dup_memo_alerts dma,
adm.dup_docbase_requests doc,
ADM.dup_testsub_coaexp_nrm coa
where dup.Project_Name = dma.project
and dup.Activity_Name = dma.activity
and dup.Project_Name = rq.proj
and dup.Activity_Name = rq.act
and rq.ver in ( 0,97 )
and e1.resp1<>e2.resp1
and (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
and nvl(dma.sent_flag,'N') = 'N'
and doc.DREQ_IN7(+) = dup.Project_Name
and doc.DREQ_IN8(+) = dup.Activity_Name
and dma.memo_type = memo_assigned
and dup.memo_sent = memo_assigned
and NORMALIZE_DASH(dup.Tst_Subst_code) = test_substance_code(+);
if memo_alert_ids.count > 0 then
forall av in memo_alert_ids.FIRST..memo_alert_ids.LAST
insert into ADM.dup_memo_assigned(
memo_type, memo_alert_id, project_name, project_description, activity_name,
activity_description, ver, study_no, start_date, end_date, reg_due_date,
act_res_status, obs, func_mgr, uc04, rn, uc06, memo_sent, sif_link,
coa_link, /*exp_date,*/ time_stamp)
values
( memo_types(av), memo_alert_ids(av), Project_Names(av), Project_Descriptions(av),
Activity_Names(av), Activity_Descriptions(av), vers(av), Report_or_Study_Nos(av),
start_dates(av), End_Dates(av), Reg_Due_Dates(av), Activity_statuss(av),
Activity_OBSs(av), resp1s(av), Responsible_persons(av),
rns(av), Tst_Subst_codes(av), memo_sents(av), sif_links(av), coa_links(av),
/*exp_dates(av),*/
to_date(to_char(sysdate,'DD-MON-YYYY'), 'DD-MON-YYYY'));
end if;
end;
I have analysed the part taking the most time, it is in this function
FUNCTION coa_url_new( p_IN_DASH in Varchar2 default null,
p_Expiration_Date in Date default null)
return varchar2
is
url varchar2(2000);
coaurl varchar2(2000);
objid varchar2(100);
v_ascii_0 PLS_INTEGER := ascii('0');
v_ascii_9 PLS_INTEGER := ascii('9');
v_last_dash PLS_INTEGER; -- location of last dash in string
v_new_subst varchar2(35); -- normalized version of v_subst; need more than 30 chars because we're lengthening it
i PLS_INTEGER; -- loop index over length of v_new_subst
v_num_digits PLS_INTEGER; -- number of digits following last dash
IN_DASH varchar2(30);
begin
if p_IN_DASH is null then
IN_DASH := IN_DASH;
-- return p_IN_DASH;
else
v_new_subst := UPPER(RTRIM(p_IN_DASH,' '));
if substr( v_new_subst, 1, 3) = 'IN-' then
v_new_subst := substr( v_new_subst, 4);
elsif substr( v_new_subst, 1, 4) = 'DPX-' then
v_new_subst := substr( v_new_subst, 5);
end if;
i := LENGTH( v_new_subst);
-- traverse string from end to beginning to find last dash
v_last_dash := 0;
for i in reverse 1..length(v_new_subst)
loop
if substr(v_new_subst,i,1) = '-' then
v_last_dash := i;
exit;
else NULL; -- it is not a dash, so keep checking
end if;
end loop;
if v_last_dash <= 0 then
IN_DASH := v_new_subst;
-- return v_new_subst;
else
-- count number of digits immediately following dash
v_num_digits := 0;
for i in v_last_dash+1..length( v_new_subst)
loop
if ascii( substr( v_new_subst, i, 1)) between v_ascii_0 and v_ascii_9 then
v_num_digits := v_num_digits + 1;
else exit;
end if;
end loop;
if v_num_digits = 1 then
v_new_subst := substr( v_new_subst, 1, v_last_dash) || '00'
|| substr( v_new_subst, v_last_dash+1);
elsif v_num_digits = 2 then
v_new_subst := substr( v_new_subst, 1, v_last_dash)
|| '0' || substr( v_new_subst, v_last_dash+1);
else null; -- either no digits or 3 or more digits after dash; return as-is
end if;
end if;
IN_DASH := v_new_subst;
-- return v_new_subst;
end if;
select documentum_link into coaurl
from adm.artemis_portal_setup;
select a.r_object_id into objid
from ADM.OBJID_NRM a
where test_substance_code = IN_DASH
and to_char(expiration_date,'DD-MON-YYYY') = To_Char(p_expiration_date,'DD-MON-YYYY');
-- select coa_url
-- into objid
-- from stock_items
-- where stock_item_id = p_stock_item_id;
if (objid is not null) then
-- url := coaurl||''''||objid||'''';
url := coaurl||objid;
return(url);
else return(null);
end if;
exception when others then
return(null);
end;
This line below in the above funtion is taking the most time (aprrox 52 minutes)
select a.r_object_id into objid
from ADM.OBJID_NRM a
where test_substance_code = IN_DASH
and to_char(expiration_date,'DD-MON-YYYY') = To_Char(p_expiration_date,'DD-MON-YYYY');
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 12:17:00 CST 2025
|