Fetch from REF CURSOR Into Sting-Indexed Associative Array

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;  

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?

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;
/

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.

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;
/

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.