[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-05-15
3
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

656 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