pl/sql problem (more info)

Further to my email. I did some more investigation work:

  SQL> execute pack.proc1;
  BEGIN pack.proc1; END;

  *
  ERROR at line 1:

  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  ORA-06512: at "pack", line 132
  ORA-06512: at line 1

  SQL> execute pack.proc2;

  PL/SQL procedure successfully completed.

  SQL> execute pack.proc1;

  PL/SQL procedure successfully completed.

  SQL>   It seems that some variables are loaded when trying to execute   proc2 and this would affect proc1. But proc never makes   use of variables of pro2.

  When I examined the code, the culprit is as follows:

  procedure proc2
  declare
  v_countrows number;
  ...
  procedure proc1
  ...
  declare
  v_countrows number;
  begin
  select count(*) into v_countrows from tables;   ...

  If I extract proc1 and execute it, it is okay.   If I amend the v_countrows of proc1 , it is not okay.   BUT if I amend the v_countrows of proc2, then it is okay.   One thing, the procedure proc2 is physically placed ahead of proc1.

  Why the change in behaviour and why is it that I have to change   proc2 and not proc1? Seems illogical and very diffcult to debug.

In article <84qser$ks5$1_at_nnrp1.deja.com>,   akkha_at_my-deja.com wrote:
> Hi,
>
> I have a couple of stored packages which have been running for years
> on Oracle 7.3.4 with no problem. When I port them to 8.1.5 and
> execute them, following error occurs:
>
> *
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error: character string buffer too small
> ORA-06512: at "name of package etc", line 132
> ORA-06512: at line 1
>
> I examined the code and found the following:
>
> ...
>
> v_countrows number;
> ...
>
> select count(*)
> into v_countrows
> from tems_pager;
>
> The above select statement is giving the trouble. If I change the
> declaration to v_countrows number(3), then the package could be run smoothly.
>
> Is this a new feature or bug? Why the change in behavouir?
> Is it documented ?
>
> Thanks for any suggestion.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 03 2000 - 14:18:16 CST