This article describes how to work with Oracle Package using dotConnect for Oracle.

• Introducing
• Requirements
• Creating Typed OraclePackages
• Using Typed OraclePackages

Introducing

Oracle databases allow creating packages, which are unified sets of stored procedures, functions, variables and user-defined types. dotConnect for Oracle leverages working with Oracle packages by implementing typed OraclePackage components. These components allow you to work with a package like with a usual .NET object, invoking stored procedures as object methods. This tutorial describes how to create and use typed OraclePackages.

Requirements

This walkthrough supposes that you know how to establish a connection to server and how to create the database objects.

To follow the tutorial, you also need the package Pack created in your Oracle database. This can be made by executing the following script:

CREATE OR REPLACE PACKAGE Pack AS
  PROCEDURE get_all_depts (cur OUT SYS_REFCURSOR);
  PROCEDURE insert_dept (pDeptno INTEGER, pDname VARCHAR, pLoc VARCHAR);
  FUNCTION dept_count RETURN INTEGER;
END PACK;
/

CREATE OR REPLACE PACKAGE BODY Pack
IS 

  PROCEDURE get_all_depts (cur OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN cur FOR SELECT * FROM dept;
  END;

  PROCEDURE insert_dept (pDeptno INTEGER, pDname VARCHAR, pLoc VARCHAR) AS
  BEGIN
    INSERT INTO dept(deptno, dname, loc) VALUES (pDeptno, pDname, pLoc);
  END;

  FUNCTION dept_count RETURN INTEGER
  AS
    cnt INTEGER;
  BEGIN
    SELECT COUNT(*) INTO cnt FROM dept;
    RETURN cnt;
  END;

END PACK;
/

Creating Typed OraclePackages

Typed OraclePackage can be created via Typed Oracle Package Wizard. The wizard can be started either from the Visual Studio main menu (Tools -> Oracle -> Oracle Package Wizard) or from the toolbox (double-click the OraclePackage component or drop it to the form). In the latter case, select the New typed package option in the dialog window and click OK:

After the wizard started, follow these steps:

1. Set the connection properties to the server where you created a package and click Next to proceed.

   Note that the wizard may operate with both Direct and OCI connections.



2. In this page, you can select whether to show all packages in the database or only those belonging to the specific user. For this purpose, use the radio buttons All and User, and the list box for selecting the user.

   Select the package you want to work with. In this sample, the Pack package is used. Click Next to continue.


3. The next page allows you to set up the name of the generated class, the namespace where it will be placed and the naming rules for members of the package. In this tutorial, the defaults are used, so just click Next.

4. Select the Add this OraclePackage to designer check box to place an instance of generated OraclePackage onto the form. Click Finish.

The wizard will generate the Pack class representing the Pack Oracle package and, if you have selected the last check box, will place Pack instance pack1 onto the form. The package class needs an open connection, so if you have created a new connection while running the wizard, OracleConnection instance oracleConnection1 will be placed onto the form too.

Using Typed OraclePackages

The procedures of the Pack package will be available in pack1 just like usual object's methods. In the next sample, we invoke all methods of the Pack package, which are doing the following:

1. Get count of rows in the Dept table.
2. Insert a new record into the Dept table.
3. Retrieve all records of the table.

// Open the connection. Until the connection is opened, 
// any invocation of package methods will throw an exception.
pack1.Connection.Open();

// 1. Check the number of rows in the dept table via the dept_count function:
int count = (int)pack1.DeptCount();

// 2. Insert a record via the insert_dept procedure:
pack1.InsertDept(100, "Research", "San-Jose");

// 3. Get all records using the get_all_depts procedure, and fill a DataTable with them:
OracleCursor cur;

// The procedure returns an OracleCursor object, which can be used to retrieve a data reader.
pack1.GetAllDepts(out cur);
OracleDataReader r = cur.GetDataReader();

// With the data reader, we can populate the data table.
OracleDataTable dt = new OracleDataTable();
dt.Load(r);

' Open the connection. Until the connection is opened, 
' any invocation of package methods will throw an exception.
Pack1.Connection.Open()

' 1. Check the number of rows in the dept table via the dept_count function:
Dim count As Integer = Pack1.DeptCount()

' 2. Insert a record via the insert_dept procedure:
Pack1.InsertDept(100, "Research", "San-Jose")

' 3. Get all records using the get_all_depts procedure, and fill a DataTable with them:
Dim cur As OracleCursor

' The procedure returns an OracleCursor object, which can be used to retrieve a data reader.
Pack1.GetAllDepts(cur)
Dim r As OracleDataReader = cur.GetDataReader()

' With the data reader, we can populate the data table.
Dim dt = New OracleDataTable()
dt.Load(r)      

See Also

Logging Onto The Server  | Creating Database Objects