• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO

Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO

Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO.

Rate Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'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;