Declare nested table in PLSQL function

Question

I have a need to query a db; fetch from the cursor and then put this data into a nested table. Put this nested table into another nested table indexed by an id.

The code is below:

   FUNCTION get_all_dna_sample_data (db_numbers IN TYPES.batch_numbers)
        RETURN TYPES.all_sample_tag_data
   IS

      TYPE REVERSE_BATCH_LOOKUP IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(256);
      reverse_batch_numbers  REVERSE_BATCH_LOOKUP;
      TYPE CUR_TYP is REF CURSOR; 
      c  CUR_TYP;
      db_ids          BATCH_IDS;
      sample_id       VARCHAR2(256);
      db_id           VARCHAR2(256);
      dna_batch_id    VARCHAR2 (256);
      construct_id    VARCHAR2 (256);
      vr_ids          VARCHAR2 (256);
      project_names   VARCHAR2 (256);
      concentration   NUMBER;
      volume          NUMBER;
      all_tag_data    TYPES.all_sample_tag_data;
      sample_tag_data        TYPES.TAG_DATA;
      sql_query       VARCHAR2 (1024);

      loop_count      NUMBER;
   BEGIN

        db_ids := BATCH_IDS();
        loop_count := 1;
        sample_id := db_numbers.FIRST;
        UTILS.DEBUG_OUTPUT('Setting table size to ' || TO_CHAR(db_numbers.COUNT));
        db_ids.EXTEND(db_numbers.COUNT);
        UTILS.DEBUG_OUTPUT('Table size is ' || TO_CHAR(db_ids.COUNT));

        LOOP
          EXIT WHEN sample_id IS NULL;
          UTILS.DEBUG_OUTPUT(sample_id || ' ' || db_numbers(sample_id));
          db_ids(loop_count) := db_numbers(sample_id);
          sample_id := db_numbers.NEXT(sample_id);
          reverse_batch_numbers(db_numbers(sample_id)) := sample_id;
          loop_count := loop_count + 1;
        END LOOP;

      sql_query := 'SELECT dna_batch_id,
             construct_id,
             vr_ids,
             project_names,
             concentration,
             volume
      FROM ' || ucb_dna_data_table || ' d ' 
      || ' WHERE d.dna_batch_id MEMBER OF (:db_coll)';

      UTILS.DEBUG_OUTPUT ('query is ' || sql_query);
      OPEN c FOR sql_query USING db_ids;

      LOOP
        FETCH c INTO dna_batch_id, construct_id, vr_ids, 
            project_names, concentration, volume;
        --here we need to create a new tag_data collection
        sample_tag_data := TYPES.TAG_DATA();
        sample_tag_data(TYPES.dna_batch_id_tag_name) := dna_batch_id;
        sample_tag_data(TYPES.construct_id_tag_name) := construct_id;
        sample_tag_data(TYPES.vr_ids_tag_name) := vr_ids;
        sample_tag_data(TYPES.project_names_tag_name) := project_names;
        sample_tag_data(TYPES.concentration_tag_name) := TO_CHAR(concentration);
        sample_tag_data(TYPES.volume_tag_name) := TO_CHAR(volume);
        all_tag_data(reverse_batch_numbers(dna_batch_id)) := tag_data;
        EXIT WHEN c%NOTFOUND;
      END LOOP;

      RETURN all_tag_data;
  END;

Basically; it is a simple question after all of this - how do I create a new nested table halfway through the code so I can keep creating new collection objects and put them into the returning data. Simply calling sample_tag_data := TYPES.TAG_DATA(); does work.

Thank you in advance for your help.

What error do you get? How is tag_data defined? From how you're using it, it looks like a record type, not a nested table. — Alex Poole
– Alex Poole, Commented Apr 28, 2016 at 23:29

Mottor · Accepted Answer · 2016-04-29 09:32:10Z

Look at Using PL/SQL Collections and Records The question is what is TYPES.TAG_DATA? If it is an Index-By Table (table of varchar2(...) index by varchar2(...)), just remove this

sample_tag_data := TYPES.TAG_DATA ();

and it should work, bit the data will be replaced on every fetch?! If you want to store sample_tag_data into all_tag_data (and all_tag_data is an Index-By table) on every fetch, add new variable

null_tag_data TYPES.TAG_DATA;

change the 'sample_tag_data := TYPES.TAG_DATA ();' with:

sample_tag_data := null_tag_data;

and 'all_tag_data (reverse_batch_numbers (dna_batch_id)) := tag_data;' with:

all_tag_data (reverse_batch_numbers (dna_batch_id)) := sample_tag_data;

If you use nested tables, you cannot use them like index-by table. They do not have index.

A small sample, to illustrate, what I mean:

DECLARE
   TYPE t_tag_names IS RECORD
   (
      dna_batch_id_tag_name    VARCHAR2 (40),
      construct_id_tag_name    VARCHAR2 (40),
      vr_ids_tag_name          VARCHAR2 (40),
      project_names_tag_name   VARCHAR2 (40)
   );

   TYPE t1 IS TABLE OF NUMBER
      INDEX BY VARCHAR2 (20);

   TYPE t2 IS TABLE OF t1
      INDEX BY VARCHAR2 (20);

   tag_names   t_tag_names;
   x           t1;
   null_x      t1;
   y           t2;
BEGIN
   tag_names.dna_batch_id_tag_name := 'batch_tag';
   tag_names.construct_id_tag_name := 'construct';
   tag_names.vr_ids_tag_name := 'vr';
   tag_names.project_names_tag_name := 'project';

   FOR i IN 1 .. 3
   LOOP
      x := null_x;
      x (tag_names.dna_batch_id_tag_name) := 5 * i;

      IF MOD (i, 2) <> 0 THEN
         x (tag_names.construct_id_tag_name) := 2 * i;
      END IF;

      x (tag_names.vr_ids_tag_name) := i;
      x (tag_names.project_names_tag_name) := 7 * i;
      y (TO_CHAR (i)) := x;
   END LOOP;

   FOR i IN 1 .. 3
   LOOP
      x := y (i);
      DBMS_OUTPUT.put (tag_names.dna_batch_id_tag_name || ':' || x (tag_names.dna_batch_id_tag_name) || '   ');

      IF x.EXISTS (tag_names.construct_id_tag_name) THEN
         DBMS_OUTPUT.put (tag_names.construct_id_tag_name || ':' || x (tag_names.construct_id_tag_name) || '   ');
      END IF;

      DBMS_OUTPUT.put_line (tag_names.vr_ids_tag_name || ':' || x (tag_names.vr_ids_tag_name) || '   ' || tag_names.project_names_tag_name || x (tag_names.project_names_tag_name));
   END LOOP;
END;

osama yaccoub · Accepted Answer · 2016-04-28 23:39:52Z

0

may be you mean :

all_tag_data.EXTEND;
all_tag_data (all_tag_data.LAST) := sample_tag_data ;

answered Apr 28, 2016 at 23:39

osama yaccoub

1,9062 gold badges24 silver badges52 bronze badges

Collectives™ on Stack Overflow

Declare nested table in PLSQL function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related