Re: Returning values from Stored Procedure with ADO and VB5

This is a workaround but it should work:

Istead of PROCEDURE create a FUNCTION that returns VARCHAR2, e.g.

CREATE OR REPLACE FUNCTION SCOTT.SAMPLEOUTPUT(   ID IN NUMBER) RETURN VARCHAR2 IS   NAME2 VARCHAR2;  Begin
  SELECT Scott.Sample2.Sample_Name
  INTO NAME2 From Scott.Sample2
  WHERE Sample_ID = ID;

RETURN (NAME2);  End SampleOutput;

Then you set the .CommandText = "select SCOTT.sampleoutput(" & _   id & ") from dual" where 'id' has desired value.

the result recordset hopefully contains the desired value.

Jussi

CSC Staff wrote:
>
> HELP! I am experimenting with Oracle 8 and VB and am having
> difficulties getting return values back from a stored procedure
>
> I have been messing aroung with this for quite some time now and am
> getting nowhere. As soon as the program executes the
> 'qySample2.Execute' command below, the following error is returned:
>
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "SCOTT.SAMPLEOUTPUT", line 6
> ORA-06512: at line 1
>
> As far as I can tell, Oracle is unhappy with the way I am referring to
> the NAME2 variable in the stored procedure. I tried to perform the same
> manuver in RDO and got a similar error.
>
> I would really appreciate any help!
>
> Thanks!
>
> Craig Love
>
> RELEVANT INFO FOLLOWS---
>
> Here is the sample VB5 code that I am using:
>
> Set cnTracker = New ADODB.Connection
>
> With cnTracker
> .ConnectionTimeout = 3
> .CursorLocation = adUseClient
> End With
>
> sConnection = "DSN=ORCL;UID=SCOTT;PWD=TIGER;"
>
> cnTracker.Open sConnection
>
> Dim qySample2 As New ADODB.Command
>
> With qySample2
> .CommandText = "SCOTT.sampleoutput"
> .CommandType = adCmdStoredProc
> .ActiveConnection = cnTracker
> .Parameters.Append .CreateParameter(, adNumeric, adParamInput)
> .Parameters.Append .CreateParameter(, adVarChar,
> adParamInputOutput, 35)
> End With
>
> qySample2(0) = 2
> qySample2(1) = "A"
>
> qySample2.Execute
>
> Here is the Stored Procedure the code is accessing:
>
> CREATE OR REPLACE PROCEDURE SCOTT.SAMPLEOUTPUT(
> ID IN NUMBER,
> NAME2 IN OUT VARCHAR2) IS
>
> Begin
> SELECT Scott.Sample2.Sample_Name
> INTO NAME2 From Scott.Sample2
> WHERE Sample_ID = ID;
> End SampleOutput;
> /
>
> Here is the table Sample2:
>
> Sample_ID Number(10)
> Sample_Name varchar2(35)

--
If you pick up a starving dog and make him prosperous, he will not bite you. This is the principal difference between a dog and a man. -Mark Twain
[Puddin'head Wilson] Received on Wed Mar 31 1999 - 23:58:48 CST