Stored procedure locking the database 2

[LarrySteele]

New to Oracle and trying to learn the tools. Searched for solution, but found nothing to solve this problem.

BACKGROUND:
I have a number of procs that fetch data and load into local tables. Several more procs pull data from these local tables and puts the results into more local tables for reporting. All told, we're looking at about 25 procs, maybe 50 tables. Row count for all tables combined might be 1,000,000. So, we're not talking a lot of data, just have to go through quite a process to assemble it.

PROBLEM:
I have one proc that executes all the other procs in the required order. When I run this proc, we run out of memory and the system locks up. If I run each proc manually in SQL*Plus, they all run with no problem.

QUESTION:
How do I run all these procs in one pass w/o using up all my memory?

MY GUESS:
I'm presuming that memory does not get released until the proc completes its task. That would explain why the procs run with not problem manually, but cannot run as part of this parent proc. I'm sure there's probably a different method/tool for this, but I haven't been able to find it.

Thanks in advance!
Larry

 

[Thargy]

Larry,

I suspect that you shouldn't. First of all, are there stages within the process at which you can commit?

If there are, commit and close all your open cursors. That ought to free up some memory.

The other thing is, I believe that the system is using the wrong approach entirely. Simply moving data from one table to another can nearly always be achieved using SQL, with no need for stored procedures.

I am willing to stick my neck out here. Post the create table statements for one source table and its target and insert statements for sample data.

I will undertake to do the move in SQL (unless you are doing very abstruse data manipulation here). How's that for a challenge?

Regards

T

 

[LarrySteele]

T,

Thanks. Yes, I'm committing after each table insert. For those processes that are a bit more...intensive...I commit every 10,000 records. Only a few procs actually use cursors. A large portion of my procs are written in the following format:

[tt]execute immediate 'truncate table {local table}';

insert into {local table}
{remaining sql statement};

execute immediate 'commit'; [/tt]

As far as the queries go, the initial round of pulling data from the remote tables isn't too bad. What I have to do with the data afterwords, yeah, abstruse is a good description.

What we're working on is creating a reporting database. The design of the source database renders it pretty much useless for reporting. Additionally, the vendor is obstinate about using their GUI to view data and their limited data warehouse to view reports. So every so often we'll get a snapshot of the data in our database and report from it. Consequently, I need some mechanism that will allow me to run through all the required procs/sql to fill our local tables.

Thanks again for your suggestions. Thanks too for offering to convert to sql, right generous.

- Larry

 

[SantaMufasa]

Larry,

Minor point...you can COMMIT in native PL/SQL since COMMIT is standard DML...no need for EXECUTE IMMEDIATE.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Larry,

I still assert that there's no need for the PL, just a script.

If you really do have to use PL, are you populating your cursors one row at a time, or using "FOR ALL" kind of statements?

Do I understand that you're getting a wad of data from a handful of tables, and fanning it out into several normalized tables, from which you can report? If you are, then I'd still suggest SQL. Can you post me one of the 'abstruse' conversions that you have to do. I need create table statements, sample data and a statement of desired output.

I think folks often overlook the sheer power of SQL. When did you last use 'with query subfactoring' or analytics or the model clause?

Regards

T

 

[LarrySteele]

Santa,

Thanks. Funny, I just checked my procs and turns out four of them used the execute immediate syntax, all the rest just had straight commit. Those were artifacts of how I originally wrote my procs - EVERYTHING was wrapped in an execute immediate statement. That's what happens when you're teaching yourself how to do something using books and the web. Since then, I've stumbled on better ways of doing things.

Anyway, got the remainder of those cleaned up. While it may not important per se, I really like consistency.

- Larry

 

[LarrySteele]

T,

Thanks again. When you talk of writing sql to create tables, I feel like I'm not communicating my situation correctly. It's just that my procs already run sql statements, so no need to create sql statements. Maybe I'm wrong. Rather than drawing this out too far, I'll post a few of the procs and see if I'm off track.

Okay, here's one of the simple procs:

CREATE OR REPLACE procedure prc_load_jobs as
-- load the table of job information for use as part of loading the employees table --
begin

  -- empty table -- 
    execute immediate 'truncate table ee_jobs';

  -- fill the table -- 
  insert into hrtdmart.ee_jobs 
  select p1.pernr, 
         p1.stell job_nbr, 
         substr(h.short,-5) aims_job_cd, 
         substr(h.mc_stext,1,20) job_title 
  from hrp1000 h, pa0001 p1 
  where h.objid=p1.stell 
    and h.mandt='020' and h.endda='99991231' and h.otype='C' and h.istat='1'
    and p1.mandt='020' and p1.endda='99991231';

  commit;  

  exception when others then dbms_output.put_line(SQLERRM);           
  
end prc_load_jobs;
/

Here's one of the cludgy procs. In the source table, they have twelve date columns named dat01-dat12. There's an associated date reason column named dar01-dar12. So, a hire date could be in any of the date columns, you have to analyze the dar columns and determine which one contains the magic value of U3. Once you find said column, then you take its associated date column and return this date. <sarcasm>This is much better than normalizing the table with one reason/date per row</sarcasm>

CREATE OR REPLACE procedure prc_load_dates as
-- load the employment dates part of loading the employees table --
begin

  -- empty table -- 
    execute immediate 'truncate table ee_dates';

  -- fill the table -- 
  insert into hrtdmart.ee_dates 
  select a.pernr, 
         s1.last_hire_date, 
         s2.adj_start_date, 
         s3.orig_hire_date, 
         s4.job_date, 
         s5.loa_start_date, 
         s6.loa_end_date, 
         s7.paid_thru_date
  from pa0001 a, 
       (select b.pernr, 
               case when b.dar01='U3' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='U3' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='U3' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='U3' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='U3' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='U3' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='U3' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='U3' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='U3' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='U3' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='U3' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='U3' then to_date(b.dat12,'YYYYMMDD') 
                    end as last_hire_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s1, 
       (select b.pernr, 
               case when b.dar01='U2' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='U2' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='U2' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='U2' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='U2' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='U2' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='U2' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='U2' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='U2' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='U2' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='U2' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='U2' then to_date(b.dat12,'YYYYMMDD') 
                    end as adj_start_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s2, 
       (select b.pernr, 
               case when b.dar01='U1' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='U1' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='U1' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='U1' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='U1' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='U1' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='U1' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='U1' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='U1' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='U1' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='U1' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='U1' then to_date(b.dat12,'YYYYMMDD') 
                    end as orig_hire_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s3, 
       (select b.pernr, 
               case when b.dar01='UB' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='UB' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='UB' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='UB' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='UB' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='UB' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='UB' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='UB' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='UB' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='UB' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='UB' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='UB' then to_date(b.dat12,'YYYYMMDD') 
                    end as job_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s4, 
       (select b.pernr, 
               case when b.dar01='U5' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='U5' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='U5' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='U5' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='U5' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='U5' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='U5' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='U5' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='U5' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='U5' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='U5' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='U5' then to_date(b.dat12,'YYYYMMDD') 
                    end as loa_start_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s5, 
       (select b.pernr, 
               case when b.dar01='UA' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='UA' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='UA' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='UA' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='UA' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='UA' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='UA' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='UA' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='UA' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='UA' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='UA' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='UA' then to_date(b.dat12,'YYYYMMDD') 
                    end as loa_end_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s6,
       (select b.pernr, 
               case when b.dar01='U4' then to_date(b.dat01,'YYYYMMDD') 
                    when b.dar02='U4' then to_date(b.dat02,'YYYYMMDD') 
                    when b.dar03='U4' then to_date(b.dat03,'YYYYMMDD') 
                    when b.dar04='U4' then to_date(b.dat04,'YYYYMMDD') 
                    when b.dar05='U4' then to_date(b.dat05,'YYYYMMDD') 
                    when b.dar06='U4' then to_date(b.dat06,'YYYYMMDD') 
                    when b.dar07='U4' then to_date(b.dat07,'YYYYMMDD') 
                    when b.dar08='U4' then to_date(b.dat08,'YYYYMMDD') 
                    when b.dar09='U4' then to_date(b.dat09,'YYYYMMDD') 
                    when b.dar10='U4' then to_date(b.dat10,'YYYYMMDD') 
                    when b.dar11='U4' then to_date(b.dat11,'YYYYMMDD') 
                    when b.dar12='U4' then to_date(b.dat12,'YYYYMMDD') 
                    end as paid_thru_date 
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s7 
  where s1.pernr=a.pernr 
    and s2.pernr=a.pernr 
    and s3.pernr=a.pernr 
    and s4.pernr=a.pernr 
    and s5.pernr=a.pernr 
    and s6.pernr=a.pernr 
    and s7.pernr=a.pernr 
    and a.mandt='020' and a.endda='99991231';

  commit;  

  exception when others then  dbms_output.put_line(SQLERRM);           
  
end prc_load_dates;
/

The fun continues. This establishes our hierarchy. The rules: We have org units. Org units report to higher org units until we make it to the top. Each org unit has a position assigned to it (1 to 1). Each position *may* have an employee assigned to it, or it may be vacant. If there's a person assigned, return this person's id. Otherwise, find the parent org unit for the current org unit and run the position/person check. If still vacant, then find the next org unit and run the position/person check. Continue until we find someone or we cycle through 5 times. (The 5 is arbitrary, but I wanted to cap the number of attempts so I don't have a runaway query in case the hierarchy is broken).

What makes this one difficult is that I have one table that contains the relationship between org units (what org unit does "xxx" report to?) and it contains the position assignments (what position is assigned to org unit "yyy"). So, I have to look at the resultset of a query to determine whether to query for org unit or query for position. I do this in a loop where I get the parent org information and try to fill the manager's id based on the found position, and I only do this where the manager's id is null (meaning I had not found a manager during a previous iteration).

What we end up with is a table with the following data (obviously not field names):
employee id
employee org unit
position id (will alternate between org unit and position during course of the proc)
manager's id
manager's org unit
manager's level (how many levels up from employee - direct manager is 1, next level is 2)

CREATE OR REPLACE procedure prc_load_managers as
-- load manager data as part of employee table load -- 
-- this procedure involves several steps
-- first, we have to identify everyone's manager (parts 1-7) and put that into mgr_reporting
-- then we have pull data in from other tables and store that as mgr_managers
-- mgr_managers will be used to combine manager data into tbl_employees


  cnt number(5);     -- used to determine how many times to loop through management levels 
  max_cnt number(2); -- used to ensure we don't go into an endless loop when going through mgt levels 
begin

  -- empty working table -- 
  execute immediate 'truncate table mgr_reporting';
    
  -- insert base records -- 
  begin
    insert into mgr_reporting 
    (ee_pernr, ee_org_unit)
    select h.pernr, h.orgeh
    from pa0001 h, ee_status s 
    where h.endda='99991231'  
      and h.orgeh != '00000000'
      and s.pernr=h.pernr 
      and s.status='3';
  
    commit;
  end;
  
  -- part 1 - pass ee's org unit and get parent org unit 
  begin
  
    update mgr_reporting g
    set g.mgr_org_unit = (select h.sobid
                          from hrp1001 h
                          where h.objid=g.ee_org_unit 
                            and h.subty='A002'
                            and h.sclas='O'
                            and h.endda='99991231'),
        g.sobid = (select h.sobid
                   from hrp1001 h
                   where h.objid=g.ee_org_unit 
                     and h.subty='B012'
                     and h.sclas='S'
                     and h.endda='99991231');
  
    commit;
  end;

  -- part 2 - find org_unit, pos_nbr assigments for all a,l,u exempt employees  
  begin
    execute immediate 'truncate table mgr_org_pos';
  
    insert into mgr_org_pos
    select p.pernr, p.orgeh org_unit, p.plans pos_nbr, 0
    from pa0001 p, ee_status s, ee_eeo e
    where p.endda='99991231'
      and p.plans<'99999999'
      and p.pernr=s.pernr
      and p.pernr=e.pernr
      and s.status='3'
      and e.flsa='E';
      
    commit;
  end;  

  -- part 3 - get mgr's pernr from mgr_org_pos and store in mgr_reporting  
  begin
    -- division heads report to an organization that has themselves as the incumbent 
    -- essentially, they appear to report to themselves.  we have to get the org that 
    -- their parent org reports to - essentially walking up the chain 
    update mgr_reporting r
    set r.mgr_pernr = (select m.pernr
                       from mgr_org_pos m
                       where m.pos_nbr=r.sobid
                         and m.pernr!=r.ee_pernr);
        
    commit;
  
  end;  

  -- part 4 - set mgr_level = 1 (indicates direct mgrs found)  
  begin
  
    update mgr_reporting
    set mgr_level = '1'
    where mgr_pernr is not null;
    commit;
  
  end;  

  -- unfilled positions will cause misses in part 5.  we have to walk up the org units 
  -- for each null mgr_pernr.  we have to do this until there's no more missing mgr_pernrs 
  begin
    max_cnt := 0;
    
    loop 
      -- we can stop if there are no more null mgr_pernr's.  
      -- if sobid is null, we'll not be able to id mgr, so ignore null sobid 
      select count(*)
      into cnt  
      from mgr_reporting
      where mgr_pernr is null
        and sobid is not null;
      
      if cnt=0 then exit;
      end if;
    
  -- part 5a - pass mgr's org unit and get its parent org unit and parent position
      update mgr_reporting g
      set g.mgr_org_unit = (select h.sobid
                            from hrp1001 h
                            where h.objid=g.mgr_org_unit 
                              and h.subty='A002'
                              and h.sclas='O'
                              and h.endda='99991231'),
          g.sobid = (select h.sobid
                     from hrp1001 h
                     where h.objid=g.mgr_org_unit 
                       and h.subty='B012'
                       and h.sclas='S'
                       and h.endda='99991231')
      where g.mgr_pernr is null;
  
      commit;
  
  -- part 5b - get mgr's pernr from mgr_org_pos and store in mgr_reporting  
      -- see remark on step 3 regarding division heads 
      update mgr_reporting r
      set r.mgr_pernr = (select m.pernr
                         from mgr_org_pos m
                         where m.pos_nbr=r.sobid
                           and m.pernr!=r.ee_pernr)
      where r.mgr_pernr is null;
        
      commit;
  
  -- part 5c - set mgr_level = 1 + amount of times loop ran (indicates direct mgrs found)  

      max_cnt := max_cnt +1; 
    
      update mgr_reporting
      set mgr_level = to_char(max_cnt +1)
      where mgr_pernr is not null
        and mgr_level is null;

      commit;
    
      if max_cnt = 5 then exit;  -- prevent runaway query if we can't find manager at any level 
      end if; 
  
    end loop;

  end;
  
  -- okay, now we've identified managers, we can fill the managers table! 
  
  -- empty managers table -- 
  execute immediate 'truncate table ee_managers';
    
  -- insert records -- 
  begin
    insert into ee_managers 
    select mr.ee_pernr,
           mr.mgr_pernr,
           u.upi mgr_upi,
           u.ssn mgr_ssn,
           n.first_name mgr_first_name,
           n.mi mgr_mi,
           n.last_name mgr_last_name,
           j.job_title mgr_job_title,
           j.job_nbr mgr_job_nbr,
           mr.mgr_org_unit,
           mr.sobid mgr_pos_nbr,
           mr.mgr_level,
           u.email_id mgr_email_id,
           u.work_phone mgr_work_phone
    from mgr_reporting mr,
         ee_usrids u,
         ee_names n,
         ee_jobs j
    where u.pernr(+)=mr.mgr_pernr
      and n.pernr=mr.mgr_pernr
      and j.pernr(+)=mr.mgr_pernr
    order by ee_pernr;

    commit;    
  end;

  exception when others then dbms_output.put_line(SQLERRM);            

end prc_load_managers;
/

Using the table filled from the previous query, I then build a complete hierarchy. Here's the data we get:

employee id
employee level
first manager's id
second manager's id
third manager's id
...
fifteenth manager's id

CREATE OR REPLACE procedure prc_load_pos_hier as
-- make sure prc_load_managers is run first.  this proc uses mgr_reporting, created from the proc 
begin

  -- subquery to build hierarchy path 
  -- use oracle's sys_connect_by_path function to walk through the mgr_reporting table 
  -- and build a slash delimited hierarchy string from most senior to most junior, then employee 
  
  -- outer query, parse the hierarchy path into separate columns using our user defined function 
  -- credit: [URL unfurl="true"]http://djmein.blogspot.com/2007/03/flatten-out-heirarchy.html[/URL] 
  
  -- empty table -- 
  begin
    execute immediate 'truncate mgr_pos_hier';
    exception when others then null;
  end;
  
  -- fill the table --   
  insert into mgr_pos_hier
  select ee_pernr, 
         pathlen +1,
         case when (NVL (parse_mgr_pernr (Path, 1, 2), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 1, 2), '') else '' end as level1,
         case when (NVL (parse_mgr_pernr (Path, 2, 3), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 2, 3), '') else '' end as level2,
         case when (NVL (parse_mgr_pernr (Path, 3, 4), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 3, 4), '') else '' end as level3,
         case when (NVL (parse_mgr_pernr (Path, 4, 5), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 4, 5), '') else '' end as level4,
         case when (NVL (parse_mgr_pernr (Path, 5, 6), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 5, 6), '') else '' end as level5,
         case when (NVL (parse_mgr_pernr (Path, 6, 7), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 6, 7), '') else '' end as level6,
         case when (NVL (parse_mgr_pernr (Path, 7, 8), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 7, 8), '') else '' end as level7,
         case when (NVL (parse_mgr_pernr (Path, 8, 9), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 8, 9), '') else '' end as level8,
         case when (NVL (parse_mgr_pernr (Path, 9, 10), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 9, 10), '') else '' end as level9,
         case when (NVL (parse_mgr_pernr (Path, 10, 11), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 10, 11), '') else '' end as level10,
         case when (NVL (parse_mgr_pernr (Path, 11, 12), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 11, 12), '') else '' end as level11,
         case when (NVL (parse_mgr_pernr (Path, 12, 13), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 12, 13), '') else '' end as level12,
         case when (NVL (parse_mgr_pernr (Path, 13, 14), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 13, 14), '') else '' end as level13,
         case when (NVL (parse_mgr_pernr (Path, 14, 15), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 14, 15), '') else '' end as level14,
         case when (NVL (parse_mgr_pernr (Path, 14, 15), '') != ee_pernr) 
              then NVL (parse_mgr_pernr (Path, 14, 15), '') else '' end as level15
  from 
    (select ee_pernr, 
            connect_by_root mgr_pernr mgr_pernr,
            level-1 Pathlen, 
            sys_connect_by_path(ee_pernr,'/') || '/' Path
     from mgr_reporting
     where level >1
     start with mgr_pernr is null
     connect by prior ee_pernr=mgr_pernr);
  
  commit;

  exception when others then dbms_output.put_line(SQLERRM);            

end prc_load_pos_hier;
/

Now, admittedly the last two are the hardest one. Most are somewhere between the first two examples.

Now, as far as create table statements, I'm not using CTAS. I'm truncating and inserting. Word I received is truncate/insert (or delete/purge/insert) is less expensive than drop/create. That's why my sql is all about insert.

Thanks again,
Larry

 

[Thargy]

Larry,

I asked, and you have duly delivered - in spades, well done.

Ok, for starters, I think I can speed things up with the first chunk.

With your data, please not the time it takes to run your procedure 'prc_load_jobs'. Then try the following and compare the times, and let me know the outcome. Note that this should be run from sqlplus, and not wrapped in a stored procedure.

Delete the existing table with

DROP TABLE EE_JOBS CASCADE CONSTRAINTS PURGE;

Then do the deed with

CREATE TABLE EE_JOBS
AS
  SELECT P1.PERNR PERNR, 
         P1.STELL JOB_NBR, 
         SUBSTR(H.SHORT,-5) AIMS_JOB_CD, 
         SUBSTR(H.MC_STEXT,1,20) JOB_TITLE 
    FROM HRP1000 H, PA0001 P1 
   WHERE H.OBJID = P1.STELL 
     AND H.MANDT='020' 
     AND H.ENDDA='99991231' 
     AND H.OTYPE='C' 
     AND H.ISTAT='1'
     AND P1.MANDT='020' 
     AND P1.ENDDA='99991231';

Please note that I have reformatted things slightly. I also note that both H.ENDDA and P1.ENDDA equal '99991231'. This raises two questions,

First, since this appears to be a number, if it is a number, why are there single quotes around it?
Second, if field matching is occurring on the same value, is there a possibility of creating an inner join on these two fields? I ask, because this might speed execution even further.

That's my first quick glance, but please bear in mind my new excuse, i.e. I'm winding down for the bank holiday.

Regards

T

 

[Thargy]

Larry,

I just noticed your final comment about deliberately not using CTAS. I confess that I hadn't read that bit, but my post still stands. It's always right to question what people say, and resolve the issue with a test. That's why I said to do the timing test. That way, you know for stone code certain which is fastest.

Regards

T

 

[Thargy]

Having spent a little longer looking at your code, there only seems to be one bit of conditional logic in there, to do with preventing runaway amounts of work.

Everything else is being hindered by stored procedures. The vast majority of this is just a simple script. Note that you should expect a further slight increase in performance, as you will eliminate the overhead of the context switches between PL/SQL and SQL.

I don't want to say anything further until you have had a chance to time the results for the first CTAS.

Regards

T

 

[LarrySteele]

T,

First things first. Timing.

Insert via proc: ~23s
CTAS from SQL*Plus: ~4s
Insert from SQL*Plus: ~15s

I tried this a few times and there was one clear winner, time wise. However, we're not going to be using the command line to run our table updates - they have to automated, and the only method I've been told are procs. If there's a method for running these sql statements with less overhead, I'm listening, just as long as you don't tell me I'd have to copy/paste to the command line.

Additionally, I believe the cost of using CTAS vs insert was with memory use. Alas, my strength is SQL in general, not Oracle.

You asked about the date field ('99991231'). Yes, this is a string, hence the quotes. No, this is not a join candidate. This field determines which record is top of stack.

Oh, and I checked timing on pulling data from remote tables. On one I did, the proc took 23s and SQL*Plus took 1.9S. Now, if you can tell me how to save queries and run them from SQL*Plus automatically, I'm all ears!

Thanks again.

Oh, and just for sticking with me this far, have a star.

 

[SantaMufasa]

...we're not going to be using the command line to run our table updates - they have to automated...

Larry, when you say "they have to (be) automated", do you mean that they must run based upon a schedule (i.e., triggered by clock time), or triggered by an event (i.e., not based on time, but rather based upon a specific event that could occur at any time)?


If the code must run based upon clock time, you can create a "cron" job (in *nix) or a "scheduled" job (in Windoze) to invoke either a SQL or a PL/SQL job, from SQL*Plus:

sqlplus <username>/<password>@<Oracle alias> @<script name>

As a proof of concept, here is a brief, contrived script, which I named, "tt_526.sql":

select 'This is a test for Larry.' from dual;

I then invoke the script as a scheduled job:

C:\DHUNT\N>sqlplus test/test @c:\dhunt\sqldba\tt_526

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Apr 9 10:07:46 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production


Session altered.


PL/SQL procedure successfully completed.


'THISISATESTFORLARRY.'
-------------------------
This is a test for Larry.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

C:\DHUNT\N>

Let us know is this provides a vision of how to do what you want without running from a stored procedure.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Larry,

Santa has one option covered, so I'll take the other.

If you mean that you want to press a button and have it all happen automatically from sqlplus, then yes, it is easy to achieve.

First of all, let me make one point before proceeding further, you've just experienced one of the most common failings in the IT community, i.e. being told about something being bad/slow/undesirable etc. Although the advice and constructive suggestions of others are always welcome, they must always be verified with a definitive test. Given timing tests, you can go back to the "expert" who told you that CTAS is slow and show your timing information. You now have clear cut evidence to show that CTAS is the only way to fly, and that PL/SQL is not. Without the evidence, so-called experts often refuse to believe other than what they falsely understand to be "the truth". The fact that you have done this test several times, substantiates the evidence, and proves that it wasn't a one-off fluke - so well done for doing it properly.

Now, to the main event. I use windoze, and therefore run sql plus therefrom, usually with a convenient batch file to launch both sqlplus and the script file. To keep it simple, I won't mention the batch file further, other than to recommend it, and offer to post a sample, once we've cracked your problem, ok?

So, create a text file using notepad and into that file put the two statements I gave you above, i.e.

DROP TABLE EE_JOBS CASCADE CONSTRAINTS PURGE;
CREATE TABLE EE_JOBS
AS
  SELECT P1.PERNR PERNR,
         P1.STELL JOB_NBR,
         SUBSTR(H.SHORT,-5) AIMS_JOB_CD,
         SUBSTR(H.MC_STEXT,1,20) JOB_TITLE
    FROM HRP1000 H, PA0001 P1
   WHERE H.OBJID = P1.STELL
     AND H.MANDT='020'
     AND H.ENDDA='99991231'
     AND H.OTYPE='C'
     AND H.ISTAT='1'
     AND P1.MANDT='020'
     AND P1.ENDDA='99991231';

Save the file as larry.sql (or something you'll spot easily) and save it where you can easily navigate to it.

Assuming windoze, open a command prompt in the folder where the file is saved, and start a sqlplus session with the relevant id, password and database.

At the sql command prompt enter the following

@larry.sql;

and press return

That will carry out the actions for you. Check that it has occurred ok, and then edit the file again. Add to it the sql from you next stored proc. The larry.sql file should now contain

DROP TABLE EE_JOBS CASCADE CONSTRAINTS PURGE;
CREATE TABLE EE_JOBS
AS
  SELECT P1.PERNR PERNR,
         P1.STELL JOB_NBR,
         SUBSTR(H.SHORT,-5) AIMS_JOB_CD,
         SUBSTR(H.MC_STEXT,1,20) JOB_TITLE
    FROM HRP1000 H, PA0001 P1
   WHERE H.OBJID = P1.STELL
     AND H.MANDT='020'
     AND H.ENDDA='99991231'
     AND H.OTYPE='C'
     AND H.ISTAT='1'
     AND P1.MANDT='020'
     AND P1.ENDDA='99991231';


DROP TABLE EE_DATES CASCADE CONSTRAINTS PURGE;

CREATE TABLE EE_DATES
AS   
  select a.pernr,
         s1.last_hire_date,
         s2.adj_start_date,
         s3.orig_hire_date,
         s4.job_date,
         s5.loa_start_date,
         s6.loa_end_date,
         s7.paid_thru_date
  from pa0001 a,
       (select b.pernr,
               case when b.dar01='U3' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='U3' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='U3' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='U3' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='U3' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='U3' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='U3' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='U3' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='U3' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='U3' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='U3' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='U3' then to_date(b.dat12,'YYYYMMDD')
                    end as last_hire_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s1,
       (select b.pernr,
               case when b.dar01='U2' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='U2' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='U2' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='U2' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='U2' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='U2' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='U2' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='U2' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='U2' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='U2' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='U2' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='U2' then to_date(b.dat12,'YYYYMMDD')
                    end as adj_start_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s2,
       (select b.pernr,
               case when b.dar01='U1' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='U1' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='U1' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='U1' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='U1' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='U1' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='U1' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='U1' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='U1' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='U1' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='U1' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='U1' then to_date(b.dat12,'YYYYMMDD')
                    end as orig_hire_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s3,
       (select b.pernr,
               case when b.dar01='UB' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='UB' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='UB' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='UB' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='UB' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='UB' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='UB' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='UB' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='UB' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='UB' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='UB' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='UB' then to_date(b.dat12,'YYYYMMDD')
                    end as job_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s4,
       (select b.pernr,
               case when b.dar01='U5' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='U5' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='U5' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='U5' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='U5' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='U5' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='U5' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='U5' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='U5' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='U5' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='U5' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='U5' then to_date(b.dat12,'YYYYMMDD')
                    end as loa_start_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s5,
       (select b.pernr,
               case when b.dar01='UA' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='UA' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='UA' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='UA' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='UA' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='UA' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='UA' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='UA' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='UA' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='UA' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='UA' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='UA' then to_date(b.dat12,'YYYYMMDD')
                    end as loa_end_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s6,
       (select b.pernr,
               case when b.dar01='U4' then to_date(b.dat01,'YYYYMMDD')
                    when b.dar02='U4' then to_date(b.dat02,'YYYYMMDD')
                    when b.dar03='U4' then to_date(b.dat03,'YYYYMMDD')
                    when b.dar04='U4' then to_date(b.dat04,'YYYYMMDD')
                    when b.dar05='U4' then to_date(b.dat05,'YYYYMMDD')
                    when b.dar06='U4' then to_date(b.dat06,'YYYYMMDD')
                    when b.dar07='U4' then to_date(b.dat07,'YYYYMMDD')
                    when b.dar08='U4' then to_date(b.dat08,'YYYYMMDD')
                    when b.dar09='U4' then to_date(b.dat09,'YYYYMMDD')
                    when b.dar10='U4' then to_date(b.dat10,'YYYYMMDD')
                    when b.dar11='U4' then to_date(b.dat11,'YYYYMMDD')
                    when b.dar12='U4' then to_date(b.dat12,'YYYYMMDD')
                    end as paid_thru_date
        from pa0041 b where b.endda = '99991231' and b.mandt='020') s7
  where s1.pernr=a.pernr
    and s2.pernr=a.pernr
    and s3.pernr=a.pernr
    and s4.pernr=a.pernr
    and s5.pernr=a.pernr
    and s6.pernr=a.pernr
    and s7.pernr=a.pernr
    and a.mandt='020' and a.endda='99991231';

Try running that, and make sure that it does all it should. Then rinse and repeat for all the other tables. Please do let us know how you get on - my curiosity bump needs a good scratch.

Regards

T

 

[LarrySteele]

Oh this is too much. So you're telling me I can just name my query name.sql and run it without going through the proc stuff? That's just priceless.

Yep, works well. Not quite as fast as running raw from the command line, but still runs 2x as fast as from the proc.

T - I did timing tests previously and didn't find a great deal of time difference between CTAS and truncate/insert. Then again, both were executed as procs from TOAD. Kinda makes me wonder how much time TOAD's adding the execution time.

Dave - Yes, you're right, this is going to be on a scheduler. We've got Oracle on Windows and UNIX, not sure which is going to host when we go into production. Thankfully I'm not involved with that portion of the project.

64k question - can I run a proc from a command line. This is for those cases where I'm looping through the data spaghetti to extract information. Almost all my procs are straight sql, but I have a few that use loops to process data similar to the prc_load_managers proc I posted above. I need to be able to run a block of sql statements, one or two procs, more sql statements, one or two procs, and the rest sql statements.

T - I'm new to Oracle, but not new to programming. Been doing it in one fashion or another for about 20 years (not including my high school time on TRS-80 Mod 1). Fortunately, this new project has given me the opportunity to add another platform to my toolbox. Unfortunately, I didn't have time to actually *learn* Oracle, so just dived in and started scripting using examples I found. We've both seen some incredibly bad examples of coding - unfortunately, when you're new to a platform it's difficult to distinguish quality examples from boisterous examples.

No worries. As David Ivor Young said, "You never learn from success. Success you take as the natural order of things." So, I'll take this as a learning adventure.

Anyway, I'll watch for the answer to the 64k question. Oh, and how to I create a script or something that will run my sql/prc files that I can run from the command line. I still need to test running the whole process from scratch.

Thanks again for your help,
Larry

 

[Thargy]

Yes, I am telling you that, and it is indeed beyond price.

64K answer - yes, it's straight forward enough.
Dave or I can show you how, or if you'd like to try it yourself first, please feel free. Basically, you get the "create or replace procedure xyx" etc. from TOAD, and paste it into the script. Then you put a trailing slash (that's important as it tells oracle to run the preceding script). That creates the procedure, and then you just say EXEC <PROCEDURE NAME>;

TOAD is cool, but for running scripts in production, always use the batch/command line/SQL plus combination. Develope your scripts using the convenience of TOAD by all means, but in the end, you want a text file with the sql in it.

You also mentioned what I was really interested in, looping through the data spaghetti with a stored proc. I want to issue you with another challenge, show me the data processing you're doing with PL and I'll do it in straight sql. Timing tests will of course be mandatory.

Below is a bat file that I use to launch sqlplus from the command prompt. The application I support is called winpath, so that's why you'll see it mentioned. The winpath schema is hard coded, but you can make it soft easily enough. Likewise, the third parameter is a table name, which you obviously don't need. As a 20 year man, I rather think you'll find this a piece of cake.

@echo off 
cls 
echo Winpath Data Load
echo -----------------

if [%1]==[] (goto instructions)
if [%2]==[] (goto instructions)
if [%3]==[] (goto instructions)

CD REFRESH\%3
SQLPLUS -S WINPATH/%2@%1 @%3.SQL
echo.

CD..
CD..

echo.
echo Refresh complete.
pause
goto end

:instructions
echo Batchfile usage:

echo %0 {db Name} {Winpath password} {table name}

echo.
echo Syntax error, refresh did not run, Press any key to try again
echo.
pause

:end

echo.

To see usage, just enter the bat file name with no parameters, and it will prompt you on-screen.

You are quite right in that a good script means that you launch it, and it does the entire task, no ifs buts or maybes. Anything less is amateurish piffle.

Post again with some end-result scripts and timing info - to sate my curiosity. And don't forget my challenge, you do it in a procedure and I'll do it in sql.

Regards

T

 

[LarrySteele]

T,

Okay my friend, for your challenge, feel free to convert my prc_load_managers into straight sql. Most of it is straight sql, just have a loop in there to walk up the hierarchy. Now the easy way is to copy/paste the sql statements between the loop five times - but that's cheating Seriously, I love learning new tricks, so if you have a better way, I'm definitely interested.

I think I'm good to go on the scripts, thanks for the example!

- Larry

 

[Thargy]

Larry,

doing something five times in sql isn't too hard. I am struggling to recognise what you want/need.

For example, you have 5a, 5b and 5c in there, so how would cut and pasting 5 times help?

Anyway, to specifics, your code says

   insert into mgr_reporting
    (ee_pernr, ee_org_unit)
    select h.pernr, h.orgeh
    from pa0001 h, ee_status s
    where h.endda='99991231'  
      and h.orgeh != '00000000'
      and s.pernr=h.pernr
      and s.status='3';

which inserts two columns worth of data (namely ee_pernr and ee_org_unit) into mgr_reporting.

However two or three lines later I see

    update mgr_reporting g
    set g.mgr_org_unit = (select h.sobid
                          from hrp1001 h
                          where h.objid=g.ee_org_unit
                            and h.subty='A002'
                            and h.sclas='O'
                            and h.endda='99991231'),
        g.sobid = (select h.sobid
                   from hrp1001 h
                   where h.objid=g.ee_org_unit
                     and h.subty='B012'
                     and h.sclas='S'
                     and h.endda='99991231');

which makes it a racing certainty that the mgr_reporting table has at least 3 columns, to wit ee_pernr, ee_org_unit and mgr_org_unit. I'm still up for the challenge, but it's pointless me guessing how many columns that table has, what its various data sources are, and how its populated.

Can you please post for that table the following:-
CREATE TABLE statement.
CREATE TABLE statement for each table used to populate/modify the content of mgr_reporting.

Explicit insert statements for sample data for each of the feeder tables, and a sample of desired output. My challenge here is figuring out what's wanted, not the technicalities of doing it. That's my excuse, and I'm sticking to it - ner!

Regards

T

 

[LarrySteele]

T,

Okay, you got me. It's kinda hard to request somebody to write a CTAS statement if they aren't given the table def.

At this point, I have to let you off the hook as venturing into the data itself is where I have to stop. And, knowing what I know about the primary source table, you have to see the data in order to understand how to extract what's needed.

You and Dave have helped tremendously. The future of this project is going to go much smoother because of it.

Thanks again,
Larry

 

[Thargy]

Thanks for realising and understanding the difficulty.

Please do us both a favour - most folks never post how something turns out. Could you be the exception that proves the rule and get back to us some time?

In particular, if you convert all those moves to a drop and CTAS, I reckon that alone will make a huge difference to your time to complete. I'd love to hear the final difference in time to complete between stored procedures and native sql.

To avoid updates (which are typically very slow) do a CTAS by selecting from multiple tables into one, in a single statement. That way you can really make it fly.

W.R.T. the data, if you have to stop for reasons of confidentiality, then who am I to argue? If you could post some anonymised data, then I'm still up to eliminate your PL and replace it with SQL - got the bit between my teeth, see?

I am about to retire for the evening, and I suspect that you are about to leave work (on the supposition that you are the other side of the pond). It's a bank holiday my side of the pond, so I'm looking forward to 4 days out of the office. Enjoy your weekend, even if it doesn't start for another day yet.

Regards

T

 

[SantaMufasa]

[off topic]

Tharg, I'm pleased that you have a UK Bank Holiday that lets you be away from work for 4 days in sequence. Here in the US, it's illegal to have bank holidays that allow banks to be shuttered for four consecutive days (...US banking regulations and all).

So, enjoy the time off, mate.
[/off topic]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”