Solved

Fetch from REF CURSOR Into Sting-Indexed Associative Array

Posted on 2007-04-02
7
6,455 Views
Last Modified: 2008-10-30
It is my understanding that in Oracle 9i, you cannot BULK COLLECT into an associative array that is indexed by VARCHAR2.

So can someone help me with the syntax for fetching the values from a ref cursor into an array that is not indexed by BINARY_INTEGER?  The following is a simple example for what I am trying to do, but in the final I will be passing the ref cursor as an argument.

Thanks

DECLARE

   TYPE emp_rec IS RECORD
      (emp_nm                              VARCHAR2(50),
       emp_email                           VARCHAR2(70));
     
   TYPE emp_tab_type IS TABLE OF emp_rec
      INDEX BY VARCHAR2(11);
     
   emp_tab                                 emp_tab_type;

   TYPE rcur IS REF CURSOR;
   
   rcur_birthday                           rcur;

BEGIN

   OPEN rcur_birthday FOR
   SELECT employee_id,
          employee_name,
          employee_email
   FROM employee
   WHERE employee_birth_date = TRUNC(SYSDATE);
   
   FETCH rcur_birthday BULK COLLECT INTO emp_tab;


END;    
0
Comment
Question by:akamarcel
  • 3
  • 3
7 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 18841029
Unfortunately, you cannot bulk collect into an associative array with non-numeric indices.
By the way, what are you expecting as the value of the indices here? Why do you like to code this way?

An alternative is:

DECLARE

   TYPE emp_rec IS RECORD
      (emp_nm                              VARCHAR2(50),
       emp_email                           VARCHAR2(70));
     
   TYPE emp_tab_type IS TABLE OF emp_rec
      INDEX BY VARCHAR2(11);
     
   emp_tab                                 emp_tab_type;

--   TYPE rcur IS REF CURSOR;
   
--   rcur_birthday                           rcur;

BEGIN
   FOR rec in (
   SELECT employee_id,
          employee_name,
          employee_email
   FROM employee
   WHERE employee_birth_date = TRUNC(SYSDATE)) LOOP
     emp_tab(rec.employee_id).emp_nm := rec.employee_name;
     emp_tab(rec.employee_id).emp_email := rec.emp_email;
   END LOOP;
   


END;  
0
 

Author Comment

by:akamarcel
ID: 18841212
The value of the employee IDs are VARCHAR.  

The sample I provided was just to illustrate what I am trying to do.  I am planning on calling a procedure that returns the em_tab_type

p_get_birhtday(rcur_birthday IN rcur, emp_tab OUT emp_tab_type)
AS


BEGIN

      **** Code the fetches the data from the cursor ref into the table ***


END;

The reason I would like to do it this way, is because the SQL is much more elaborate than the sample I have provided.  We often need to create the SQL Dynamically.  Sometimes we want to return the cursor ref, and then do some processing using the data that has been added to the table.

So, is there a way to fill my table from a ref cursor?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18841322
I feel that what is being expected is a little ambigous.
If you like to return em_tab_type then why do you need to have it index by varchar2?? Index it by integer and make the empno as an attribute of emp_rec.

Now, you say that you want to return a REF CURSOR instead. Well, whatever way you prepare the query, the only constraint is that the number of columns returned should be the same. Something like this.

declare
 l_cur sys_refcursor;
 <declare the record type here>;
 <declare the table type based on the record type>; -- All these can be put in a package for ease of declaration.
 <declare table type variable>
begin
 your_procedure(l_cur);
 fetch l_cur bulk collect into <table type variable>

 forall i in <table type variable>.first..<table type variable.last>
  insert into <table> values <table type variable>(i);

end;
/
0
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.

 

Author Comment

by:akamarcel
ID: 18843248
I apologize. Not tyring to be ambiguous.  I was just trying to keep my question simple.  I need/want to have my table type indexed by varchar2, because i want to pull values out of the table based on the indexed varchar2 value without having to loop through the table.  I want to use a ref cursor, because it will allow me to simplify my maintenance of the code I already have that works. In fact, it looks exactly like your first response.

If the answer to "How can I fill a table type indexed by VARCHAR2 from a REF CURSOR?"  is that you can't, then I will continue to use the method you listed in your first reponse.  However, if it IS possible,  I would like to see an example of how to do that.

Thank you for your time, I appreciate your efforts.







0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18846497
DECLARE
TYPE emp_rec IS RECORD (emp_nm VARCHAR2(50), emp_email VARCHAR2(70));
e1 emp_rec;
TYPE emp_tab_type IS TABLE OF emp_rec INDEX BY VARCHAR2(11);
emp_tab emp_tab_type;
TYPE rcur IS REF CURSOR;
rcur_birthday rcur;
BEGIN
 OPEN rcur_birthday FOR SELECT employee_id, employee_name, employee_email FROM employee WHERE employee_birth_date = TRUNC(SYSDATE);

open rcur_birthday;
LOOP
fetch rcur_birthday INTO e1;
exit when rcur_birthday%notfound;
emp_tab(e1.employee_name).employee_id := e1.employee_id;
emp_tab(e1.employee_name).employee_email := e1.employee_email;
END LOOP;
close rcur_birthday;
END;
/
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 125 total points
ID: 18848647
If I understood your requirement correctly
- you want to fetch the REF CURSOR directly into the associative array indexed by VARCHAR2
- and you want to use a bulk fetch

First point - is not possible
Second one - can be done using a work around. By first bulk fetching into another array and then feeding an array of type emp_tab_type. Do a test whether it is really giving any performace gain and is worth the effort. Code is something like:

DECLARE

   TYPE emp_rec IS RECORD
      (emp_nm                              VARCHAR2(50),
       emp_email                           VARCHAR2(70));
     
   TYPE emp_tab_type IS TABLE OF emp_rec
      INDEX BY VARCHAR2(11);
     
   emp_tab                                 emp_tab_type;

   TYPE rcur IS REF CURSOR;
   
   rcur_birthday                           rcur;
 
   type dummy_rec_type is record
      (emp_nm                              VARCHAR2(50),
       emp_email                           VARCHAR2(70),
       empno                               VARCHAR2(11) );

  type dummy_tbl_type is table of dummy_rec_type index by binary_integer;

  l_t1 dummy_tbl_type;

BEGIN
   OPEN rcur_birthday FOR
   SELECT employee_name,
          employee_email,
          employee_id
   FROM employee
   WHERE employee_birth_date = TRUNC(SYSDATE) ;

   fetch rcur_birthday bulk collect into l_t1; -- you can consider fetching in batches using LIMIT clause.

   for i in l_t1.first..l_t1.last loop
     emp_tab(l_t1(i).employee_id).emp_nm := l_t1(i).emp_nm;
     emp_tab(l_t1(i).employee_id).emp_email := l_t1(i).emp_email;
   end loop;

   --
END;  
0
 

Author Comment

by:akamarcel
ID: 18850749
Thanks Sujith.  
You have the first part of my requirements correct.  
  "- you want to fetch the REF CURSOR directly into the associative array indexed by VARCHAR2 "
The second part, I don't care about
 "- and you want to use a bulk fetch"

Your example of having the second table type will work, and I may implement that if I have to. I was trying to avoid creating two additional types if I did not have to.  But I think you have answered my question. It is not possible.  And that is OK.  Guess we can't have everything.

I was looking for something like Mohan has suggested.  Although I switched which field to set as the index

DECLARE
TYPE emp_rec IS RECORD (emp_nm VARCHAR2(50), emp_email VARCHAR2(70));
e1 emp_rec;
TYPE emp_tab_type IS TABLE OF emp_rec INDEX BY VARCHAR2(11);
emp_tab emp_tab_type;
TYPE rcur IS REF CURSOR;
rcur_birthday rcur;
BEGIN
 OPEN rcur_birthday FOR SELECT employee_id, employee_name, employee_email FROM employee WHERE employee_birth_date = TRUNC(SYSDATE);

open rcur_birthday;
LOOP
fetch rcur_birthday INTO e1;
exit when rcur_birthday%notfound;
emp_tab(e1.employee_id).employee_name:= e1.employee_name;
emp_tab(e1.employee_id).employee_email := e1.employee_email;
END LOOP;
close rcur_birthday;
END;

Whe I try this I get an error on compile:

PL/SQL: Statement ignored
PLS-00302: component 'employee_id' must be declared
PL/SQL: Statement ignored
PLS-00302: component employee_id must be declared

Which makes sense since the record variable I am trying to insert into dpes not contain this field. And that is what I was struggling with.  A way to fetch the values from the cursor ref into the table.  But it does not look possible.

This is my first post here, and I want to thank you for being patient with me. I appreciate the help.


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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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