Re: Help: Concatenating Records

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/01
Message-ID: <3482c7ac.1539263@inet16>#1/1

On 1 Dec 1997 10:21:09 GMT, Christine Elssner <celssner_at_aixterm3.urz.uni-heidelberg.de> wrote:

>Hi,
>
>I'm looking for help for the following problem:
>
>I'm trying to write a Procedure to concatenate several records retrieved by a select statement. When trying to run the procedure, I get the following error messages:
>

Its probable that by concatenate all the records together, you have exceeded 2000 characters and you are trying to stuff more then 2000 bytes into kandidat.

To see, do this:

>for rec in cur loop

 begin
> kandidat:=kandidat||rec.x;

    exception

        when others then
            raise_application_error( -20000, 'Yes, its the concat' );
    end;


>	xxx:=kandidat;

>end loop;

Why have a varchar2(2000) and a long (a long in pl/sql is really just a varchar2(32760). Pl/sql doesn't limit varchar2's to 2000 characters.

>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "SYSKEY.KANDIDATEN", line 13
>ORA-06512: at line 1
>
>And this is the procedure:
>
>CREATE or replace PROCEDURE KANDIDATEN (prof number) as
>
>kandidat varchar2(2000):='';
>xxx long;
>cursor cur is
> select a.vnam||' '||a.name||' ('||DIENSTE.TEXTN(d.ANTI,100125)||' '||initcap(d.vnam)||' '||initcap(d.name)||')
>' x
> from studenten a, pruefungen b, pruefer c, pruefer d
> where prof=b.pruefers and b.dts IS NOT NULL and b.noteps IS NULL
> and a.matr=b.matr and b.gutachter=d.persnr;
>
>
>BEGIN
>for rec in cur loop
> kandidat:=kandidat||rec.x;
> xxx:=kandidat;
>end loop;
>--update pruefer set pruefer.kandidaten=kandidat
>-- where pruefer.persnr=prof;
>--commit;
>END kandidaten;
>/
>
>
>Any Ideas?
>
>Greetings,
> Christine Elssner
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 01 1997 - 00:00:00 CST