Ejecutar procedimientos de PL/SQL desde VBasic 6

Mi duda es la indicada en el titulo, necesitaría saber de que manera puedo ejecutar procedimientos almacenados de PL/SQL desde una aplicación en Visual Basic. No sé que instrucción/-es se necesitan para realizarlo.
Agradecería vuestra ayuda, asi como alguna dirección donde pueda obtener ayuda relacionada con pl/sql en conjunción con visual basic.
1

1 Respuesta

64.225 pts. Diego Rodrigo Munoz Herrera, de 34 anios de edad,...
Aqui una instruccion para hacerlo utilizando Microsoft ActiveX Data Objects (ADO) y el Oracle Provider for OLE DB.
Abstract
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 OLE DB.
Product Name, Product Version Oracle Provider for OLE DB, versions 8.1.7, 9.0, and 9.2
Platform Windows 98, NT, 2000, and XP Professional
Date Created 18-AUG-2000
Instructions
Execution Environment:
Visual Basic 6.0
Access Privileges:
Requires ability to create and manipulate tables and stored procedures in the Oracle database.
Usage:
Run the sample from within Visual Basic or directly from a command prompt.
Instructions:
1. Create a Data Source Name (DSN) in the ODBC Administrator using Oracle's
ODBC Driver.
2. Create the PL/SQL package in the database as described in the Script section
of this article.
3. Cut and paste the Visual Basic code into a button click method in a VB Form.
4. Edit the connection information as appropriate.
PROOFREAD THIS SCRIPT BEFORE
USING IT! Due to differences in the way text
editors, e-mail packages, and operating
systems handle text formatting (spaces,
tabs, and carriage returns), this
script may not be in an executable state
when you first receive it. Check
over the script to ensure that errors of
this type are corrected.
Description
Description:
Note that since the Oracle Provider for OLE DB cannot provide parameter information
to ADO, this has to be called in a different way from how it is called using the
ODBC Driver (see <Note:74264.1>).
Prerequisites:
* Microsoft Visual Basic 6.0
* Microsoft Data Access Components (MDAC) version 2.1 or above
* Oracle RDBMS version 8.1.7 or above
* Oracle client software version 8.1.7 or above
* Oracle ODBC driver version 8.1.7 or above
Sample Output:
The program produces two message boxes with the following contents:
Message 1 is:
Return Values from Proc are : 3 and 1
Message 2 is:
Return Value from Func are : 7 and OddFred and Odd
References
<Note:74264.1> Calling Packaged Procedures & Functions Through ADO and ODBC
Sample Code
Private Sub Callproc_Click()
' 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;
' /
'
'
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
Muchas Gracias, es lo que estaba buscando, pero antes de finalizar me gustaría que me indicaras la dirección de la página de donde has obtenido esa información. Es que también necesitaria saber como tratar los parámetros que devuelve si ejecuto funciones en pl/sql.
Muchas Gracias de antemano
Esta informacion esta e metalink.oracle.com.
Para ingresar a esta pagina debes tener tu CSI de oracle, el CSI lo obtienes y mantienes mientras tengas licenciados productos oracle estés al día con tu soporte.
Saludos.
Diego.
PD. No t olvides de cerrar la pregunta.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas