Calling Oracle procedures from Visual Basic 6

Consider the following relation

employee(emp_no number(4),e_name varchar2(15),designation varchar2(15),salary number(6),ph_no number(10),address varchar2(25));

Consider the following oracle procedure to raise the salary of employees belonging to a particular designation.

create or replace procedure employee_bonus(desig IN varchar2,bonus IN number)
is
cursor emp
is
select emp_no from employee where designation=desig;
y number(5);
begin
open emp;
loop
fetch emp into y;
exit when emp%NOTFOUND;
update employee set salary=salary+bonus where emp_no=y;
end loop;
close emp;
end ;
/

Assume the user enters the designation and the bonus in textbox1 and textbox2 respectively . The following code calls the oracle procedure "employee_bonus" from Visual Basic 6.

Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim m as String
Dim n as Integer
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=localhost;User Id=paul;Password=oracle;"
Set pro = New ADODB.Command
pro.ActiveConnection = oconn
pro.CommandText = "employee_bonus"
pro.CommandType = adCmdStoredProc
pro.Parameters.Append pro.CreateParameter("desig", adVarChar, adParamInput, 25)
pro.Parameters.Append pro.CreateParameter("bonus", adNumeric, adParamInput, 5)
m=Text1.Text
n=Text2.Text
pro("desig") = m
pro("bonus") = n
pro.Execute

To execute a procedure ,first create a ADODB Connection to the database.Then declare a ADODB command object and assign it with the created ADODB Connection.In the above code,we create a ADODB Connection called "oconn" and a ADODB command object called "pro".In the statements,

Set pro = New ADODB.Command
pro.ActiveConnection = oconn

we create a new ADODB command object and assign it with a ADODB Connection.

The next step is to assign the "CommandText" and "CommandType" property of command object "pro".CommandText property is used to specify the name of the oracle procedure to be called.CommandType specifies the type the command object points to.So we write the statements

pro.CommandText = "employee_bonus"
pro.CommandType = adCmdStoredProc

The next step is to create each parameter to be passed to the oracle procedure.Thus here we write the statements to create two parameters,

pro.Parameters.Append pro.CreateParameter("desig", adVarChar, adParamInput, 25)
pro.Parameters.Append pro.CreateParameter("bonus", adNumeric, adParamInput, 5)

Here inside the CreateParameter() ,the first argument is the name of the parameter as mentioned in the oracle procedure.The next argument is the type of the parameter(ie chracter or numeric).The third argument specifies whether the parameter belongs to IN or OUT or INOUT type as mentioned in the oracle procedure.The IN parameter cannot be modified inside the oracle procedure.The OUT parameter can be modified inside the oracle procedure.The INOUT parameter is the combination of both types.The last argument in CreateParameter() is the length of the parameter to be passed.

The next step is to assign values to the parameters to be passed to the oracle procedure.

pro("desig") = m
pro("bonus") = n

The last step is to execute the procedure.This is done by the following command.

pro.Execute

Thus we have successfully executed a oracle procedure from VB6.