Re: pl/sql problem (more info)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 03 Jan 2000 16:36:00 -0500
Message-ID: <sg527sgq4m44p8h1vkde2gjvhg87m5tqtd@4ax.com>

A copy of this was sent to akkha_at_my-deja.com (if that email address didn't require changing) On Mon, 03 Jan 2000 20:18:16 GMT, you wrote:

>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.
>

can you post a small test case? I do not follow 100% -- i tried this:

tkyte_at_8i> create or replace procedure p2   2 as
  3 v_countrows number;
  4

  5          procedure p1
  6          is
  7                  v_countrows number;
  8          begin
  9                  select count(*) into v_countrows from emp;
 10          end;

 11 begin
 12
 13 p1;
 14
 15 end;
 16 /

Procedure created.

tkyte_at_8i>
tkyte_at_8i> exec p2

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> create or replace procedure p1   2 is
  3 v_countrows number;
  4 begin
  5 select count(*) into v_countrows from emp;   6 end;
  7 /

Procedure created.

tkyte_at_8i>
tkyte_at_8i> exec p1

PL/SQL procedure successfully completed.

and it works OK

> 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.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 03 2000 - 15:36:00 CST