Making best out of Oracle batabases using ODP.net - Part 2

原创 2004年09月05日 06:03:00
 
Introduction

The issue of using generic oledb.net, odbc.net layers vs. vendor specific providers has always been a debate during the development of data access layers. Second in this series this article will cover the other specific features of oracle database as used by ODP.net Please visit my earlier article Making best out of Oracle databases Using ODP.net – Part I.

Returning Multiple Result Sets from Stored Procedures

It is not uncommon for the front end development teams requesting for result sets from the stored procedures so that they need not concentrate on complex relations. The following code piece will explain one of the different ways to retrieve multiple result sets (cursors) from an odp.net client. REF CURSORS are variable cursors using which oracle allow you to pass the cursor locators between the applications.

CREATE OR REPLACE PACKAGE RefCurPkg AS
TYPE cur_emp_t IS REF CURSOR RETURN emp%ROWTYPE;
TYPE cur_dept_t IS REF CURSOR RETURN dept%ROWTYPE;
PROCEDURE get_cursor(dept_in IN NUMBER,
cur_out_emp OUT cur_emp_t,
cur_out_dept OUT cur_dept_t);
END;
/
CREATE OR REPLACE PACKAGE BODY REfCurPkg AS
PROCEDURE get_cursor(dept_in IN NUMBER,
cur_out_emp OUT cur_emp_t,
cur_out_dept OUT cur_dept_t) IS
BEGIN

OPEN cur_out_emp FOR SELECT * FROM emp
WHERE deptno = dept_in;

OPEN cur_out_dept FOR SELECT * FROM dept;
END get_cursor;
END;
/

As evident the above stored procedure returns two REF CURSORS one for the employees for a given department and the other one the entire department table.

The following ODP.net calls the stored procedure and retrieves the two result sets from it.

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "REFCURPKG.GET_CURSOR";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("dept_in", OracleDbType.Int32, DBNull.Value, ParameterDirection.Input);
cmd.Parameters.Add("cur_out_emp", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
cmd.Parameters.Add("cur_out_dept", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
cmd.Parameters[0].Value = 30;

cmd.ExecuteNonQuery();
// Read the employee result set
OracleRefCursor oraref = (OracleRefCursor) cmd.Parameters[1].Value;
OracleDataReader dr = oraref.GetDataReader();
while (dr.Read())
{
System.Console.WriteLine(dr["ename"]);
}
// Read the dept result set
oraref = (OracleRefCursor) cmd.Parameters[2].Value;
dr = oraref.GetDataReader();
while (dr.Read())
{
System.Console.WriteLine(dr["dname"]);
}


As evident from the code piece OracleRefCursor is a corresponding ODP.NET for the REF CURSOR implemented in the stored procedure. The GetDataReader() from OracleRefCursor returns an OracleDataReader.

Passing C# Array Objects as Arguments To Stored Procedures

Arrays have been a popular data structure in different programming languages, unfortunately arrays have not been directly supported inside databases. However oracle has long been supporting arrays in the form of PL/SQL extensions. This support has been extended to odp.net also. The following example shows how you can pass an array object from C# client to an oracle PL/SQL stored procedure using ODP.net.

This simple example gets a list of employees as an array and returns the total salary for these employees, resulting in less network round trips.

CREATE OR REPLACE PACKAGE ArrayDemo AS
TYPE emparr_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE get_emp_sal_sum(emparr IN emparr_t, salsum OUT NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY arraydemo AS
PROCEDURE get_emp_sal_sum(emparr IN emparr_t, salsum OUT NUMBER) IS
curemp emp.empno%TYPE;
cursal emp.sal%TYPE;
BEGIN
salsum := 0;
FOR i IN 1..emparr.LAST
LOOP
BEGIN
curemp := emparr(i);
SELECT sal
INTO cursal
FROM emp
WHERE empno = curemp;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
cursal := 0;
END;
salsum := salsum + cursal;
END LOOP;
END get_emp_sal_sum;
END;
/

The corresponding C# client to pass an array will be as follows.


OracleCommand cmd = new OracleCommand( "begin ARRAYDEMO.GET_EMP_SAL_SUM(:1, :2); end;", con);
OracleParameter Param1 = cmd.Parameters.Add("emparr",OracleDbType.Int32);
Param1.Direction = ParameterDirection.Input;
Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param1.Value = new int[4]{7900,7902,7934,7788};
Param1.Size = 4;
OracleParameter Param2 = cmd.Parameters.Add("salsum", OracleDbType.Int32, ParameterDirection.Output);

cmd.ExecuteNonQuery();
System.Console.WriteLine(cmd.Parameters[1].Value);

Look at the usage of OracleCollectionType.PLSQLAssociativeArray to indicate the usage of PL/SQL array. Similar concept can be applied in array binding features thru which multiple rows can be inserted into a table in a single round trip.

LOB Support

ODP.net provides the objects OracleClob to access character large object and OracleBlob to access a binary large object stored inside the database. The following example loads a Word Resume into an employee table and then retrieves the same document to create a new word document. Internally OracleBlob is represented as System.Byte[].

Let us first create a table in oracle with a BLOB column, in this simple example employee’s information are stored along with their resume.

CREATE TABLE empinfo
(
eno NUMBER(4) ,
ename VARCHAR2(10) ,
resume BLOB
)
TABLESPACE USERS;

The following code piece will create an employee record and will load a resume from the local file system.

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "INSERT INTO empinfo(eno, ename, resume) VALUES (:eno,:ename,:resume) ";
cmd.Connection = con;
cmd.Parameters.Add(":eno", OracleDbType.Int32 );
cmd.Parameters.Add(":ename", OracleDbType.Varchar2);
cmd.Parameters.Add(":resume", OracleDbType.Blob);
cmd.Parameters[0].Value = 100;
cmd.Parameters[1].Value = "WILL SMITH";
System.IO.FileStream fs =
new System.IO.FileStream("C://FILES TO MOVE//docs//resume.doc",
System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] resBytes = new byte[fs.Length];
fs.Read(resBytes, 0, resBytes.Length);
fs.Close();
cmd.Parameters[2].Value = resBytes;
cmd.ExecuteNonQuery();

Having loaded the resume into the database, the following code piece will retrieve the employee record and will create a word document from the OracleBlob column.

System.IO.FileStream fw =
new System.IO.FileStream("C://FILES TO MOVE//docs//resumenew.doc",
System.IO.FileMode.Create, System.IO.FileAccess.Write);
BinaryWriter br = new BinaryWriter(fw);
cmd.CommandText = "SELECT eno, ename, resume FROM empinfo";
OracleDataReader dr = cmd.ExecuteReader();
OracleBlob readResume = new OracleBlob(con);

while (dr.Read())
{
Console.WriteLine(dr["ename"]);
readResume = dr.GetOracleBlob(2);
}
br.Write(readResume.Value);
dr.Close();

Conclusion

This section of the series demonstrates further the ability of ODP.net in developing database applications on oracle databases. The features like LOB, multi result sets from stored procedures are certainly essential for any serious database development projects. I will try to cover mode advanced features in the coming articles.

通过odp.net方式连接Oracle数据库

问题描述: 先说下基本开发环境:在winserver2008 r2 上安装了oracle 32位Client,通过vs2012 新建C# 网站工程连接服务器端的oracle。 连接字符串为:”Pr...
  • xu_guowei
  • xu_guowei
  • 2015年07月23日 15:48
  • 1161

Odp.net 64位配置详细说明

(一)下载ODAC121021Xcopy_x64.zip,地址  http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.htm...
  • u010755500
  • u010755500
  • 2016年02月22日 18:00
  • 544

oracle ODP.NET 批量插入或更新

使用ADO.NET提供的功能访问Oracle库要进行批量数据的插入或者更新的时间太慢了,对于10W级别的数据基本上都在5分钟左右;然而利用ODP.Net本身提供的一种数组参数功能来实现批量操作, 结果...
  • fighter_yy
  • fighter_yy
  • 2013年05月04日 13:40
  • 1001

使用 ODP.NET 访问 Oracle(.net如何访问Oracle)详解

1,什么是ODF .NE,?就是Oracle 为 .NET (ODP.NET) 专门编写了 Oracle Data Provider,一个用于 Microsoft .NET 环境下的 Oracle 数...
  • dyllove98
  • dyllove98
  • 2013年06月15日 15:21
  • 1779

在vs里面安装ODP.NET连接oracle数据库,及代码如何使用

//在vs里面安装ODP.NET连接oracle数据库 在程序包管理器控制台里面输入命令: Install-Package Oracle.ManagedDataAccess 相关网址: https...
  • u011511086
  • u011511086
  • 2016年11月16日 10:06
  • 817

c#中odp.net 4.0利用odac操作oracle数据库学习(一)

c#中odp.net 4.0利用odac操作oracle数据库学习(一)
  • pengzhen8805
  • pengzhen8805
  • 2013年12月16日 17:16
  • 2612

基于部分亲和字段PAF(Part Affinity Field)的2D图像姿态估计

该文章出自2017年的CVPR,Realtime Multi-Person 2D Pose Estimation using Part Affinity Field,是CMU的工作,效果真的amazi...
  • qq_14845119
  • qq_14845119
  • 2017年05月19日 13:21
  • 5257

jstack诊断weblogic连接池满的问题

1.节点现场告警,weblogic节点告警,且系统使用缓慢。 2.看weblogic日志出现大量的错误日志,连不上数据源: java.sql.SQLException: start() fai...
  • guogang83
  • guogang83
  • 2017年12月19日 19:56
  • 65

在oracle的连接(join)中使用using关键字

前面我们介绍了在oralce中使用natural join,也就是自然连接。在Oracle中的join连接中使用using关键字,是相对于natural join的。 我们在前面提到,如果是使用...
  • kai27ks
  • kai27ks
  • 2013年09月30日 22:41
  • 4580

C#利用ODP.NET往oracle中高效插入百万数据

由于工作的原因,要使用winform来处理大量的数据,但是c#自带的System.data.OracleClient效率不是很高,在网上找了很久,找到了ODP.NET,是oracle为c#提供的。貌似...
  • xwnxwn
  • xwnxwn
  • 2016年06月15日 10:01
  • 3245
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Making best out of Oracle batabases using ODP.net - Part 2
举报原因:
原因补充:

(最多只允许输入30个字)