troubleshooting Question

Passing values from one tab type to another

Avatar of Swadhin Ray
Swadhin RayFlag for United States of America asked on
Oracle Database
4 Comments1 Solution394 ViewsLast Modified:
Hello Experts,

I have a code below where I want to pass : L_OBJ_NEW:=L_OBJ ;


I have a object type as below code which exists in my DB:

CREATE OR REPLACE TYPE XX_ASGN_REC_NEW IS OBJECT (ORG_ID NUMBER, SOURCE_ID VARCHAR2(50));
 
create or replace TYPE xx_asgn_tab_new AS TABLE OF XX_ASGN_REC_NEW;
 
CREATE OR REPLACE TYPE xx_asgn_rec    AS OBJECT (org_id number, source_id varchar2(50));
 
CREATE OR REPLACE TYPE xx_asgn_tab AS TABLE OF xx_asgn_rec;
 

Table script :
CREATE TABLE operating_units 
(
org_id NUMBER ,
source_id varchar2(50),
created_date date
);


insert into operating_units values (1,'MD-001','01-JUL-1990');
insert into operating_units values (2,'JKD-002','31-JAN-1998');
insert into operating_units values (3,'SUN-003','05-JUL-2002');
insert into operating_units values (4,'SL-004','12-JAN-2012');

commit;

Finally the code where I want to pass the values from one tab type to another for "L_OBJ_NEW:=L_OBJ " is as below:

DECLARE
  L_OBJ XX_ASGN_TAB ;
  l_obj_new xx_asgn_tab_new;
  CURSOR c1
  IS
    SELECT CAST(MULTISET
      ( SELECT org_id,source_id FROM operating_units
      ) AS xx_asgn_tab)
    FROM DUAL;
  BEGIN
    OPEN c1 ;
    FETCH C1 INTO L_OBJ;
    IF L_OBJ.EXISTS(1) THEN
      --L_OBJ_NEW:=L_OBJ; -- want to pass here
      DBMS_OUTPUT.PUT_LINE('Record exists' );
    END IF ;
    DBMS_OUTPUT.PUT_LINE('Total record count is ='||L_OBJ.COUNT);
/* So rather using the l_obj I want to use L_OBJ_NEW tab type */
    FOR I IN 1 .. L_OBJ.COUNT
    LOOP
      DBMS_OUTPUT.PUT_LINE(L_OBJ(I).ORG_ID || ' - ' || L_OBJ(i).SOURCE_ID);
    END LOOP;
    CLOSE c1;
  END; 
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros