Home » SQL & PL/SQL » SQL & PL/SQL » Add records in collection (Pl/sql/11.2.0.2.0., Win7)
Add records in collection [message #617976] Sun, 06 July 2014 02:16 Go to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi all

Appreciate if someone help me out. I tried to cover my scenario with Scott schema.
I have the following table structure.
drop table test21;
CREATE TABLE test21
AS SELECT * FROM emp
where 1<>2;

Sample Code
---------
Seq creation
CREATE SEQUENCE test21_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

DECLARE

  CURSOR c1 IS SELECT * FROM emp;

TYPE t_emp IS TABLE OF c1%rowtype;

v_emp t_emp:=t_emp();

g_limit number:=1000;

BEGIN

OPEN c1;

  loop fetch c1  BULK COLLECT INTO  v_emp LIMIT g_limit;
  exit WHEN v_emp.count=0;
    forall i IN v_emp.FIRST..v_emp.LAST
	INSERT INTO test21
	  (empno,
	   JOB,    
	   ename,
	   hiredate,
	   deptno
	  )

    	VALUES
    	  (
    	   test21_seq.nextval,
	   v_emp(i).JOB,    
           v_emp(i).ename,
           v_emp(i).hiredate,
           v_emp(i).deptno
    );
  END loop;

COMMIT;
close c1;
end;

--------
Rule
Now what I want is that if there is a deptno=20 then I need to create 2 records against deptno 20, something like below:
1st record
JOB='ITCLERK'
ename='ab'
hiredate='17-DEC-80'
deptno=20

2nd record

JOB='xxx'
ename='cd'
hiredate='17-NOV-80'
deptno=20
-------
Please let me know how come I do this?. I think I need to extend my collection before insert but not sure. Thanks in advance

[Updated on: Sun, 06 July 2014 02:18]

Report message to a moderator

Re: Add records in collection [message #617979 is a reply to message #617976] Sun, 06 July 2014 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I think I need to extend my collection before insert but not sure.


If you really want to do it in PL/SQL then you are right.
But, of course, this can be done in a single INSERT SELECT statement.

Re: Add records in collection [message #617980 is a reply to message #617979] Sun, 06 July 2014 04:22 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi Michel

I need to do it in by using plsql and specially collection. Can we do it? Thanks

[Updated on: Sun, 06 July 2014 04:22]

Report message to a moderator

Re: Add records in collection [message #618021 is a reply to message #617980] Sun, 06 July 2014 23:14 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Any help would be highly appreciated. Thanks in advance
Re: Add records in collection [message #618029 is a reply to message #618021] Mon, 07 July 2014 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you said is correct, if you know how to do it, WE are waiting for to show us: extend your collection.
What is your problem now to do it?

Re: Add records in collection [message #618032 is a reply to message #618029] Mon, 07 July 2014 00:16 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
ok. I will try and let u know. Thanks
Re: Add records in collection [message #618087 is a reply to message #618032] Mon, 07 July 2014 11:24 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
I did something like below. Please let me know if the below code required some improvements. Thanks

set serveroutput on
DECLARE 
  CURSOR c1 IS SELECT * FROM emp;
TYPE t_emp IS TABLE OF c1%rowtype;
v_emp t_emp:=t_emp();

g_limit number:=1000;
BEGIN
OPEN c1;
  loop fetch c1  BULK COLLECT INTO  v_emp LIMIT g_limit;
  exit WHEN v_emp.count=0;
  FOR i IN 1..v_emp.count
    loop
      IF v_emp(i).deptno=20 THEN
      --Create 1st record
      v_emp.EXTEND;
        v_emp(v_emp.LAST).deptno  :=v_emp(i).deptno;
        v_emp(v_emp.LAST).empno   :=v_emp(i).empno;
        v_emp(v_emp.LAST).ename   :='ab';
        v_emp(v_emp.LAST).JOB     :='ITCLERK';
        v_emp(v_emp.LAST).hiredate:='17-DEC-80';
      --Create 2nd record
      v_emp.EXTEND;
        v_emp(v_emp.LAST).deptno  :=v_emp(i).deptno;
        v_emp(v_emp.LAST).empno   :=v_emp(i).empno;
        v_emp(v_emp.LAST).ename   :='cd';
        v_emp(v_emp.LAST).JOB     :='xxx';
        v_emp(v_emp.LAST).hiredate:='17-NOV-80';
      END IF;
    end loop;
    forall i IN 1..v_emp.count
      INSERT INTO test21
      (empno,
       JOB,    
       ename,
       hiredate,
       deptno
       )
      VALUES
      (
      v_emp(i).empno,
      v_emp(i).JOB,    
      v_emp(i).ename,
      v_emp(i).hiredate,
      v_emp(i).deptno
      );
  END loop;
COMMIT;
close c1;
end;


Re: Add records in collection [message #618089 is a reply to message #618087] Mon, 07 July 2014 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to add an exception handler to close the cursor if an exception occurs and you should move the current close just after the loop.
"hiredate" is a date and '17-DEC-80' is a string (and you should use TO_DATE with a format mask).
If there currently are several rows in department 20 you will insert several times the supplement rows.
(ALso in real world empno is a PK, so you can't insert a new employee with the same empno.)

Apart from that, the method is correct.

Forget: you don't need to initialize the collection when you use it with BULK COLLECT, this later do it.

Re: Add records in collection [message #618090 is a reply to message #618089] Mon, 07 July 2014 12:06 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks for the reply Michel, I will definitely follow all you mentioned instructions but I'm wondering that if you explain your below statement.

Forget: you don't need to initialize the collection when you use it with BULK COLLECT, this later do it.


I use nested table and if I disable initialize the collection then I will get error. Please advice
Re: Add records in collection [message #618095 is a reply to message #618090] Mon, 07 July 2014 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me:
SQL> DECLARE
  2    CURSOR c1 IS SELECT * FROM emp;
  3  TYPE t_emp IS TABLE OF c1%rowtype;
  4  v_emp t_emp;
  5  g_limit number:=1000;
  6  BEGIN
  7  OPEN c1;
  8    loop fetch c1  BULK COLLECT INTO  v_emp LIMIT g_limit;
  9    exit WHEN v_emp.count=0;
 10    FOR i IN 1..v_emp.count
 11      loop
 12        IF v_emp(i).deptno=20 THEN
 13        --Create 1st record
 14        v_emp.EXTEND;
 15          v_emp(v_emp.LAST).deptno  :=v_emp(i).deptno;
 16          v_emp(v_emp.LAST).empno   :=v_emp(i).empno;
 17          v_emp(v_emp.LAST).ename   :='ab';
 18          v_emp(v_emp.LAST).JOB     :='ITCLERK';
 19          v_emp(v_emp.LAST).hiredate:='17-DEC-80';
 20        --Create 2nd record
 21        v_emp.EXTEND;
 22          v_emp(v_emp.LAST).deptno  :=v_emp(i).deptno;
 23          v_emp(v_emp.LAST).empno   :=v_emp(i).empno;
 24          v_emp(v_emp.LAST).ename   :='cd';
 25          v_emp(v_emp.LAST).JOB     :='xxx';
 26          v_emp(v_emp.LAST).hiredate:='17-NOV-80';
 27        END IF;
 28      end loop;
 29      forall i IN 1..v_emp.count
 30        INSERT INTO test21
 31        (empno,
 32         JOB,
 33         ename,
 34         hiredate,
 35         deptno
 36         )
 37        VALUES
 38        (
 39        v_emp(i).empno,
 40        v_emp(i).JOB,
 41        v_emp(i).ename,
 42        v_emp(i).hiredate,
 43        v_emp(i).deptno
 44        );
 45    END loop;
 46  close c1;
 47  end;
 48  /

PL/SQL procedure successfully completed.

Re: Add records in collection [message #618097 is a reply to message #618095] Mon, 07 July 2014 12:25 Go to previous message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Got it. Thanks and really appreciate your guidance.
Previous Topic: Autocomplete
Next Topic: ORA-29285: file write error
Goto Forum:
  


Current Time: Fri Apr 26 19:51:21 CDT 2024