CODEWITHSUNDEEP
oracle-databaseplsqlcollections
Rexam
Rexam

Reputation: 913

Collect multiple rows and store values in PL/SQL

I have the following query:

DECLARE
  type INV_TASK_GLOBAL is varray(10) of NUMBER(20,0);
  type STUDY_CASE_GLOBAL is varray(10) of NUMBER(20,0);

BEGIN
  SELECT T_INVESTIGATIONTASK.ID, T_STUDYCASE.ID 
     into INV_TASK_GLOBAL, STUDY_CASE_GLOBAL 

  FROM T_VALIDATIONCARRIER 
      left join T_ESTIMATIONOBJECT on T_VALIDATIONCARRIER.IA_ESTIMATIONOBJECT_ID = T_ESTIMATIONOBJECT.ID
      left join T_INVESTIGATIONTASK on T_ESTIMATIONOBJECT.INVESTIGATIONTASK_ID = T_INVESTIGATIONTASK.ID
      left join T_STUDYCASE on T_INVESTIGATIONTASK.STUDYCASE_ID = T_STUDYCASE.ID

  WHERE T_VALIDATIONCARRIER.ESTIMATIONOBJECT_ID = 940;

  dbms_output.Put_line('INVESTIGATIONTASK_ID: ' || INV_TASK_GLOBAL);
  dbms_output.Put_line('STUDYCASE_ID: ' || STUDY_CASE_GLOBAL);
END;

The compiler is telling me that the number specified in exact fetch is less than the rows returned. The fact is that I want those lines to be returned. To be specific: I would like to collect all the T_INVESTIGATIONTASK.ID and T_STUDYCASE.ID (one per each row which is captured by the WHERE clause), store them into the INV_TASK_GLOBAL and in STUDY_CASE_GLOBAL and then display all the values returned (separated maybe by a comma).
I may change the WHERE condition in the future but the maximum number of elements I expect to be returned for both variables is 10 anyway.
I know that I am using the varray datatype in a wrong way: I need some sort of cycle and a list/array datatype to store all the returned values in the INV_TASK_GLOBAL and STUDY_CASE_GLOBAL variables and then print the array on screen. Do you have any idea of how to accomplish it?

Upvotes: 0

Views: 2933

Answers (1)

Rexam
Rexam

Reputation: 913

After some tests the following code solved my problem:

DECLARE
  type collection_id is table of NUMBER(20,0);

  INV_TASK_GLOBAL collection_id := collection_id(10);
  STUDY_CASE_GLOBAL collection_id := collection_id(10);

BEGIN
  SELECT T_INVESTIGATIONTASK.ID, T_STUDYCASE.ID 
    BULK COLLECT into INV_TASK_GLOBAL, STUDY_CASE_GLOBAL 
  FROM T_VALIDATIONCARRIER 
     left join T_ESTIMATIONOBJECT on T_VALIDATIONCARRIER.IA_ESTIMATIONOBJECT_ID = T_ESTIMATIONOBJECT.ID
     left join T_INVESTIGATIONTASK on T_ESTIMATIONOBJECT.INVESTIGATIONTASK_ID = T_INVESTIGATIONTASK.ID
     left join T_STUDYCASE on T_INVESTIGATIONTASK.STUDYCASE_ID = T_STUDYCASE.ID
  WHERE T_VALIDATIONCARRIER.ESTIMATIONOBJECT_ID = 940;

  FOR indx IN 1 .. INV_TASK_GLOBAL.COUNT
  LOOP
     dbms_output.Put_line('INVESTIGATIONTASK_ID: ' || INV_TASK_GLOBAL(indx));
  END LOOP;

  FOR indx IN 1 .. STUDY_CASE_GLOBAL.COUNT
  LOOP
      dbms_output.Put_line('STUDY_CASE_ID: ' || STUDY_CASE_GLOBAL(indx));
  END LOOP;
END;

Upvotes: 1

Related Questions