Solved

Recommend an efficient method of accessing data from 2 cursors - oracle 11g

Posted on 2013-05-15
3
225 Views
Last Modified: 2013-06-07
Which form of fetching the records from the cursor is most efficient.
Could you recommend a more efficient way of looping through the cursor.
I read for loops renders poor performance.

CURSOR get_parent_depts IS
 
    SELECT dept.department_id, dept.name  unit_name, d_type.NAME dept_type  
    FROM  
       departments dept
    INNER JOIN
       dept_types d_type
    ON dept.dept_type_id = d_type.dept_type_id;
   
 
  CURSOR get_child_depts (p_dept_id  VARCHAR2) IS

    SELECT * from (SELECT parent_department_id, child_department_id, (select name from departments o where o.department_id = dept_aff.child_department_id ) child_dept
     FROM  
        dept_aff
     WHERE dept_relationship_type_id = 'MILLWORK')
     CONNECT BY NOCYCLE prior child_department_id  = parent_department_id
     START WITH  parent_department_id = p_dept_id;

BEGIN

FOR parent_rec in get_parent_depts
   LOOP
      FOR child_rec in get_child_depts(parent_rec.name)
      LOOP
        -- Insert parent_rec.name child_rec.child_dept
      END LOOP;
   -- Insert a row for parent to itself;
        -- Insert parent_rec.name parent_rec.name
END LOOP;

   RETURN total_val;

END;
0
Comment
Question by:cookiejar
  • 2
3 Comments
 

Author Comment

by:cookiejar
ID: 39169820
In the snippet of code above, the RETURN total_val; should not be a part of the code.
0
 

Author Comment

by:cookiejar
ID: 39169987
So is the 'for loop' the best method to use in this case?  Is anyone out there?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39170006
I would probably do two separate inserts:

insert into parent_depts
select ... from dept_table;

insert into child_depts
select * from child_table;

You should be able to fashion second select to grab the rows from the parent without a parameter.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now