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 Go to next message
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 #321688 is a reply to message #321687] Wed, 21 May 2008 01:28 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Use dynamic native sql.

eg:

for cur in 'select column from table_not_yet_existing' loop
  stuff;
end loop;
Re: Execute immediate [message #321692 is a reply to message #321687] Wed, 21 May 2008 01:31 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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
icon6.gif  Re: Execute immediate [message #321712 is a reply to message #321687] Wed, 21 May 2008 02:08 Go to previous messageGo to next message
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 Razz


Re: Execute immediate [message #321761 is a reply to message #321712] Wed, 21 May 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you think you need dynamic sql?
Re: Execute immediate [message #321774 is a reply to message #321687] Wed, 21 May 2008 05:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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');


Re: Tuning this piece of code [message #321801 is a reply to message #321795] Wed, 21 May 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post the same question in multiple topics.

Regards
Michel
Re: Execute immediate + Tuning this piece of code (merged) [message #321803 is a reply to message #321687] Wed, 21 May 2008 06:32 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Oh sorry for that Embarassed
Previous Topic: PLS-00201: Compilation Error!
Next Topic: BEFORE LOGON TRIGGER
Goto Forum:
  


Current Time: Fri Dec 02 12:16:46 CST 2016

Total time taken to generate the page: 0.17508 seconds