Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ranjan.

Asked: May 24, 2017 - 5:50 pm UTC

Last updated: May 25, 2017 - 1:38 am UTC

Version: 10g

Viewed 1000+ times

You Asked

why below error is comming for this code?

declare
type tt is table of test_tab%rowtype index by binary_integer;
vt tt;

begin
for i in (select id from test_tab1) loop
select name bulk collect into vt from test_tab where f_id =i.id;


for j in vt.first..vt.last loop
dbms_output.put_line(vt(j));
end loop;
end loop;
end;

ora-00913:too many values
pls-00306:wrong number or type of argument.



and Connor said...

dbms_output.put_line

will output *scalar* values, not complex values. So you need to output *each* element of the record, eg

dbms_output.put_line(vt(j).col1 ||','||vt(j).col2||','||vt(j).col3 );

etc

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Collection review

Dipali, May 25, 2017 - 7:01 am UTC

in type declaration you have used %rowtype and in Type variable you are using single column (name).
%rowtype will expect row to be fetch in variable.

declare
type tt is table of DEMO_TAGS.content_type %type index by binary_integer;
vt tt;

begin
for i in (select id from DEMO_TAGS) loop
select content_type bulk collect into vt from DEMO_TAGS where id=i.id;


for j in vt.first..vt.last loop
dbms_output.put_line(vt(j));
end loop;
end loop;
end;

Results

PRODUCT
PRODUCT
CUSTOMER
CUSTOMER
CUSTOMER
ORDER
ORDER
ORDER
ORDER
ORDER
ORDER
ORDER
ORDER
ORDER

Statement processed.

0.14 seconds

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here