CODEWITHSUNDEEP
oracle-databaseplsqlcollections
Vidya Pandey
Vidya Pandey

Reputation: 211

Population of object type from other object type oracle

I have requirement where i need to append data to already populated object type. Data to be appended, depends on data which is already populated in object type. Can anyone please help me with this.

I tried below approach which is working fine. But I want to know if there is better way of doing it.

  1. Step 1 : Declare and populate object type 1
  2. Step 2 : Declare another object type 2 and Populate it using object type 1
  3. Step 3 : Declare one more object type 3 for final result-set and populate using combine data of object type 1 and object type 2

Also i need to assign uniq id to each record populated (for which I am using rownum pseudo column )

Below is code that i tried which is working fine. Please note that, in actual requirement, i have many attributes in object type and many filters and joins while populating object type. Below is just simplified code to know the solution.

-- table EMPLOYEE
CREATE TABLE EMPLOYEE
(
     ENAME          VARCHAR2(128)  ,
     EADDRESS       VARCHAR2(200)  ,
     ESTATUS        VARCHAR2(11)   ,
     PARENT_NAME    VARCHAR2(128)   
);

-- Object
CREATE OR REPLACE TYPE OBJ_USER_ACTIVITIES AS OBJECT
(
     UNIQ_ID        NUMBER(9)      ,
     EMPNAME        VARCHAR2(128)  ,
     ADDRESS        VARCHAR2(200)  ,
     STATUS         VARCHAR2(11)   ,
     PARENT_NAME    VARCHAR2(128)            
);

-- Type
CREATE OR REPLACE TYPE TAB_USER_ACTIVITIES IS TABLE OF OBJ_USER_ACTIVITIES;

-- Procedure
CREATE OR REPLACE PROCEDURE Test_Tickets
IS 
     TEMP_USER_ACTIVITIES_1        TAB_USER_ACTIVITIES := TAB_USER_ACTIVITIES();
     TEMP_USER_ACTIVITIES_2        TAB_USER_ACTIVITIES := TAB_USER_ACTIVITIES();
     TEMP_USER_ACTIVITIES_FINAL    TAB_USER_ACTIVITIES := TAB_USER_ACTIVITIES();

     i integer;

BEGIN

     -- First resultset
     SELECT OBJ_USER_ACTIVITIES
     (ROWNUM, EMPNAME, ADDRESS, STATUS, PARENT_NAME)
     BULK COLLECT INTO TEMP_USER_ACTIVITIES_1
     FROM
     (
          SELECT    ENAME AS EMPNAME, EADDRESS AS ADDRESS, ESTATUS AS STATUS, PARENT_NAME
          FROM      EMPLOYEE
          --WHERE   SOME FILTER
     );

     i := TEMP_USER_ACTIVITIES_1.COUNT;

     -- Build second resultset depending on valus of first resultset
     SELECT OBJ_USER_ACTIVITIES
     ( i + ROWNUM, EMPNAME, ADDRESS, STATUS, PARENT_NAME )
     BULK COLLECT INTO TEMP_USER_ACTIVITIES_2
     FROM
     (
          SELECT    ENAME AS EMPNAME, EADDRESS AS ADDRESS, ESTATUS AS STATUS, PARENT_NAME
          FROM      EMPLOYEE
          WHERE     ENAME IN ( SELECT PARENT_NAME FROM TABLE(TEMP_USER_ACTIVITIES_1) WHERE PARENT_NAME IS NOT NULL )           
     );


     -- FINAL resultset combining above two resulset
     SELECT OBJ_USER_ACTIVITIES
     ( UNIQ_ID, EMPNAME, ADDRESS, STATUS, PARENT_NAME )
     BULK COLLECT INTO TEMP_USER_ACTIVITIES_FINAL
     FROM
     (
          SELECT    UNIQ_ID, EMPNAME, ADDRESS, STATUS, PARENT_NAME
          FROM      TABLE(TEMP_USER_ACTIVITIES_1)
          UNION
          SELECT    UNIQ_ID, EMPNAME, ADDRESS, STATUS, PARENT_NAME
          FROM      TABLE(TEMP_USER_ACTIVITIES_2)
     );

END;

Upvotes: 0

Views: 218

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11596

There is a great set of nested table operators available to you for this. HEre is an example (with some of the other operators commented out just for completeness)

SQL> create or replace type MY_OBJ as object ( x int, y int );
  2  /

Type created.

SQL>
SQL> create or replace type MY_OBJ_LIST as table of my_obj
  2  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2    t1 my_obj_list :=
  3      my_obj_list(
  4        my_obj(1,2),
  5        my_obj(3,4),
  6        my_obj(5,6));
  7
  8    t2 my_obj_list :=
  9      my_obj_list(
 10        my_obj(7,8),
 11        my_obj(9,10),
 12        my_obj(11,12));
 13
 14    t3  my_obj_list := my_obj_list();
 15  begin
 16   t3 :=  t1 MULTISET UNION t2;
 17
 18   dbms_output.put_line('I now have '||t3.count||' elements');
 19
 20  --  t3 := t1 multiset union t2;
 21  --  t3 := t1 multiset union distinct t2;
 22  --  t3 := t1 multiset intersect t2;
 23  --  t3 := t1 multiset intersect distinct t2;
 24  --  t3 := set(t2);
 25  --  t3 := t1 multiset except t2;
 26  --  t3 := t1 multiset except distinct t2;
 27
 28  end;
 29  /
I now have 6 elements

PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions