Ejecutar procedimientos almacenados PL/SQL desde Visual Basic

Toni
27 de Diciembre del 2003
Buenas,
Pues mi duda es la siguiente:
Me gustaria saber como ejecutar procedimientos almacenados de PL/SQL de Oracle desde una aplicación en Visual Basic. No se con que instrucción /-es se necesitan para llevarlo a cabo.

Muchas Gracias de antemano.

Johnny
27 de Diciembre del 2003
PL/SQL Package.
----------------------------

CREATE OR REPLACE PACKAGE Employees AS
TYPE empcur IS REF CURSOR;
PROCEDURE GetEmpRecords(p_cursor OUT empcur,
q_cursor OUT empcur,
indeptno IN NUMBER,
p_errorcode OUT NUMBER);
FUNCTION GetDept(inempno IN NUMBER,
p_errorcode OUT NUMBER)
RETURN empcur;
END Employees;
CREATE OR REPLACE PACKAGE BODY Employees AS
PROCEDURE GetEmpRecords(p_cursor OUT empcur,
q_cursor OUT empcur,
indeptno IN NUMBER,
p_errorcode OUT NUMBER) IS
BEGIN
p_errorcode := 0;
OPEN p_cursor FOR
SELECT *
FROM emp
WHERE deptno = indeptno
ORDER BY empno;
OPEN q_cursor FOR
SELECT empno
FROM emp
WHERE deptno = indeptno
ORDER BY empno;
EXCEPTION
WHEN OTHERS THEN
p_errorcode:= SQLCODE;
END GetEmpRecords;
FUNCTION GetDept(inempno IN NUMBER,
p_errorcode OUT NUMBER)
RETURN empcur IS
p_cursor empcur;
BEGIN
p_errorcode := 0;
OPEN p_cursor FOR
SELECT deptno
FROM emp
WHERE empno = inempno;
RETURN (p_cursor);
EXCEPTION
WHEN OTHERS THEN
p_errorcode:= SQLCODE;
END GetDept;
END Employees;

To execute the PL/SQL procedure GetDept.
---------------------------------------------------------------------

Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
Dim Message, Title, Default, EmpNoValue
Message = "Enter an employee number (5000 - 9000)"
Title = "Choose an Employee"
Default = "7654"
On Error GoTo err_test
EmpNoValue = InputBox(Message, Title, Default)
If EmpNoValue = "" Then Exit Sub
If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654
Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=exampledb;" & _
"User ID=scott;" & _
"Password=tiger;"
Oracon.Open
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
EmpNoValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2
&#8217; Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE
cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
Set recset = cmd.Execute
&#8217; Disable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = FALSE
MsgBox "Number: " & EmpNoValue & " Dept: " & recset.Fields("deptno").Value
Exit Sub
err_test:
MsgBox Error$
For Each objErr In Oracon.Errors
MsgBox objErr.Description
Next
Oracon.Errors.Clear
Resume Next

To execute the PL/SQL procedure GetEmpRecords.
---------------------------------------------------------------------------------

Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
Dim Message, Title, Default, DeptValue
Message = "Enter a department number (10, 20, or 30)"
Title = "Choose a Department"
Default = "30"
On Error GoTo err_test
DeptValue = InputBox(Message, Title, Default)
If DeptValue = "" Then Exit Sub
If DeptValue < 10 Or DeptValue > 30 Then DeptValue = 30
Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=exampledb;" & _
"User ID=scott;" & _
"Password=tiger;"
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
DeptValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2
&#8217; Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE
cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
Set recset = cmd.Execute
&#8217; Disable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = FALSE
Do While Not recset.EOF
MsgBox "Number: " & recset.Fields("empno").Value & " Name: " &
recset.Fields("ename").Value & " Dept: " & recset.Fields("deptno").Value
recset.MoveNext
Loop
Exit Sub
err_test:
MsgBox Error$
For Each objErr In Oracon.Errors
MsgBox objErr.Description
Next
Oracon.Errors.Clear
Resume Next