Problem in getting Oracle function return value using ODP.net

 Dear All

I fall a problem by getting value from oracle function in asp.net. the code is as follows...

the oracle function is

...start...

 create or replace function ultimus.FXN_GET_TRAN_DATE(pBRANCH_ID IN VARCHAR2) return date is

-- ********************************************************************************
-- Description: Get Branch Working Date
-- ================================================================================

  Result DATE;


begin

     Select  DECODE(a.day_status_id, 2, a.TRAN_DT, NULL) AS TRAN_DT
      Into Result
      From ULTIMUS.SMS_BRANCH_DAY_LOG a
     Where a.BRANCH_ID = pBRANCH_ID;

    return(Result);

exception
  When NO_DATA_FOUND Then
  return(null);
end FXN_GET_TRAN_DATE;

....end... 

And the C# code in asp.net is

...start...

OracleConnection ocon = new OracleConnection(ConfigurationManager.ConnectionStrings["application"].ConnectionString);
           OracleCommand ocom = new OracleCommand();
            ocom.Connection = ocon;
            ocom.CommandType = CommandType.StoredProcedure;
            ocom.CommandText = "ULTIMUS.FXN_GET_TRAN_DATE";
            ocom.Parameters.Add("pBRANCH_ID", pBRANCH_ID);
            ocom.Parameters.Add("TRAN_DT", DBNull.Value);
            ocom.Parameters["TRAN_DT"].DbType = DbType.Date;
            ocom.Parameters["TRAN_DT"].Direction = ParameterDirection.ReturnValue;
            ocon.Open();

            ocom.ExecuteNonQuery();
            string s = ocom.Parameters["TRAN_DT"].Value.ToString();
            ocon.Close();

....end... 

Now the problem:

In my return function parameter i always get "" value. But i test the oracle function and it returns a date value like "27/07/2007". but i got the value "".

Please help me to find out where is the problem.

Thanks 



0
aminulbari
7/28/2007 8:47:07 AM
asp.net.oracle 2269 articles. 0 followers. Follow

5 Replies
2111 Views

Similar Articles

[PageSpeed] 32

You would have to use DataSet to return a row and you put your value there.


from Villa El Salvador for world!
0
Javier
7/29/2007 4:51:31 AM

I am not using any data reader. so why do i need dataset. I am using ExecuteNonquery() function.

 



0
aminulbari
7/29/2007 6:13:18 AM

Although ExecuteNonQuery may not be the most intuitive name, you will most surely notice why you don't get any return value if you look up the function on MSDN.

Function should be called ExecuteQueryAndDontReturnAnythingAtAll instead Wink


If this post was useful to you, please mark it as answer. Thank you!
0
johram
7/31/2007 11:25:21 AM

If you are calling Oracle functions with single return value from ASP.NET using ODP.NET, then you have to make your ReturnValue parameter the first parameter in you OracleParameter Collection to make it work.

0
ekhanna
9/14/2007 2:17:49 PM

Hi Friend,

1st, Your function takes only 1 parameter and you are trying to pass 2 parameters from your .net code.

2. You write a simple query something like this.

select ultimus.FXN_GET_TRAN_DATE('YourBranchID') from dual;

this query will fetch the result in a datareader and then read the value from there.

Hope this will help you.


B'Rgds,
Yash

Dont forget to click "Mark as Answer" on the post that helped you.
0
ypcing
9/18/2007 2:31:35 AM
Reply:

Similar Artilces:

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

vb.net function and Oracle procedure to get an Oracle Dataset??
I have similar code (a procedure and function) working perfectly with a sql server data source. I;'m trying to do the same things with Oracle, but have run into the problems.    I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best  option. create or replace package body PK_RETAILER isPROCEDURE sp_Get_Order_History(v_ani IN number,output_cursor out PK_CURSORES.generic_cursor) ASBEGINopen output_cursor forselect o.account_number, p.phone, o.amount, o.name, o.init_datefrom orders ojoin users_phones p on p.account_number...

Passing boolean values to oracle function / procedure from C#.Net
Hi,  I dont know how to pass boolean values to oracle function / procedure arguments, could you please help me to know ?  Thank you, Sreedhar After spending lot of time on google, I understand C#.Net doest support PL/SQL boolean values. Thanks Sreedhar...

return resultset from oracle proc using ODP.net
i have istalled ODP.net successfully on my machine and i am working with my oracle database. but i need to be able to run a procedure from withing c# and get a resultset in return which i can feed into a datareader or dataset through commandObj.ExecuteReader(). i am having a problem returning a resultset from an oracle proc within c#. can any body tell me how to do that? Hi, Heres some code, hope it helps. Greg SQL ===== CREATE OR REPLACE PACKAGE tp2 AS TYPE empcur IS REF CURSOR; Procedure GetEmpRecordsAsProc(ecur in out empcur,deptnum in number);...

which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...

How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)?
I try to call Oracle Function in .net C# by using System.Data.OracleClient. But i am not successful. Here is my code FUNCTION TTHAO_MONTT_EXISTS ( vten varchar2) RETURN boolean IS ret boolean; montt_id integer; BEGIN ret:=false; montt_id:= -1; select monthethao_id into montt_id from TTHAO_MONTHETHAO where ten = vten; if montt_id!=-1 then ret:=true; end if; return ret; Exception when others then return false; END; I do not know, when I call function TTHAO_MONTT_E...

Problem while inserting character Ö using ODP.Net into oracle
Hi  I am facing problem while inserting string 'CH-FERBÖX_A_'. The character 'Ö' is getting converted into 'O' after insertion.The string is being  converted into ' CH-FERBOX_A_' after insertion.When I fire same insert statment using  sql plus , replacement of character from  'Ö' to 'O' did not happen.I am using ASP.Net 1.1. Actual String  : 'CH-FERBÖX_A_' Value in DB : ' CH-FERBOX_A_' after insertion does anyone know how to get around this problem ? Thanks in advance   ...

Oracle store procedure problem using Powerbuilder and Oracle 8.0.4.0 Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

Oracle ODP.net mulitple Oracle Client
To connect to the oracle database using ODP.net, the applicate must add the dll file called Oracle.DataAccess. There are a lot of version of Oracle.DataAccess. As I know, if the client use Oracle Client 10g Release 2. If the client use Oracle Client 10g Release 2, the application also have to use the Oracle.DataAccess the come from Oracle Client 10g Release 2.  My question is that there are any method to add more than one Oracle.DataAccess version in my product to support multiple OracleClient.   That should automatically happen for the most part.  Oracle's ODP.NET i...

urgent: .NET Provider for Oracle or Oracle Data Provider for .NET
If I want to access Oracle DB in ASP.NET application, and I am using .NET Provider for Oracle or Oracle Data Provider for .NET, should I have to install OracleClient on my .NET server? Is there other choice? Thank you very much.Johnson Johnson2007:should I have to install OracleClient on my .NET server?  You have to options1. install the ODP.NET on your web serverOR2. put the oracle assemblies in your BIN directory   Hi jimmy,Thank you for you reply. Because I could not find a server right now, I should test it first before I agree or disagree with you, as most articles I...

How To Change An Oracle Password Using ODP.NET?
Hi, I am converting a VB6 login module that uses OO4O to a .NET application using ODP.NET. The VB6 module has a checkbox which lets the user change their password after giving a valid Oracle ID and password, using the 'OraSession.ChangePassword hoststring, ID, Old Password, New Password' command. In ODP.NET, the closest I can find is the OpenWithNewPassword option. However, the ODP.NET Developer's Guide says 'Note: OpenWithNewPassword should only be used when the user password has expired, not for changing the password.' That's all there is, no mention about how to change a password...

Problem calling an Oracle function with a return value
Hi, Vitals.... Win2000 PB6.5 Oracle 8.05 I am attepting to fire off an oracle function, via PB, that will do an insert (among other things) and then return the primary key of the transaction it just inserted. I have attempted (unsuccessfully!) to code it using the Dynamic SQL Format #3 (code below). I keep receiving "Procedure has not been executed or has no results". Code: DECLARE mo_load DYNAMIC CURSOR FOR SQLSA; ls_sql = "imf_create_im_master_order(" + string(idb_location_id) + ", " + & string(ldb_order_frequency_pk) + ",...

Problem installing ODP.NET for Oracle 10gR2.
  I downloaded ODP.NET for Oracle 10gR2, I ran setup.exe, it created a new home, and when I go to the folders, there are no files. I'm running Windows 2000, and I'm able to connect to Oracle using SQL PLUS with no problems.  Any suggestions?  Thanks.Thanks---Ep...

Web resources about - Problem in getting Oracle function return value using ODP.net - asp.net.oracle

Wave function - Wikipedia, the free encyclopedia
... (a ball attached to a spring ) in classical mechanics (A-B) and quantum mechanics (C-H). In quantum mechanics (C-H), the ball has a wave function ...

iPhone best tricks and functions - Business Insider
Your iPhone can do a lot more than you think.

Study: Car Infotainment Systems Remain A Distraction, Despite Voice Command Functions
... one of the study’s authors told the Associated Press . “They make a promise that they are a safe alternative, but they just don’t always function ...

Review: HP Spectre 360 A Perfect Mix Of Style, Form, Function
HP Inc. delivers a brushed-aluminum laptop with a new 6th Generation Intel i7 processor, making it one of the best-looking, best-priced and best-performing ...

Why Global Corporations Need To Redesign Their Strategic Planning Function
Corporate strategy teams play an important role in guiding business leaders to make smarter, better and more informed decisions, especially in ...

Sponsored post: Garmin’s fenix 3 Pairs Form and Function with GPS Watch
... timepiece to polish off a professional look, or strap on a rugged stopwatch for our workouts. But when it comes to everyday use, the functional ...

Code Craft – Embedding C++: Timing Virtual Functions
... compiler just like the standard language types. A subtype inherits the characteristics of its parent type and can use, or not, the member functions ...

Madras HC asks police to allow Tipu’s birth anniversary function
... Station on a petition filed by Ismail, General Secretary of Thamizhaga Makkal Jananayaga Katchi, seeking grant of permission to hold the function. ...

Colorado governor: This Planned Parenthood shooting may be a function of inflammatory rhetoric from bloggers ...
Via Grabien , skip to 4:45 of the clip below for the key bit. I naively thought we were (mostly) done with “climate of hate” stupidity after ...

1964 Archival Footage of British Soldiers Under the Effects of LSD Losing the Ability to Properly Function ...
1964 archival footage of British soldiers being given LSD-25 while training the the field. Over time they lost their ability to properly function, ...

Resources last updated: 12/3/2015 8:49:26 AM