Oracle Stored Proc IN/OUT parm using ADO - get garbage

[beckybear]

Sorry this is posted in a few places - I'm new and trying to find the right forum for this problem.

I have a VB6 program that 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 had been working fine with oracle 7.3.4, but started giving garbage when tried 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

I've tried assigning cmd.Parameters("p_key&quot to both a string and a variant and get the same thing. The variable contains the proper 18 character sequence number, plus 2 garbage characters. If I change the parm buffer size to 17, it works with 8.1.7, but then I get an error message on the 7.3.4 version that the buffer is too small. I'd like 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. I also tried this with another oracle stored procedure that generates sysdate string and get the same result - garbage at the end of the string up to the buffer length. I would appreciate any help you can give me.

 

[Kavius]

Not sure on this one but I know that VB stores strings in a different format than a lot of other programs. Never had this problem in Oracle, but have had it in a couple of other instances. To get around it I initialize the string to all chr(0) and then use an instr and left to grab the relavant piece.

Dim str As String
'initialize string
str = String(100, 0) 
'fetch value from procedure
getVal str
'trim it
str = Left(str, InStr(1, str, 0, vbBinaryCompare))

I'm not sure if this will do the trick, but...good luck.