OraFAQ Forum: SQL & PL/SQL » Collections in plsql

Home » SQL & PL/SQL » SQL & PL/SQL » Collections in plsql - syntax (Oracle 11.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Collections in plsql - syntax [message #652291]

Mon, 06 June 2016 07:25

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.

Report message to a moderator

Re: Collections in plsql - syntax [message #652293 is a reply to message #652291]

Mon, 06 June 2016 07:49

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

Barbara Boehmer
Messages: 9088
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.

Report message to a moderator

Re: Collections in plsql - syntax [message #652345 is a reply to message #652304]

Tue, 07 June 2016 11:21

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;

Report message to a moderator