Re: How to concatenate multiple rows from db_source?

From: Rashat Abdullin <rashat_at_cell.ru>
Date: 1996/04/15
Message-ID: <3171EAB3.34BC_at_cell.ru>#1/1

badri wrote:
>
> Hi:
>
> I would like to concatenate all rows of the db_source column
> for a given "name". Thus, I want one column in my result that has
> the concatenation of all the lines of the selected procedure,
> for example.
>
> How do I do this?
>
> thanks a lot,
>
> -Badri

You'd better create a function as follows:

---

Create or replace FUNCTION GetSourceLines(Sname In char)  return varchar2 AS

 SrcLines varchar2(20000) := '';
 SingleLine varchar2(2000) :='';

CURSOR Cline is select text from all_source  where
 name=Sname
 order by line;

Begin
 OPEN Cline;
 LOOP
 FETCH Cline into SingleLine;
 EXIT WHEN Cline%NOTFOUND;
   SrcLines:=SrcLines||SingleLine;
 End LOOP;
 CLOSE Cline;
return SrcLines;
End GetSourceLines;

---

Then after function creation you give a query:

SQL>select GetSourceLines('GETSOURCELINES') from dual;

GETSOURCELINES('GETSOURCELINES')

---

FUNCTION GetSourceLines(Sname In char)
 return varchar2 AS
 SrcLines varchar2(20000) := '';
 SingleLine varchar2(2000) :='';
CURSOR Cline is select text from all_source  where
 name=Sname
 order by line;
Begin
 OPEN Cline;
 LOOP
 FETCH Cline into SingleLine;
 EXIT WHEN Cline%NOTFOUND;
   SrcLines:=SrcLines||SingleLine;
 End LOOP;
 CLOSE Cline;
return SrcLines;
End GetSourceLines;

SQL> Think it help you. Received on Mon Apr 15 1996 - 00:00:00 CEST