How to use PL/SQL Record Datatype in nested table?

Question

I have a nested table which is holding a record datatype. So I tried:

DECLARE
  TYPE my_nested_table   IS TABLE OF ot.employee%ROWType;
  var_nt  my_nested_table :=  my_nested_table ();

  cursor cur is 
  select * from ot.employee;

BEGIN
 for i in cur
 loop
 var_nt.extend;
  end loop;

END;
/

The table employee has empno,empname,job columns. Is the process I am folowing is wrong or i can store this record in nested table?I am not getting any error compiling this code.how to add all those cursor values to the nested table variable and print them?

how to add all those cursor values to the nested table variable and print them? — Random guy
– Random guy, Commented Nov 5, 2019 at 17:00
Please see How to Ask in the help section. If you follow that as a template for your question you greatly increase your chance of getting satisfactory answer. In particular here what have you tried and what did you get vs what you expected. — Belayer
– Belayer, Commented Nov 5, 2019 at 17:04

XING · Accepted Answer · 2019-11-06 04:47:40Z

how to add all those cursor values to the nested table variable and print them?

Using LOOP you can assign values one by one as below: Using Nested Table

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype;
  cursor cur is 
        select *
        from employee;

  var_nt   my_nested_table := my_nested_table ();
BEGIN
  for i in cur 
  loop
    var_nt.extend;        
    var_nt(var_nt.count)   := i; -- Assignment 
    --Printing. You can add the columns you want
    dbms_output.put_line(i.empno ||'-'|| i.ename||'-'||i.sal||'-'|| i.deptno);
  end loop;
END;
/

Using Associative Array: - Which could save you from initializing and extending

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype index by pls_integer;
  cursor cur is 
        select *
        from employee;

  var_nt   my_nested_table; -- No need to initialize
BEGIN
  for i in cur loop
    --var_nt.extend; -- No need to extend
    var_nt(var_nt.count)   := i;
    dbms_output.put_line(i.empno ||'-'|| i.ename||'-'||i.sal||'-'|| i.deptno);
  end loop;
END;
/

Secondly You can avoid the loop and fetch the values directly in Nested table as shown below:

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype;
  var_nt   my_nested_table := my_nested_table ();
BEGIN
  select * 
  BULK COLLECT INTO  var_nt
  from employee;

  for i in 1..var_nt.count Loop
    dbms_output.put_line('Employee No. - '||var_nt(i).empno||'  '||'Employee Name - '||var_nt(i).ename);
  end loop;

END;
/

VBoka · Accepted Answer · 2019-11-05 17:50:45Z

0

Not sure if I understand but you can do this:

DECLARE

  CURSOR c_data IS
    SELECT *
    FROM employee;
  tmp_event c_data%rowtype;

BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data INTO tmp_event;
    EXIT WHEN c_data%NOTFOUND;
    dbms_output.put_line(tmp_event.id);
  END LOOP;
  CLOSE c_data;
END;
/

Here is the DEMO

answered Nov 5, 2019 at 17:50

VBoka

9,0933 gold badges19 silver badges27 bronze badges

2 Comments

Random guy Over a year ago

cant i put cursor value in nested table where nested table is %rowtype?

VBoka Over a year ago

Yes you can and I see @XING did it in his first example already... Here is the demo of that : dbfiddle.uk/…

Collectives™ on Stack Overflow

How to use PL/SQL Record Datatype in nested table?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related