Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Returning values from Stored Procedure with ADO and VB5

Returning values from Stored Procedure with ADO and VB5

From: CSC Staff <cscstaff_at_shentel.net>
Date: Wed, 31 Mar 1999 17:44:05 -0500
Message-ID: <3702A535.71E37355@shentel.net>


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)



Received on Wed Mar 31 1999 - 16:44:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US