Oracle stored procedure call from VB6

Hi everebody,
Maybe somebody know link/book with good explanation/examples how to Open Connection and call Oracle's stored procedure with OUT parameters from VB6?

Thanks in advance,
Ilya
IlyaFoxAsked:
Who is Participating?
 
watyCommented:
' #Mandix Repository#*****************************************************
' * Programmer Name  : Oracle
' * Web Site         : http://www.vbdiamond.com
' * E-Mail           :
' * Date             : 13/11/01
' **********************************************************************
' * Comments         : Calling Stored Procedures and Functions From VB/ADO using the OLEDB Provider
' *
' * This article contains an example of how to call stored procedures
' * and  functions from Visual Basic using Microsoft Activex Data
' * Objects (ADO)  and the Oracle Provider for OLEDB.
' *
' **********************************************************************
Private Sub Callproc_Click()
   'This code demonstrates calling an Oracle Packaged Procedure and Function
   ' using the ODBC {Call...} Syntax From Visual Basic using the Microsoft
   ' Activex Data Objects (ADO) 2.1 Interface via The Oracle OLEDB Provider.
   '  The pl/sql Package called is
   'create or replace package odbpack as
   '
   'Procedure Proc(param1 in number,param2 in out number,
   '     param3 out number);
   '
   'Function Func(param1 in varchar2, param2 in out varchar2,
   '    param3 out varchar2) return number;
   'end odbpack;
   '/
   '
   'create or replace package body odbpack as
   'Procedure Proc(param1 in number,param2 in out number,
   '     param3 out number) is
   'begin
   '      param2 := param1+param2;
   '      param3 := param1;
   'end;
   '
   'Function func(param1 in varchar2, param2 in out varchar2,
   '    param3 out varchar2) return number is
   'begin
   '    param2 := param1||param2;
   '    param3 := param1;
   '    return length(param2);
   'end;
   'end odbpack;
   '/
   '
   ' This code was tested using VB 6 and the Version 8.1.6.0 of the OLEDB Provider

   '

   Dim cnn1             As ADODB.Connection
   Dim cmdExeproc       As ADODB.Command
   ' Open connection.
   Set cnn1 = New ADODB.Connection
   ' Modify the following line to reflect a Connection within your environment
   strCnn = "Provider=OraOLEDB.Oracle;User ID=SCOTT;Password=TIGER;Data Source=S692816.WORLD;"

   ' Create Parameter Objects to be used later

   Dim prm1             As ADODB.Parameter
   Dim prm2             As ADODB.Parameter
   Dim prm3             As ADODB.Parameter
   Dim prm4             As ADODB.Parameter

   cnn1.Open strCnn

   Set cmdExeproc = New ADODB.Command

   cmdExeproc.ActiveConnection = cnn1

   cmdExeproc.CommandText = "{call odbpack.proc(?,?,?)}"
   '  In the next set of code, we have to manually set up
   '  the parameters types since Our OLEDB Provider cannot provide parameter information
   '  when calling procedures

   Set prm1 = cmdExeproc.CreateParameter("prm1", adSmallInt, adParamInput, , 1)
   cmdExeproc.Parameters.Append prm1

   Set prm2 = cmdExeproc.CreateParameter("prm2", adSmallInt, adParamInputOutput, , 2)
   cmdExeproc.Parameters.Append prm2

   Set prm3 = cmdExeproc.CreateParameter("prm3", adSmallInt, adParamOutput)
   cmdExeproc.Parameters.Append prm3

   ' Now we have the parameters set - execute the command.

   cmdExeproc.Execute

   ' Show the results

   MsgBox ("Return Values from Proc are : " & _
      cmdExeproc.Parameters(1).Value & _
      " and " & _
      cmdExeproc.Parameters(2).Value)

   ' Now do the same thing for a function

   Set cmdExeproc = New ADODB.Command

   cmdExeproc.ActiveConnection = cnn1

   '  Set up the command text - note the ? = call syntax
   cmdExeproc.CommandText = "{? = call odbpack.func(?,?,?)}"

   '  In the next set of code, we have to manually create
   '  the parameters since our OLEDB driver cannot provide this
   '  information when calling packaged procedures.

   ' first parameter is the return value - note it's type

   Set prm1 = cmdExeproc.CreateParameter("prm1", adInteger, adParamReturnValue)
   cmdExeproc.Parameters.Append prm1

   Set prm2 = cmdExeproc.CreateParameter("prm2", adVarChar, adParamInput, 3, "Odd")
   cmdExeproc.Parameters.Append prm2

   Set prm3 = cmdExeproc.CreateParameter("prm3", adVarChar, adParamInputOutput, 30, "Fred")
   cmdExeproc.Parameters.Append prm3

   Set prm4 = cmdExeproc.CreateParameter("prm4", adVarChar, adParamOutput, 30)
   cmdExeproc.Parameters.Append prm4

   ' execute the command

   cmdExeproc.Execute

   'Show the results
   MsgBox ("Return Values from Func are : " & _
      cmdExeproc.Parameters(0).Value & _
      " and " & _
      cmdExeproc.Parameters(2).Value & _
      " and " & _
      cmdExeproc.Parameters(3).Value)

   '  Close Connection
   cnn1.Close
End Sub
0
 
watyCommented:
' #Mandix Repository#*****************************************************
' * Programmer Name  : Yuening Dai
' * Web Site         : http://www.vbdiamond.com
' * E-Mail           :
' * Date             : 08/12/1999
' * Time             : 15:53
' **********************************************************************
' * Comments         : Pass parameters to stored procedures of Oracle using ADO
' *
' *
' **********************************************************************
'From MSDN online help sample, if you want to pass parameters to a stored
'procedure, you need to create parameter objects, append them to ADO Command
'object, and then assign the values. But I found an easy way to do the same
'work if you are going to call procedures in Oracle. In the same method, you can
'return from an Oracle PL/SQl function.
'
Dim strSQL As String
Dim qryStoredProc As New ADODB.Command
Dim id As Long
Dim name As String
'
With qryStoredProc
   .CommandText = "scott.instrec"
   .CommandType = adCmdStoredProc
   .ActiveConnection = mCnn          'Suppose that you already have a valid one.
End With
'
id = CLng(InputBox("Enter an integer below:"))
name = InputBox("Enter the  name:")
'
'Insert a new record into Oracle table.
qryStoredProc(0) = id
qryStoredProc(1) = name
qryStoredProc.Execute
'
'The first parameter is InOut and the other is Out.
MsgBox "ParamInOut: " & qryStoredProc(0) & _
   NL & "ParamOut: " & qryStoredProc(2)

'----------------------------------------------------------
'The Oracle table MYTEST has only two cols: MYID and MYTEST
'The code for the stored procedure is listed below.
'
'procedure       instrec (
' p_id in out number, p_name in varchar2,
' p_id_plus out number
' ) AS
'BEGIN
' insert into mytest values(p_id, p_name);
' commit;
' -- Testing only
' p_id_plus:=p_id+1;
' p_id:=p_id+1;
'END instrec;
0
 
watyCommented:
' #Mandix Repository#*****************************************************
' * Programmer Name  : Support
' * Web Site         : http://www.vbdiamond.com
' * E-Mail           :
' * Date             : 12/10/01
' **********************************************************************
' * Comments         : Insert data in Oracle using a Stored Procedure
' *
' * Very basic sample of how to use a text box to get input from
' * a user and then insert it into Oracle using a  stored procedure.
' *
' **********************************************************************

' Sql Script to create the table and Stored procedure:
' create table jo
' (no number,
' name char(10),
' lname char(10));
'
' create or replace procedure Getjo
'    (no in number,name in char, lname in char)
'  as
'  begin
'   insert into jo values (no + 1 , name ,lname);
'  end;
'
' Commit;

Private Sub Command1_Click()

   Dim con              As New ADODB.Connection
   Dim cmd              As New ADODB.Command
   Dim Param1           As New ADODB.Parameter
   Dim Param2           As New ADODB.Parameter
   Dim Param3           As New ADODB.Parameter

   con.ConnectionString = "DSN=jillbert; UID=scott; PWD=tiger;"
   con.Open

   cmd.ActiveConnection = con
   cmd.CommandText = "Getjo"
   cmd.CommandType = adCmdStoredProc

   Set Param1 = cmd.CreateParameter("Prm1", adInteger, adParamInput, , 100)
   cmd.Parameters.Append Param1

   Set Param2 = cmd.CreateParameter("Prm2", adChar, adParamInput, 10, Text1.Text)

   cmd.Parameters.Append Param2

   Set Param3 = cmd.CreateParameter("Prm3", adChar, adParamInput, 10, Text2.Text)
   cmd.Parameters.Append Param3

   cmd.Execute
   MsgBox ("Done Executing the Stored Procedure")
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.