Unable to get Output data using VB, Ado 2.1 and Oracle Stored procedure 
Author Message
 Unable to get Output data using VB, Ado 2.1 and Oracle Stored procedure

I am getting error message when I try to run stored procedure in Oracle
through Visual Basic (6.0) using Ado 2.1. Can any body kindly give me a clue
how to get data back. I have run this stored procedure in oracle environment
using PL/SQL. Over there it is working fine. But I couldn't make it work in

Error Message is:
Ora-06502: PL/SQL: Numeric or Value error
Ora-06512 at "DMVPROC.PG_Veh101", line 1578
Ora-06512: at line 1
Ora-06502: PL/SQL: numeric or value error.

It crashes on oCmd1.Execute line.

The format for Stored Procedure is...
Package Name:    PG_VEH101
Procedure Name:  P_VEH101A

PROCEDURE p_veh101a
      (g_inq_type      IN     VARCHAR2,
       g_oracle_key    IN     VARCHAR2,
       g_confidential  IN     VARCHAR2,
       g_end_status    OUT    CHAR, 'length 255
       g_oracle_data   OUT    VARCHAR2); 'length 3000, The data returned is
an unlabeled, fixed-length stream of data.

Codes are....

Dim oCmd1 As ADODB.Command
Set oCmd1 = New ADODB.Command
Set oCmd1.ActiveConnection = objConn

On Error GoTo error_handler

With oCmd1
  .CommandText = "pg_veh101.p_veh101a"
  .CommandType = adCmdStoredProc
  '.ActiveConnection = objConn
  .Parameters.Append .CreateParameter("g_end_status", adChar, adParamOutput,
  .Parameters.Append .CreateParameter("g_oracle_data", adVarChar,
adParamOutput, 3000)
  .Parameters.Append .CreateParameter("g_inq_type", adVarChar, adParamInput,
3, "VIN")
  .Parameters.Append .CreateParameter("g_oracle_key", adVarChar,
adParamInput, 23, "1B4FK5435KX685867")
  .Parameters.Append .CreateParameter("g_confidential", adVarChar,
adParamInput, 30, "TAGT9283928392")

oCmd1.Execute '**I get error here

MsgBox "Inquiry status is: " & oCmd1(0) & vbCrLf & _
       "Data is: " & oCmd1(1)

Set oCmd1 = Nothing

Exit Sub
MsgBox "Error number is: " & Err.Number & ":  " & vbCrLf & _
"Error Description is : " & Err.Description & ".", vbOKOnly + vbCritical,
Set oCmd1 = Nothing
End Sub

Tue, 18 Feb 2003 01:06:33 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Accessing Oracle Stored procedure output parameter from VB using RDO2.0

2. output parameter using oracle stored procedure

3. oracle's stored procedure from vb using ADO

4. Unable to execute Oracle Procedure using Ado2.5 - using synonyms for procedures

5. HELP ==>Oracle Stored Procedure using ADO(VB)

6. HELP ==>Oracle Stored Procedure(PACKAGE) using ADO(VB)

7. ADO Date Fields (ADO 2.1, VB 6, Oracle 8)

8. Getting data from a stored procedure using VB

9. Bulk Loading of Data to Oracle with ADO 2.1

10. Getting the output from a stored procedure in VB.

11. Getting the output from a stored procedure in VB.

12. Oracle stored procedure and input arrays using ADO and VB6


Powered by phpBB® Forum Software