How to call Oracle stored procedures in Visual Basic. net using Microsoft Oracle-hosted providers

From: http://support.microsoft.com/kb/321718

Create an oracle table

 

This example uses the Scott/tiger schema defined in Oracle. By default, Oracle Scott/tiger architecture comes with standard Oracle installation.

If this architecture does not exist, you must run the following "create table and insert" script, table:

CREATE TABLE DEPT
   (DEPTNO NUMBER(2,0) NOT NULL, 
   DNAME VARCHAR2(14) NULL, 
   LOC VARCHAR2(13) NULL,
   PRIMARY KEY (DEPTNO)
   );

   INSERT INTO Dept VALUES(11,'Sales','Texas');
   INSERT INTO Dept VALUES(22,'Accounting','Washington');
   INSERT INTO Dept VALUES(33,'Finance','Maine');

   CREATE TABLE EMP
   (EMPNO NUMBER(4,0) NOT NULL, 
   ENAME VARCHAR2(10) NULL, 
   JOB VARCHAR2(9) NULL, 
   MGR NUMBER(4,0) NULL,
   HIREDATE DATE NULL,
   SAL NUMBER(7,2) NULL, 
   COMM NUMBER(7,2) NULL, 
   DEPTNO NUMBER(2,0) NULL,
   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),  
   PRIMARY KEY (EMPNO)
   ); 

   INSERT INTO Emp VALUES(123,'Bob','Sales',555,'28-JAN-79',35000,12,30);
   INSERT INTO Emp VALUES(321,'Sue','Finance',555,'12-MAY-83',42000,12,10);
   INSERT INTO Emp VALUES(234,'Mary','Account',555,'14-AUG-82',33000,12,20);

Create an Oracle package

Create the following Oracle packages on the Oracle server:

CREATE OR REPLACE PACKAGE curspkg_join AS 
        TYPE t_cursor IS REF CURSOR ; 
        Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor); 
   END curspkg_join;
   /

Create the following Oracle package body on the Oracle server:

CREATE OR REPLACE PACKAGE BODY curspkg_join AS
   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 
   IS 
        v_cursor t_cursor; 
   BEGIN 
        IF n_EMPNO <> 0 
        THEN
             OPEN v_cursor FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO 
                  AND EMP.EMPNO = n_EMPNO;

        ELSE 
             OPEN v_cursor FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO;

        END IF;
        io_cursor := v_cursor; 
   END open_join_cursor1; 
   END curspkg_join;
   /

Create a Visual Basic. NET application

1. Follow these steps to create a visual basic Windows application project:
   1. Start Microsoft Visual Studio. NET.
   2. InFilePointNewAnd then clickProject.
   3. ClickProject TypeUnderVisual Basic ProjectAnd then clickTemplateUnderWindows Applications. By default, Form 1 is added to the project.
2. InProjectClickAdd referenceAnd set the reference to system. Data. oracleclient.
3. SetButtonControl andDataGridDrag the widget from the toolbox to the form.
4. Add the following code at the top of the code window:

   Imports System.Data.OracleClient

5. Add the following code to form 1Button 1 _ clickEvent:

   Dim x As Exception
           Dim Ds As New DataSet()
           Dim Oraclecon As New OracleConnection("Server=YourOracle;Uid=uid;Pwd=pwd")
   
   
           Oraclecon.Open()
   
   
           Dim myCMD As New OracleCommand()
           myCMD.Connection = Oraclecon
           myCMD.CommandText = "curspkg_join.open_join_cursor1"
           myCMD.CommandType = CommandType.StoredProcedure
           myCMD.Parameters.Add(New OracleParameter("n_empno", OracleType.Number)).Value = 123
           myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
   
   
           Dim MyDA As New OracleDataAdapter(myCMD)
   
           Try
   
               MyDA.Fill(Ds)
           Catch x
               MessageBox.Show(x.Message.ToString)
           End Try
   
   
   
           DataGrid1.DataSource = Ds.Tables(0)
   
           Oraclecon.Close()

6. Modify the oracleconnection string that suits your environment.
7. Compile and run the application according to F 5.