Calling a Stored Procedure in Oracle using VB 
Author Message
 Calling a Stored Procedure in Oracle using VB

Hello All,

I have a stored procedure in an Oracle database that has four input
parameters an one output parameter.  I am trying to find the correct way to
run the stored procedure and store the output to a variable or recordset.
Both the input and outputs are defined as strings (or VarChar2) in Oracle
DB.  I have been using the code below:

 Dim CMD1 As ADODB.Command
 Dim ParamPeriod As New ADODB.Parameter
 Dim ParamUserid As New ADODB.Parameter
 Dim ParamTablename As New ADODB.Parameter
 Dim ParamAgencycode As New ADODB.Parameter
 Dim ParamViewName As New ADODB.Parameter
 Dim RSViewName As New ADODB.Recordset
 Dim CMDString As String

'Defining the initial connection to the Database to retrieve agency
information and verify the user.
  Set OracleCnnX = New ADODB.Connection
  CnnX = "Provider=MSDAORA;Data Source=;User ID=fsm2000;Password=fsm2000"
  OracleCnnX.ConnectionString = CnnX

'Opening the database connection.
  OracleCnnX.Open

  Set RSViewName = New ADODB.Recordset

  Set CMD1 = New ADODB.Command
  CMD1.CommandText = "call fsm2000.createConsolidatedView"
  CMD1.CommandType = adCmdStoredProc
  Set CMD1.ActiveConnection = OracleCnnX

  Set ParamPeriod = CMD1.CreateParameter(, , adParamInput)
  Set ParamUserid = CMD1.CreateParameter(, , adParamInput)
  Set ParamTablename = CMD1.CreateParameter(, , adParamInput)
  Set ParamParamAgencycode = CMD1.CreateParameter(, , adParamInput)
  Set ParamViewName = CMD1.CreateParameter(, , adParamOutput)

  CMD1.Parameters.Append ParamPeriod
  ParamPeriod.Value = "122001"

  CMD1.Parameters.Append ParamUserid
  ParamUserid.Value = "fsm2000"

  CMD1.Parameters.Append ParamTablename
  ParamTablename.Value = "cr_balancesheet"

  CMD1.Parameters.Append ParamAgencycode
  ParamAgencycode.Value = "1"

I get an error that says the parameter is improperly defined, inconsistent
or incomplete data was provided.
I can not figure what is wrong with this code, it pretty much follows what
is in the help files.

If I could get this to work I would go on to execute the command and set it
to a recordset or variable.

KJ Smith



Wed, 01 Sep 2004 02:27:17 GMT  
 Calling a Stored Procedure in Oracle using VB
There are mor properties than value to worry about...

Quote:
>   ParamPeriod.Value = "122001"

i.e. ...

prm.Name = "au_lname"
prm.Type = adVarChar
prm.Direction = adInput
prm.Size = 40
prm.Value = "Ringer"
cmd.Parameters.Append prm

D.


Quote:
> Hello All,

> I have a stored procedure in an Oracle database that has four input
> parameters an one output parameter.  I am trying to find the correct way
to
> run the stored procedure and store the output to a variable or recordset.
> Both the input and outputs are defined as strings (or VarChar2) in Oracle
> DB.  I have been using the code below:

>  Dim CMD1 As ADODB.Command
>  Dim ParamPeriod As New ADODB.Parameter
>  Dim ParamUserid As New ADODB.Parameter
>  Dim ParamTablename As New ADODB.Parameter
>  Dim ParamAgencycode As New ADODB.Parameter
>  Dim ParamViewName As New ADODB.Parameter
>  Dim RSViewName As New ADODB.Recordset
>  Dim CMDString As String

> 'Defining the initial connection to the Database to retrieve agency
> information and verify the user.
>   Set OracleCnnX = New ADODB.Connection
>   CnnX = "Provider=MSDAORA;Data Source=;User ID=fsm2000;Password=fsm2000"
>   OracleCnnX.ConnectionString = CnnX

> 'Opening the database connection.
>   OracleCnnX.Open

>   Set RSViewName = New ADODB.Recordset

>   Set CMD1 = New ADODB.Command
>   CMD1.CommandText = "call fsm2000.createConsolidatedView"
>   CMD1.CommandType = adCmdStoredProc
>   Set CMD1.ActiveConnection = OracleCnnX

>   Set ParamPeriod = CMD1.CreateParameter(, , adParamInput)
>   Set ParamUserid = CMD1.CreateParameter(, , adParamInput)
>   Set ParamTablename = CMD1.CreateParameter(, , adParamInput)
>   Set ParamParamAgencycode = CMD1.CreateParameter(, , adParamInput)
>   Set ParamViewName = CMD1.CreateParameter(, , adParamOutput)

>   CMD1.Parameters.Append ParamPeriod
>   ParamPeriod.Value = "122001"

>   CMD1.Parameters.Append ParamUserid
>   ParamUserid.Value = "fsm2000"

>   CMD1.Parameters.Append ParamTablename
>   ParamTablename.Value = "cr_balancesheet"

>   CMD1.Parameters.Append ParamAgencycode
>   ParamAgencycode.Value = "1"

> I get an error that says the parameter is improperly defined, inconsistent
> or incomplete data was provided.
> I can not figure what is wrong with this code, it pretty much follows what
> is in the help files.

> If I could get this to work I would go on to execute the command and set
it
> to a recordset or variable.

> KJ Smith



Wed, 01 Sep 2004 04:13:10 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Calling an Oracle Stored Procedure from Vb using Q+E

2. Calling Oracle Stored Procedure from VB6 using DAO

3. Can Oracle 7 Boolean type be used as out parameter in call a stored procedure

4. Error calling Oracle store procedures using ADO

5. Call Stored Procedure using PL/SQL,VB4 and ORACLE 7.3

6. Calling Oracle Stored Procedure from VB

7. How to call an Oracle stored procedure from VB

8. Calling Oracle Stored Procedure in VB

9. Calling Oracle Stored Procedure from VB

10. Calling Oracle Stored Procedure in VB ?

11. Calling Oracle SQL Stored Procedures from VB

12. VB/Access to call Stored Procedures in Oracle

 

 
Powered by phpBB® Forum Software