Home » SQL & PL/SQL » SQL & PL/SQL » Collections in plsql - syntax (Oracle 11.2.0.4.0)
Collections in plsql - syntax [message #652291] Mon, 06 June 2016 07:25 Go to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Hi guys...

I really get confused over how to define a variable of a collection type being created.

Eg 1:
declare
TYPE orders_test_tab IS TABLE OF orders_all%ROWTYPE;
l_tab orders_test_tab;
...
...
end;

Eg 2:
declare
TYPE orders_test_tab IS TABLE OF orders_all%ROWTYPE;
l_tab orders_test_tab:=orders_test_tab();
...
...
end;
--------------------------------------------------------------
What is the difference in the above two declarations of the variable l_tab? And how to decide which declaration to be used and when?

Please clarify.
Re: Collections in plsql - syntax [message #652293 is a reply to message #652291] Mon, 06 June 2016 07:49 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In the first example the variable only gets declared. In the second example something else happens.

Observe these different error messages for example:

SQL> declare
  2    TYPE orders_test_tab IS TABLE OF integer;
  3    l_tab orders_test_tab;
  4  BEGIN
  5    Dbms_Output.put_line(l_tab(1));
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5


SQL>
SQL>
SQL> declare
  2  TYPE orders_test_tab IS TABLE OF integer;
  3  l_tab orders_test_tab:=orders_test_tab();
  4  BEGIN
  5    Dbms_Output.put_line(l_tab(1));
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5


SQL>

[Updated on: Mon, 06 June 2016 07:49]

Report message to a moderator

Re: Collections in plsql - syntax [message #652304 is a reply to message #652291] Mon, 06 June 2016 14:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
A collection can be declared and initialized separately or it can be declared and initialized at the same time or, depending on how you plan to populate it, you may not need to initialize it. Please see the demonstrations below that all produce the same output. In general, the last one is less code to write and runs faster, but you may not always be able to use that method, depending on where the data that you are using to populate your collection comes from. In addition, whatever your whole process is, if you can do it in SQL without PL/SQL, that is generally preferable.

-- separate declaration and initialization:
SCOTT@orcl_12.1.0.2.0> declare
  2    TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
  3    -- declare:
  4    l_tab orders_test_tab;
  5  begin
  6    -- initialize:
  7    l_tab := orders_test_tab();
  8    -- populate:
  9    for r in (select * from dept order by deptno) loop
 10  	 l_tab.extend;
 11  	 l_tab(l_tab.last).deptno := r.deptno;
 12  	 l_tab(l_tab.last).dname := r.dname;
 13  	 l_tab(l_tab.last).loc := r.loc;
 14    end loop;
 15    -- output:
 16    for i in 1 .. l_tab.count loop
 17  	 dbms_output.put_line
 18  	   (l_tab(i).deptno || ' ' ||
 19  	    RPAD (l_tab(i).dname, 14)  || ' ' ||
 20  	    l_tab(i).loc);
 21    end loop;
 22  end;
 23  /
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

PL/SQL procedure successfully completed.

-- declaration and initialization at the same time:
SCOTT@orcl_12.1.0.2.0> declare
  2    TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
  3    -- declare and initialize:
  4    l_tab orders_test_tab := orders_test_tab();
  5  begin
  6    -- populate:
  7    for r in (select * from dept order by deptno) loop
  8  	 l_tab.extend;
  9  	 l_tab(l_tab.last).deptno := r.deptno;
 10  	 l_tab(l_tab.last).dname := r.dname;
 11  	 l_tab(l_tab.last).loc := r.loc;
 12    end loop;
 13    -- output:
 14    for i in 1 .. l_tab.count loop
 15  	 dbms_output.put_line
 16  	   (l_tab(i).deptno || ' ' ||
 17  	    RPAD (l_tab(i).dname, 14)  || ' ' ||
 18  	    l_tab(i).loc);
 19    end loop;
 20  end;
 21  /
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

PL/SQL procedure successfully completed.

-- declaration, with no initialization necessary due to population method:
SCOTT@orcl_12.1.0.2.0> declare
  2    TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
  3    -- declare:
  4    l_tab orders_test_tab;
  5  begin
  6    -- populate (no separate initialization is necessary):
  7    select * bulk collect into l_tab from dept order by deptno;
  8    -- output:
  9    for i in 1 .. l_tab.count loop
 10  	 dbms_output.put_line
 11  	   (l_tab(i).deptno || ' ' ||
 12  	    RPAD (l_tab(i).dname, 14)  || ' ' ||
 13  	    l_tab(i).loc);
 14    end loop;
 15  end;
 16  /
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

PL/SQL procedure successfully completed.
Re: Collections in plsql - syntax [message #652345 is a reply to message #652304] Tue, 07 June 2016 11:21 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Thanks all for responding!
So it's just about declaring and initialising and nothing else just like below?

declare
a number;
begin
a:=10;
..
..
end;

declare
a number:=10;
begin
..
..
end;

declare
a number;
begin
select 10 into a from dual;
end;
Re: Collections in plsql - syntax [message #652346 is a reply to message #652345] Tue, 07 June 2016 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
So it's just about declaring and initialising and nothing else just like below?


Yes, please read [How to use [code] tags and make your code easier to read.

Re: Collections in plsql - syntax [message #652350 is a reply to message #652346] Tue, 07 June 2016 11:46 Go to previous message
adfnewbie
Messages: 54
Registered: January 2016
Member
Thanks! It was a simple one so didn't bother to do the formatting! Will do all the time from now on!

Thanks again!
Previous Topic: Number to words
Next Topic: behaviour of to_timestamp and time zones
Goto Forum:
  


Current Time: Sun Aug 01 18:44:22 CDT 2021