v7 possible bug... (+)

[hubamuba]

Hi!

Make new proc:

create or replace procedure killme1 (p_lsValue in long, p_sValue in varchar2, p_nLongLength out number, p_nVarcharLength out number)
is
lsValue long := null;
sValue varchar2(4000);
begin
if p_lsValue is not null then
   lsValue := p_lsValue;
   p_nLongLength := length (lsValue);
   end if;
if p_sValue is not null then
   sValue := p_sValue;
   p_nVarcharLength := length (sValue);
   end if;
end killme1;

Now test it in Test Window with default value of bind variables:

and get result:

oops, why long value is not null?!?

for example, this test in Sql Windows:

declare
nlonglength number;
nvarcharlength number;
begin
killme1 (null, null, nlonglength, nvarcharlength);
dbms_output.put_line (nvl (nlonglength,0));
dbms_output.put_line (nvl (nvarcharlength,0));
end;

output:
0
0

Anton.

 

[Marco Kalter]

Can you send me the program file and test script by e-mail?

 

[hubamuba]

Originally posted by Marco Kalter:
Can you send me the program file...

You mean plsqldev.exe from PLSQL Developer folder? ok...

 

[Bo Pedersen]

The test case is simple

declare
  input long;
begin
  :output1 := length(:input);
  :output2 := length(input);
end;

Where utput1 and utput2 are integers and :input is a long.

When the long is initialised as a bind variable in the test window it has the length of 32512 but when initialised by the PL/SQL engine the length is null. After execution, the Large Data Editor shows a bunch of zeros.

Using the CLOB datatype, the behaviour is as expected

Bo Pedersen

 

[Bo Pedersen]

Addendum: editing the long bind variable to save an empty long also gives the expected results.

 

[Marco Kalter]

The problem is that for a NULL value the test window assumes an output variable. Due to a restriction for long variables, they have to be pre-allocated on the client. Hence the 32512 characters (the maximum long size). I admit it's a bit awkward and unexpected.