Solved

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

Posted on 2013-05-15
3
226 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

932 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

10 Experts available now in Live!

Get 1:1 Help Now