CODEWITHSUNDEEP
oracle-databaseplsql
Kiran Patil
Kiran Patil

Reputation: 339

How to display all record in single row

My query is

create or replace procedure TEMP_ARRAy_RECORD2
is
--type emp_det_tbl IS TABLE OF temp_employee_det%ROWTYPE index by pls_integer;
  type lemp_det IS RECORD 
   ( 
     --l_emp_id int, 
     l_emp_id temp_employee_det.emp_id%type,
     l_city temp_employee_det.city%type,
     l_amount temp_employee_det.amount%type
     );
     TYPE emp_det IS VARRAY(15) OF lemp_det;
  f_emp_det emp_det;
BEGIN 

for i in (select emp_id,city,amount BULK COLLECT into  f_emp_det from temp_employee_det)
loop
dbms_output.put_line(i.emp_id||'$'||i.city||'$'||i.amount);
exit when SQL%NOTFOUND;
end loop;
end;

Now show my output

SQL*Plus statement executed
PL/SQL block executed
1$NASHIK$100
1$NASHIK$50
2$PPPPP,KK,JJ$45
2$KKKK,KK,$50
2$KKKK,kk$300
5$PPPPP$15.07
3$PPPPP$15.507
4$NASHIK$122

But my expected output is

1$NASHIK$1001$NASHIK$502$PPPPP,KK,JJ$452$KKKK,KK,$502$KKKK,kk$3005$PPPPP$15.073$PPPPP$15.504$NASHIK$122

I have tried on Index By Table and Varray but not get proper answer. Please don't use any function and other. I want this output in an array.

Upvotes: 3

Views: 395

Answers (1)

APC
APC

Reputation: 146239

Your function uses DBMS_OUTPUT.PUT_LINE to display your results. This prints the message to the output buffer, followed by a new line character.

All you need do to get the output on a single line is use PUT() instead of PUT. PUT() prints the output but doesn't print the newline. Consequently each call to PUT() concatenates its output with the existing contents of the line. Finally we need to call DBMS_OUTPUT.NEW_LINE() to print the line.

PUT() is very useful when we want to build our output in stages, say in a loop. The concatenated output cannot exceed the specified length of a single line (maximum allowed is 32k).

So it's quite a simple change to your code:

create or replace procedure TEMP_ARRAy_RECORD2
is
--type emp_det_tbl IS TABLE OF temp_employee_det%ROWTYPE index by pls_integer;
  type lemp_det IS RECORD 
   ( 
     --l_emp_id int, 
     l_emp_id temp_employee_det.emp_id%type,
     l_city temp_employee_det.city%type,
     l_amount temp_employee_det.amount%type
     );
     TYPE emp_det IS VARRAY(15) OF lemp_det;
  f_emp_det emp_det;
BEGIN 

for i in (select emp_id,city,amount BULK COLLECT into  f_emp_det from temp_employee_det)
loop
dbms_output.put(i.emp_id||'$'||i.city||'$'||i.amount);
exit when SQL%NOTFOUND;
end loop;
dbms_output.new_line;
end;

Upvotes: 4

Related Questions