Hello all,
I am on 12c.
The question I have is this:
Is there any way we can return an entire row in the bulk collect instead of each individual column
In the below code, I am deleting those employees in the emp table whose salary is more than 2000 and then populating the same in emp_deleted table.
Eg:
create table emp1 as
select * from emp;
create table emp_deleted
as
select empno, ename, sal, deptno
from emp
where 1 = 2;
declare
type emp_rec is record
(
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type,
deptno emp.deptno%type
);
type emp_ntt is table of emp_rec;
--type emp_ntt is table of emp%rowtype; --Say I declare it like this
emp_coll emp_ntt;
begin
delete from emp1 where sal > 2000
returning empno, ename, sal, deptno
--returning row --Instead of mentioning the individual column names like above, I want to return a row itself
bulk collect into emp_coll;
dbms_output.put_line('No of emps: '||emp_coll.count);
forall i in emp_coll.first..emp_coll.last
insert into emp_deleted values emp_coll(i);
end;
/
How do I code it ?
Regards,
Ranagal