by Yuening Dai (30 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Mon 12th July 1999
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO.
VBcoders
'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;
No comments have been posted about Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO. Why not be the first to post a comment about Easy way to pass parameters to stored procedures of Oracle when using Command.Execute method in ADO.
