0

I have a situation where I want to have a collection, actually in this case it is a nest collection (Associative array) that I would like to index by varchar so that I can access it by a field element instead of iterating to find a value. The only thing is I don't want to run into the NO_DATA_FOUND error when I try to access a position that does not exist.

So is there a way that I can dynamically take a record type that has elements such as

TYPE sample_emp_rec IS RECORD (name   VARCHAR2(30),
                               dob    DATE,
                               position VARCHAR2(30));

THEN initialize a collection:

 TYPE sample_emp_elem_typ IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(2);
 sample_emp_elem_tbl sample_emp_elem_typ;

 FOR element_name IN sample_emp_rec%ROWTYPE LOOP
     sample_emp_elem_tbl(element_name) := NULL;
 END LOOP;

Then I assign some values

sample_emp_elem_tbl(name) := 'John';
sample_emp_elem_tbl(position) 'Boss';

Then I try to access the non-existent element

 CASE WHEN sample_emp_elem_tbl(dob) = to(date('01/01/2018','mm/dd/yyyy') 
      THEN 'do something'
 END CASE;

If I hadn't initialized that position, I would get a no data found but in this case I would just get null.

Am I dreaming that something like this is possible. Is there a better way to do this?

One thing I could do is always check for the existence but that didn't seem good like

 CASE WHEN sample_emp_elem_tbl(dob).EXISTS AND sample_emp_elem_tbl(dob) 
      = to(date('01/01/2018','mm/dd/yyyy') 
      THEN 'do something'
 END CASE;

That being said, I would still like to know dynamically what elements are in the current record. I will potentially have dozens of record types to work with.

programmerNOOB
  • 121
  • 3
  • 19
  • It's hard to figure out what you mean from the bits of invalid code you've shown. Are you saying you want to populate `sample_emp_elem_tbl ` with the field names from `sample_emp_rec`; and then somehow use that to retrieve and validate data from a record instance? I don't think this is possible but I don't understand why you'd want to do it or what it would achieve, so perhaps if you can explain what your underlying goal is it might shed some light on what you mean, and alternatives. – Alex Poole Jan 18 '18 at 18:09
  • The goal is to dynamically fill a collection indexed by varchar by using the elements of a record that is defined at the package level. In the case above, name, position and dob would be indexes which are elements of the record – programmerNOOB Jan 18 '18 at 21:20
  • I did not lay this out in my thoughts correctly. I will try again, explaining what the ultimate goal is any my initial thoughts on how to achieve. – programmerNOOB Jan 19 '18 at 15:56

0 Answers0