Home > .NET, C#, Database > ODP.NET ORA-06502, problem of DBParameter

ODP.NET ORA-06502, problem of DBParameter

Gần đây tôi thường xuyên phải làm việc với Oracle trên C# NET. Để tương tác với Oracle db, tôi sử dụng bộ thư viện ODP.NET do chính Oracle cung cấp. Mới nhập môn đã thấy mọi thứ nằm ngoài tầm hiểu biết, mọi thứ đều không trơ tru như là MS SQL Server + ADO.Net. Phức tạp từ conversion kiểu dữ liệu giữa môi trường lập trình với database, add parameter cho command object, cấp phát bộ nhớ,… đến các syntax T-SQL trên Oracle cũng khác khá nhiều trên MS SQL.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Đó là 2 lỗi (cùng là một dạng lỗi về kiểu dữ liệu) mà tôi gặp ngày hôm nay, mà loay hoay không biết vì sao. Sau gần 1h tìm hiểu, đầu óc mới mở mang ra đôi chút.

1. Lỗi do thứ tự parameter khi add trên chương trình không khớp với thứ tự đã định nghĩa trong db

Tôi viết một function có tên TESTFUNCTION trên một db Oracle như sau:

FUNCTION TESTFUNCTION(pNUM NUMBER)

RETURN STRING

AS

pSTR STRING(50);

BEGIN

IF (pNUM=0) THEN

pSTR := ‘EQUAL ZERO’;

ELSE

pSTR := ‘NOT EQUAL ZERO’;

END IF;

RETURN pSTR;

END TESTFUNCTION;

Viết một hàm để thực hiện TESTFUNCTION trên VS 2005:

private void testing(int num)

{

DbProviderFactory objFactory = DbProviderFactories.GetFactory(“Oracle.DataAccess.Client”);

DbConnection objConnection = objFactory.CreateConnection();

DbCommand objCommand = objFactory.CreateCommand();

objConnection.ConnectionString = “…”; // your connection string

objCommand.Connection = objConnection;

 

// create input param for TESTFUNCTION

DbParameter inParam = objCommand.CreateParameter();

inParam.ParameterName = “pNUM”;

inParam.DbType = DbType.Int32;

inParam.Value = num;

objCommand.Parameters.Add(inParam);

 

// create return param for TESTFUNCTION

DbParameter returnParam = objCommand.CreateParameter();

returnParam.ParameterName = “pSTR”;

returnParam.DbType = DbType.String;

returnParam.Direction = ParameterDirection.ReturnValue;

objCommand.Parameters.Add(returnParam);

 

try

{

objCommand.CommandText = “MYPACKAGE.TESTFUNCTION”;

objCommand.CommandType = CommandType.StoredProcedure;

objConnection.Open();

objCommand.ExecuteNonQuery();

string s = returnParam.Value as string;

MessageBox.Show(s);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

objConnection.Close();

}

}

Thực hiện gọi “testing(0);” trên chương trình.

Exception:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 1

Cause: thứ tự của parameter không đúng.

Fix it: Đối với các function trả về giá trị phải add return parameter trước tiên, sau đó lần lượt add các param in/out theo đúng thứ tự mà function đã định nghĩa. Ở hàm [testing(int num)] Cần phải đưa đoạn code [create return param for TESTFUNCTION] lên trước đoạn [create input param for TESTFUNCTION]

ODP.net không hỗ trợ named parameters khi thông qua ADO.NET của .NET framework. Do đó khi gọi function/stored procedure của Oracle trên .NET phải add parameter theo đúng thứ tự mà  function/stored procedure đó định nghĩa.

Đối với các Oracle function có giá trị trả về (return value), parameter cho giá trị trả về đó luôn có thứ tự đầu tiên (index = 0)

 

2. Lỗi do parameter trên chương trình không đủ bộ nhớ để chứa dữ liệu trả về từ db

Sau khi thay đổi lại đúng thứ tự các parameter như TESTFUNCTION đã định nghĩa. Gọi lại “test(0);

Exception:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 1

Cause: returnParam không đủ bộ nhớ để lưu trữ dữ liệu trả về.

Fix it: Cần phải thiết lập giá trị cho thuộc tính Size của returnParam. Như vậy phải thêm dòng “returnParam.Size = 50;” sau dòng code khởi tạo đối tượng returnParam

.NET framework tự động cấp phát bộ nhớ cho các parameters được khởi tạo từ ADO.NET (OLEDB, ODBC, SQLClient). Đối với ODP.net thì không như vậy, do đó luôn phải chú ý về việc thiết lập bộ nhớ cho từng parameter khi làm việc với ODP.NET.

Lúc này, hàm testing được thực hiện thành công, và kết quả là hộp thoại đưa ra thông báo “EQUAL ZERO”.

Categories: .NET, C#, Database Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: