Home » SQL & PL/SQL » SQL & PL/SQL » Assigning one nested table to other in same position (Oracle 12c 2)
Assigning one nested table to other in same position [message #683775] Sat, 20 February 2021 11:13 Go to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to assign the values from one nested table to other in same position, pleas suggest how to do?
The actual requiremnt is as follows,
DECLARE
type rec
IS
  record
  (
    empno NUMBER,
    ename VARCHAR2(20),
    JOB   VARCHAR2(20));
type tab
IS
  TABLE OF rec;
  tab1 tab;
  tab2 tab;
begin
  --14 rows with empno
  select empno,null,null bulk collect into tab2 from emp ;
  --3 records with empno
  select empno,ename,job bulk collect into tab1 from emp where job='MANAGER';
  --need to assign same 3 ename records from tab1 to tab2 against same empno
  --same need to perform 3 job records from tab1 to tab2 agains same empno for job CLERK
  FOR i IN tab1.first..tab1.last
  loop
    tab2(tab1.empno).ename:=tab1(empno).ename;
  end loop;
  
  FOR i IN tab2.first..tab2.last
  LOOP
    dbms_output.put_line(tab2(i).ename||' '||tab2(i).empno);
  END LOOP;
END;

Thank You
Re: Assigning one nested table to other in same position [message #683776 is a reply to message #683775] Sat, 20 February 2021 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your statements are inconsistent: is this against same position or against same empno? It can't be both (or by luck or assigning with a specific order).


What does mean "same need to perform 3 job records from tab1 to tab2 agains same empno for job CLERK"?
Provide the result you want in the end.

Re: Assigning one nested table to other in same position [message #683781 is a reply to message #683775] Sun, 21 February 2021 06:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3063
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ideally you should run single select:

select  empno,
        case job when 'MANAGER' then ename end ename,
        case job when 'MANAGER' then job end job
  bulk  collect
  into  tab1
  from emp;
Otherwise:

SET SERVEROUTPUT ON
DECLARE
type rec
IS
  record
  (
    empno NUMBER,
    ename VARCHAR2(20),
    JOB   VARCHAR2(20));
type tab
IS
  TABLE OF rec;
  tab1 tab;
  tab2 tab;
type list_by_empno
  is table of number
     index by pls_integer;
v_list_by_empno list_by_empno;
begin
  --14 rows with empno
  select empno,null,null bulk collect into tab2 from emp ;
  --3 records with empno
  select empno,ename,job bulk collect into tab1 from emp where job='MANAGER';
  --need to assign same 3 ename records from tab1 to tab2 against same empno
  --same need to perform 3 job records from tab1 to tab2 agains same empno for job CLERK
  
  FOR i IN tab2.first..tab2.last
  LOOP
    v_list_by_empno(tab2(i).empno) := i;
  END LOOP;
  FOR i IN tab1.first..tab1.last
  loop
    tab2(v_list_by_empno(tab2(i).empno)).ename:=tab1(i).ename;
    tab2(v_list_by_empno(tab2(i).empno)).job:=tab1(i).job;
  end loop;

  FOR i IN tab2.first..tab2.last
  LOOP
    dbms_output.put_line(tab2(i).job||' '||tab2(i).ename||' '||tab2(i).empno);
  END LOOP;
END;
/
MANAGER JONES 7369
MANAGER BLAKE 7499
MANAGER CLARK 7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

SQL>
SY.
Re: Assigning one nested table to other in same position [message #683782 is a reply to message #683781] Sun, 21 February 2021 12:19 Go to previous messageGo to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Thank you SY you have given the answer what I have expected. Your second option is my requirement. But can we manage the first for loop with the first select bulk collect statement only if possible. I think this is assigning based on empno values to other table.

[Updated on: Sun, 21 February 2021 12:20]

Report message to a moderator

Re: Assigning one nested table to other in same position [message #683785 is a reply to message #683782] Sun, 21 February 2021 16:07 Go to previous message
Solomon Yakobson
Messages: 3063
Registered: January 2010
Location: Connecticut, USA
Senior Member
No. Sparse bulk collect isn't supported.

SY.
Previous Topic: Trigger on Table (4 merged)
Next Topic: Space is replaced by xA0 in SQL Query
Goto Forum:
  


Current Time: Mon Jun 21 21:48:28 CDT 2021