Re: Oracle configuration related weird behaviour?
From: sybrandb <sybrandb_at_gmail.com>
Date: Thu, 4 Jun 2009 01:58:23 -0700 (PDT)
Message-ID: <96f90a78-488c-4d1b-8d24-9441093caf82_at_h11g2000yqb.googlegroups.com>
On 4 jun, 09:55, qualit..._at_gmail.com wrote:
> I want to replicate some schemas from a Oracle 9.2.0.8 to a 9.2.0.1.
> The export/import process was OK and now I've objects replicated and
> data loaded.
>
> A function is not working properly, and an example of what's the
> problem can be reproduced with this example:
>
> declare
> cursor cPrueba is
> select 'apruebas' as nom from dual
> union
> select trim('This is a very very long text' || ' ' || 'This is
> another very very long text' ) as nom from dual
> order by nom;
> begin
> for elemento in cPrueba
> loop
> dbms_output.put_line('this is ' || elemento.nom);
> end loop;
> end;
>
> I've been trying with this query, and when I added the first select,
> it generates a ORA-06502: PL/SQL: numeric or value error string.
>
> If I do a substr in the second select to truncate that text to be as
> long as the first one 'apruebas' plus one, 8+1, then it works ok.
> The rule applies if I change the first string, just substr the next
> ones to be the first length + 1.
>
> It seems the first row determines the length of that 'column'.
>
> I haven't this problem in the first database, so this is the reason
> why I think the diference may be related to server configuration.
>
> Is there any configuration parameter determining this behaviour?
>
> Thank you in advance.
Sybrand Bakker
Senior Oracle DBA
Date: Thu, 4 Jun 2009 01:58:23 -0700 (PDT)
Message-ID: <96f90a78-488c-4d1b-8d24-9441093caf82_at_h11g2000yqb.googlegroups.com>
On 4 jun, 09:55, qualit..._at_gmail.com wrote:
> I want to replicate some schemas from a Oracle 9.2.0.8 to a 9.2.0.1.
> The export/import process was OK and now I've objects replicated and
> data loaded.
>
> A function is not working properly, and an example of what's the
> problem can be reproduced with this example:
>
> declare
> cursor cPrueba is
> select 'apruebas' as nom from dual
> union
> select trim('This is a very very long text' || ' ' || 'This is
> another very very long text' ) as nom from dual
> order by nom;
> begin
> for elemento in cPrueba
> loop
> dbms_output.put_line('this is ' || elemento.nom);
> end loop;
> end;
>
> I've been trying with this query, and when I added the first select,
> it generates a ORA-06502: PL/SQL: numeric or value error string.
>
> If I do a substr in the second select to truncate that text to be as
> long as the first one 'apruebas' plus one, 8+1, then it works ok.
> The rule applies if I change the first string, just substr the next
> ones to be the first length + 1.
>
> It seems the first row determines the length of that 'column'.
>
> I haven't this problem in the first database, so this is the reason
> why I think the diference may be related to server configuration.
>
> Is there any configuration parameter determining this behaviour?
>
> Thank you in advance.
The problem is related to comparing a patched, desupported, release 9.2.0.8 with a non-patched, equally desupported, base release 9.2.0.1.
This is exactly the type of issue being left-over in a base release
and being patched in a patch release.
So if it works, in 9.2.0.8, dump 9.2.0.1 or better still : dump 9.2
and upgrade to 10gR2.
Sybrand Bakker
Senior Oracle DBA
Experts : those who *did* read the documentation. Received on Thu Jun 04 2009 - 03:58:23 CDT