Problem with IN/OUT parameter for stored procedure

[beckybear]

I'm not sure if this post belongs here or in the VB forum, but haven't heard anything in VB, so here I am...

I have a VB6 program on a Win2000 box which is calling an Oracle stored procedure. The problem is that the IN/OUT parameter is returning the correct string, but with garbage on the end of the string up to the buffer size. This code works fine with 7.3.4, but does not work with 8.1.7.

Stored Proc:

PROCEDURE get_seqkey(p_key IN OUT VARCHAR2) AS
  n_key NUMBER(18);
BEGIN
  SELECT abc_seq.nextval
    INTO n_key
    FROM DUAL;
  p_key := LTRIM(TO_CHAR(n_key,'000000000000000000'));
END get_seqkey;

VB Code:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "get_seqkey"
cmd.Parameters.Append cmd.CreateParameter("p_key", adVarChar, adParamInputOutput, 19, "")
cmd.Execute

After the execution, cmd.Parameters("p_key&quot contains the proper 18 character sequence number, plus 2 garbage characters. If I change the parm buffer size to 17, it works, but then I get an error message on the 7.3.4 version that the buffer is too small. I'd like the upgrade of this program to be independent of our oracle upgrade, if possible.

I've tried running this stored procedure from outside this VB program and it works fine. Thanks in advance for your help.

 

[spenglerr]

According to the to_char format model or Oracle 8
you would have to give the format as

p_key := LTRIM(TO_CHAR(n_key,'099999999999999999'));

to get leading zeroes.

Perhaps this is the reason.

 

[beckybear]

Thanks for the info. I don't have any control over the oracle database to be able to change this and test it, but I tried another procedure that just does a

select to_char(sysdate,'YYYYMMDDHH24MISS')
INTO p_key
FROM DUAL;

and I'm experiencing the same problem. So I think it's something more generic than the specific procedure code.

 

[jee]

Can you solve your problem by using the SUBSTR function to grab the first 18 characters? I know this doesn't address the reason for the change in behaviour between database versions.

Since your procedure works properly when you don't use VB to call it, my guess would be there is some difference in the ODBC driver for the two Oracle versions.