How To call Oracle function from ADO.NET?

How To call Oracle function from ADO.NET?

The simple function takes 2 integer arguments and returns string.
I want to call this function from my C# code.
9/3/2004 8:42:27 AM 2269 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 18

the sql would be

select function(param1,param2) as result from dual;
where function is your function, an param* are your two arguements.
The result will come back as recordset("result"). Best not use result because it could quite easily be reserved word.
9/3/2004 9:34:06 AM

Or you can use ExecuteNonQuery and access the return param directly. Example follows.

create or replace function myfunc(
myInVarchar in varchar2, myInNumber in number, myInOutVarchar in out varchar2)
return varchar2
retval varchar2(50);
retval := myInVarchar || myInNumber || myInOutVarchar;
myInOutVarchar := myInVarchar || ' from the stored func';
return retval;
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
public class odpparams
public static void Main()
OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "myfunc";
cmd.Connection = con;
OracleParameter retval = new OracleParameter("myretval",OracleDbType.Varchar2,50);
retval.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(new OracleParameter("myfirstparam",OracleDbType.Varchar2,50)).Value="MyValue";
cmd.Parameters.Add(new OracleParameter("mysecondparam",OracleDbType.Decimal)).Value=1;
OracleParameter inoutval = new OracleParameter("inoutval",OracleDbType.Varchar2,50);
inoutval.Direction = ParameterDirection.InputOutput;
inoutval.Value = "Hello";
Console.WriteLine("Return value is {0}",retval.Value);
Console.WriteLine("InOut value is {0}",inoutval.Value);

9/8/2004 1:20:18 PM

Similar Artilces:

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...

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...

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...

Does .NET map calls for ADO to ADO.NET?
Hello,We are trying to troubleshoot some DB problems from COM objects that call ADO. I heard that when ADO is loaded inside of a COM object by a .NET app, that .NET intercepts the call and re-maps those calls over to ADO.NET. This seems untrue to me, given the nature of ADO.NET being a totally separate technology.Does anyone know for sure whether this does or does not occur?The problem we are seeing is actually almost identical in symptoms to this:"FIX: Data Queries Sporadically Return Empty Recordsets" Literally, this is what...

PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES
PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES - ADO.NET TO ORACLE FUNCTION   I have a very simple oracle function I'm trying to call from The code works fine for a stored procedure but I get the error  PLS-00221: is not a procedure or is undefined  when I try to call the function. here's my code:  I've manually tested both the function and stored procedure and they are both working as expected.  here's my code Protected Overrides Sub OnLoad(ByVal e As EventArgs) Dim cnn As OracleConnection Dim sConnSt... 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,, o.amount,, o.init_datefrom orders ojoin users_phones p on p.account_number...

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 ; ...

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 to call an oracle stored function/procedure using ODBC.NET
Hi all, I have been trying to call an oracle stored funtion and cannot make it. Can anyone give me a clue. Thanks, Ted Code........ <%@ Page Language="VB" %> <%@ Import Namespace="System" %> <%@ Import Namespace="System.Web" %> <%@ Import Namespace="System.Web.UI" %> <%@ Import Namespace="System.Web.UI.WebControls" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Configuration" %> <%@ Import Namespace="Microsoft.Data...

Oracle Spatial support in Devart's ADO.NET Data Provider for Oracle [Edit]
dotConnect for oracle v. 7.3 , ADO.NET Provider from Devart, includes support for Oracle Spatial in the projects, using Entity Framework. Devart Team constantly implements all the available Entity Framework innovations, and shortly after the introduction of DbGeometry and DbGeography classes in Entity Framework 5 has implemented support for Oracle Spatial in the Entity Framework provider for Oracle. Oracle MDSYS.SDO_GEOMETRY object type can now be mapped to DbGeometry and DbGeography classes. Now dotConnect for Oracle includes new versions of Devar...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

.NET Oracle Provider
Hi all,I am using .NET 2.0 Frameworks Oracle provider and it does not have a OracleDbTypes Type for me to use. I would like to create a parameter that is of the Oracle type NVARCHAR, but it seems I can only use DbTypes, which does not have this. If I used DbTypes.String, would this be ok, otherwise, how else can I be more specific about the Oracle Parameter data type?Thanks  can u explain which type of data u r going to storeThank uBaba Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. What's your version of ODP? You have OracleD...

Which .Net Provider for Oracle 9i Microsoft or Oracle
Can anyone provide me pros and cons of provider to access Oracle Database ? I am aware of Microsoft Provider and Oracle Provider. Microsoft Oracle I guess you can use either. But as some post suggests, if both installed, then MS ODP will not work. I've been using DataDirect Technologies Connect for .Net and Oracle. It's not free, so it depends on your budget. The speed improvements were great for us, and it's ni...

Oracle mulitple Oracle Client
To connect to the oracle database using, 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...

C#.NET with Oracle Functions
 Hi,In my C#.NET application, i am using OracleClient as connectivity to intract with Oracle Database.I can able to retrieve data from Oracle Database. Also, i can able to insert records using Procedure.But, i am facing big problem with Oracle Function.Here is my coding,public string UPDATE_MEMBER_INFO(int org_id, string account_id, int member_info_detail_id, int account_detail_info_id, string detail_desc, int default_ind, int active_ind, int user_id, string created_at, string connString){            OracleConnection orConn = null;  ...

OleDb.Net vs. ODBC.Net Connection to Oracle
I want to connect to an Oracle database, and I understand that I can use either the OleDB.Net data provider or the ODBC.Net data provider for my connection. Does it matter which one I use? What are the advantages and disadvantages of each? Would appreciate any input. ODBC is slower than OleDb. because ODBC has to talk to OleDbProvider then OleDbProvider will talk to Oracle Db. Instead if you are able to use OleDb straight to DB then it will be faster. If you only have THOSE choices, I would take OleDb over ODBC any day. If you are using Framework 1.1, you have the choice of using th...

PB 10.5.1 Build 6021 , Oracle 10g server , Oracle 8i client. Making an RPC call to a Oracle Function and the last character is getting truncated
Making an RPC call to a Oracle Function and the last character is getting truncated. Similar Support Case : CR Number: 386424 Case Number: 11158124 I tried Disableunicode = 1 and not working .Is there any other workaround for oracle 8i client. That case was for Oracle 9i server and 8i client. There was a second workaround which was to use the Oracle 9i client. Not sure if that would work for you since you're on Oracle 10g server. > Making an RPC call to a Oracle Function and the last > character is getting truncated. > > Simi...

ADO.NET Oracle help needed!
I'm just starting with ADO.NET and ASP.NET. I have been working with ADO for a while now, but I seem to be stuck on ADO.NET. I don't understand why the following code brings back an error message saying "Value cannot be null. Parameter name: Dataset." and highlights the DA.Fill(DS,"grade") line, saying that is where the problem is. Can anyone help me figure this out? Most of my readings on ADO.NET talk about SQLserver...not exactly much help there. Thanks! Dim DS As DataSet Dim MyConnection As OleDBConnection Dim DA As New OleDBDataAdapter ...

How use unicode in Oracle with ADO.NET
I created one table in Oracle by statement : create table Test2 (id integer, Ten nvarchar2(50) ) and I write one application by C# to insert data into Oracle Database : OleDbConnection conn = new OleDbConnection("Provider=MSDAORA;Data Source=hbk;Persist Security Info=True;User ID=hbk;Password=hbk;Unicode=True;"); conn.Open(); OleDbCommand cmd = new OleDbCommand("insert into Test2(ID,Ten) values(10,N'Nguyễn thị hồng nhung')", conn); cmd.ExecuteNonQuery(); conn.Close(); I retrieved data that inserted by snip code: OleDbConnection conn = new OleDbConnection("Pro...

Using RPCFUNC when calling Oracle Procedures/Functions in Oracle PACKAGES
I have looked at the documentation that I have, and found that RPCFUNC is the recommended option to call remote stored procedures. Does this, however, also work with Oracle packages (functions and procedures within an Oracle package)? I have done the following: 1. Created a user object (uo_transaction) inherited from transaction and had the following on its Decalre Local External Functions: FUNCTION string get_ins_address_zid(string address_type_zid, string address_line1, string address_line2, string address_line3, string brgy_zid) RPCFUNC ALIAS FOR "MAPI_CENTRAL.TBPKG__A...

Oracle Sequence Numbers in ADO.NET...
In TOAD I can do something like: CREATE SEQUENCE seq_instance_id;SET SERVEROUTPUT ONDECLARE x t_run_record.instance_id%TYPE;BEGIN   INSERT INTO T_RUN_RECORD   (instance_id, ae2e_key_id, rec_status_id, run_id ,user_id, status_note, rec_exists_yn, case_id_number, ponder, echid, ltmgr, pididid, scididid, target, design, version_num)   VALUES   (seq_instance_id.NEXTVAL, 11111, 3, 8, "TEST", "Test record inserted by me.", "Y", -99999, "BA1100NSTRING", "", "ID10T-FORM", "", "", "ADMIN", -99999, "")   RETURNING instance...

.Net with Oracle
Anybody has tried to develop mobile app. with .Net against an Oracle database? Any idea to synchronize the data record between the database server and the client (pda) data. It will be great if you could share your codes / experience. Thanks a lot. Regards, Elton ...

Oracle and .Net
I realize this is an open ended question.  I've written several applications that use Access databases as input.  All of my reading, writing, deleting and updating to these databases were always through Oledbconnections; then creating Sql parameter queries or just creating queries in code to manage data.  I may be getting involved with an application whose data is kept in an Oracle database.  How much different is it from obtaining data from Access databases?  Does the same theories apply:  Create a connection, open the connection, r...

Choosing Oracle Client Version with Microsoft Managed Oracle Provider For .net
We need to change the Oracle Client that the System.Data.OracleClient uses to connect to our database on a server that has multiple versions of the Oracle Client. A different application on the server that we do not have control over is requiring the default ORACLE_HOME directory to remain an older Oracle 9i version of the client. Because of this, our applications using the Managed Oracle Provider for .net are using the 9i client to connect. We have a planned Oracle upgrade for our Database that will require us to use the new 10g version of the client that is also installed on the server, bu...

Web resources about - How To call Oracle function from ADO.NET? -

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 ...

Resource sector may outsource finance functions to Asia
Outsourcing financial functions to Asia is one tactic struggling Australian energy and resources companies may use this year to survive the slump ...

Using Functions to Make Better Abstractions
... (code that is tightly coupled to its own implementation details). I was wrong. I was wrong because I had an incomplete understanding of functions. ...

Media devices sold to feds have hidden backdoor with sniffing functions
... AMX NX-1200 , a programmable device used to control AV and building systems. The researchers first became suspicious after encountering a function ...

A Non-Sokratic Dialogue on Social Welfare Functions: Hoisted from the Archives from 2003
**[A Non-Sokratic Dialogue on Social Welfare Functions: Hoisted from the Archives from 2003]( ...

Drop Point Knives: Function, Fit, Timeless
Drop Point Knives: Function, Fit, Timeless

Here's how squads and iron sights will function in Rising Storm 2: Vietnam
... roles and create a squad, and the creator can name, lock, or invite others to their little group. The more an outfit fills up, the more functions ...

Yahoo Begins Grim Job of Closing Functions, Laying Off Staff
Yahoo Food is closing, and other digital magazines on tech, travel and beauty are also scheduled for closing or major cutbacks.

Don’t tase me, bro: Study shows being shocked by a Taser disrupts brain function
Researchers at Drexel shot a bunch of college students with 50,000 volts. The result? Short-term declines in cognitive function comparable with ...

How The U.S. Supreme Court Will Function Without Justice Scalia
... but in a fashion that could have frustrating ramifications for a number of major cases set to be decided. Here's how the court will function, ...

Resources last updated: 2/18/2016 4:09:33 PM