open your eyes…

say it’s you

Posts Tagged ‘C#’

[C#] Getting return value from Oracle Stored Function using ODP.NET

October 9th, 2009

#1

You have two options to achieve this goal in .NET enviroment

using System.Data.OracleClient namespace provided by .NET Framework
using Oracle.DataAccess namespace provided by ORACLE

I chose No.1 becuase of simplicity. Don’t need to install any addons,.. all I need to do is typing some codes like followings….

using System.Data.OracleClient
 
OracleCommand cmd = new OracleCommand("DeleteStoredProcedureName",
connOracleConnectionObject);   // ([ProcedureName],[OracleConnection]) param.
cmd.CommandType = CommandType.StoredProcedure;
 
OracleParameter p_ID = new OracleParameter("PrimaryKeyID", OracleType.Int32); p_ID.Value = _ID; // _ID is class level variable (aka field) that holds the id for the record to be deleted  cmd.Parameters.Add(p_ID); 
 
OracleParameter p_ReturnValue = new OracleParameter("PrimaryKeyID",OracleType.Int32);
p_ReturnValue.Direction = ParameterDirection.ReturnValue; // ReturnValue if it's a stored fuction.
 
cmd.ExecuteNonQuery();

Ths point is System.Data.OracleClient part. which means you’re using classes provided from .Net.

Code is pretty simple. You make a connection. You make a OracleCommand and specify the CommandType. And you make parameters in right way.

Finally you Execute it. (There are diffrent methods for query execution depends on the type of query, find it in MSDN)And you may see an error-code

‘ORA-06502: PL/SQL: numeric or value error string…..‘

which is funcking funny since you put all parameters in right way. I spent fucking 2-days and googling to find out solution to resolve this problem. But never I did. So I changed my mind. I chose No.2 option.

Here is the code,..

using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
OracleCommand cmd = new OracleCommand("DeleteStoredProcedureName",
connOracleConnectionObject);   // ([ProcedureName],[OracleConnection]) param.
cmd.CommandType = CommandType.StoredProcedure;
 
OracleParameter p_ID = new OracleParameter("PrimaryKeyID",
OracleType.Int32);
p_ID.Value = _ID; // _ID is class level variable (aka field) that holds the id for the record to be deleted
cmd.Parameters.Add(p_ID);
 
OracleParameter p_ReturnValue = new OracleParameter("ReturnValue",OracleType.Int32);
p_ReturnValue.Direction = ParameterDirection.ReturnValue; // Choose ReturnValue if it's a stored fuction.
 
cmd.ExecuteNonQuery();

See which namespaces I’m using…
Everything is pretty much same as example above.
But you may see this error

‘ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘[StoredFunction]‘‘

I did googling, googling again and again… finally I found solution.
Some expert says that your code finally turned into this form

:rv = [Schema].[StoredFunction](:p1, :p2…);

which means you should add parameters in right order.

Here is the code.

using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
OracleCommand cmd = new OracleCommand("DeleteStoredProcedureName",
connOracleConnectionObject);   // ([ProcedureName],[OracleConnection]) param.
cmd.CommandType = CommandType.StoredProcedure;
 
/* Add return Parameter before than any other parameter*/
OracleParameter p_ReturnValue = new OracleParameter("ReturnValue",OracleType.Int32);
p_ReturnValue.Direction = ParameterDirection.ReturnValue; // Choose ReturnValue if it's a stored fuction.
 
OracleParameter p_ID = new OracleParameter("PrimaryKeyID",
OracleType.Int32);
p_ID.Value = _ID; 
cmd.Parameters.Add(p_ID);
 
cmd.ExecuteNonQuery();

and It will work like magic… T-T

Let’s summarize it.

Use OLE Provider from Oracle.. namespace Oracle.DataAccess-Oracle Client and ODAC required(Download it from Oracle.com).-Don’t forget about TNSNAMES.ORA(If you know what it means).
!Add Return Parameter First!!
Specify the Size of Char and Varchar2 Parameters

No comments »

Posted in the workssss

Tags: .net C# ODAC ora-06502 ora-06550 oracle

[C#] My app’s process won’t kill when windows shuts down

February 20th, 2009

이것도 검색용,
윈도우 종료시 프로세스가 종료되지 않을때,
혹은 내 프로그램으로 인하여 윈도우 종료가 되지 않을때

그런 경우가 있냐? 하겠지만 있다.
어플을 만들다 보면 엑스버튼을 눌러도 종료가 안되고 그냥 트레이창으로 사라지게 해야할때가 있다.

그때는 보통
FormClosing 이벤트를 다음처럼 재정의하거나 해서 해당 기능을 구현한다.

        /// 
        /// 종료 이벤트 처리, 종료되지 않게 강제처리
        /// 
        ///
 
        ///
 
        private void 너으윈도우훰_FormClosing(object sender, FormClosingEventArgs e)
        {
                e.Cancel = true;
                this.Closing();
        }

요렇게~ 하면,
엑스버튼을 눌러도 종료이벤트가 발생하지 않는다.

하지만, 어쩐 일인지 윈도우 종료를 시키려 해도 종료가 되지 않는다.
잘 찾아보니 원인은 이 놈. 해당 어플이 돌아가는 도중에 종료가 되려면 이 놈을 반드시 호출해야 되는데, 그로 인해 종료가 이루어지지 않는 것.

데브피아도 구글링도 썩 만족스러운 답변이 없어 혹시나 해서 해보니 잘 된다.

        /// 
        /// 종료 이벤트 처리, 종료되지 않게 강제처리
        /// 
        ///
 
        ///
 
        private void 너으윈도우훰_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (e.CloseReason == CloseReason.WindowsShutDown)
            {
                e.Cancel = false;
            }
            else
            {
                e.Cancel = true;
                this.Closing();
            }
        }