?
Solved

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

Posted on 2013-05-15
3
Medium Priority
?
233 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month17 days, 7 hours left to enroll

831 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