Oracle function call from C#, return DataSet? Pipelined

Got one for the Oracle/C# gurus.

 I have a function setup in my Oracle 10G database that returns a pipelined dataset.  The function takes one input parameter in VARCHAR2 format, and runs fine from TOAD.  I'm using .NET 2.0 and System.Data.OracleClient, and this is the code in question:


1    DataTable dt = new DataTable();
3    OracleConnection Conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
4    OracleCommand DbComm = new OracleCommand("SCHEMA.MYFUNCTION", Conn);
5    DbComm.CommandType = CommandType.StoredProcedure;
7    OracleParameter inVar = new OracleParameter("VAR", OracleType.VarChar);
8    inVar.Direction = ParameterDirection.Input;
9    inVar.Value = "999";
10  DbComm.Parameters.Add(inVar);
12   //I've tried this block of code:
13   Conn.Open();
14   OracleDataAdapter adapter = new OracleDataAdapter(DbComm);
15   adapter.Fill(dt);
16   Conn.Close();
18   //And also tried this:
19   Conn.Open();
20   OracleDataReader reader = DbComm.ExecuteReader();
21   dt.Load(reader);
22   reader.Close();
23   Conn.Close();
This is the result:

ERROR: ORA-06550: line 1, column 7: PLS-00221: 'MYFUNCTION' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Any hints?  I haven't seen anywhere on the net where there is an example of a C# call to a Oracle FUNCTION that returns a dataset.....

Software Engineer
9/20/2007 8:54:00 PM 2269 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 41

Not saying that it's not possible, but the only time I've used or seen functions called in my code or code I inherited is within sql statments (i.e. select date_time, schema.myfunction(some_value), result from table_A where....

I would create a stored procedure that calls that function. Then you can call the SP from your code and pass it your variable(s).

Life would be so much easier if we only had the source code.
9/20/2007 9:23:55 PM

I guess I could setup a procedure that returns a cursor with data from the select statement, but I was hoping not to modify the database function, and there goes my pipelining.  I'll try that in the morning tomorrow and see if it works.

Software Engineer
9/20/2007 10:53:25 PM

Thanks jMac.  I ended up just creating an Oracle Procedure that returns a SYS_REFCURSOR to the .Net page with data from the select statement.  Seems to be working well!  I do lose pipelining, but I do away with the 2 other datatypes necessary for each pipelined function.

Software Engineer
9/21/2007 2:31:33 PM

Try this: 

1    DataTable dt = new DataTable();
3    OracleConnection Conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
4    OracleCommand DbComm = new OracleCommand("SCHEMA.MYFUNCTION", Conn);
5    DbComm.CommandType = CommandType.StoredProcedure;
7    OracleParameter inVar = new OracleParameter("VAR", OracleType.VarChar);
8    inVar.Direction = ParameterDirection.Input;
   inVar.Value = "999";
10  DbComm.Parameters.Add(inVar);
11  DbComm.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.ReturnValue;

19   Conn.Open();
20   OracleDataReader reader = DbComm.ExecuteReader();
21   dt.Load(reader);
22   reader.Close();
23   Conn.Close();


4/24/2008 2:00:05 AM

Similar Artilces: 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...

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

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

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

Anyone have PB9 called Oracle SP that calls c that returns values successfully?
Asking this for a coworker who's going crazy trying to figure out the problem He has PB9 calling Oracle SP that's calling a c function that was compiled using Oracle Pro C/C++ Precompiler. He's wanting the out variables returning data but they don't. I'm not really familiar with the Oracle parameter types so I'm not sure what the problem is: -- the sp as defined in our oracle 9i db CREATE OR REPLACE function job_description(is_spw varchar2, is_suserid varchar2, li_study_seq binary_integer, ls_survey_type varchar2, ls_item varchar2, ls_case_grp varchar2,...

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

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

calling a oracle function in a package from C#
Hi,  I've developed a function and placed the same inside a package in oracle.  How to call it from c# using command object  ?  i've tried assigning the property  Command.CommandType = CommandType.StoredProcedure and CommandType.Text. But it didn't work.  Can any one suggest me the ways to achieve calling the package function.  Thanks and Rgds,  Sree    This is same as calling any other stored procedure: OracleCommand cmd = new OracleCommand(); cmd.Connection=OraConn; cmd.CommandText="Username.Pa...

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. Thanks 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. Hi, Or you can use ExecuteNonQuery and access the return param directly. Example follows. Greg /* create or replace function myf...

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

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) + ",...

Getting Oracle Function return in C# Code
Hi, I have a function in Oracle to return a VarChar2 value to my C# code behind. Problem is when I run my Function in the DataBase, it works fine but when I run my code to get the return value and display it, I do not get a value returned. Does anyone know how to handle this in C# please?? C# Code below. Thanks in Advance.  private void btnSubmit_Click(object sender, EventArgs e) { try {conn = new OracleConnection(ConfigurationManager.ConnectionStrings["Info1"].ConnectionString);if (conn.State == ConnectionState.Closed) conn.Open(); cmd = new OracleCommand("BEET...

Calling an Oracle 10g function with a NUMBER return
I am trying to get a return value from an Oracle 10g database function that accepts a group of string parameters and returns a NUMBER value using a TADOStoredProc in D2007 (win32). I've tried using both the Oracle 9 provider and the MS provider for Oracle. I either get access violation or it returns a zero. I've disabled BCD so I should be getting a valid Double. The data that I'm passing to the function does get posted (It's an logging function) but I need the return as it's the key for completing the log when the program terminates. I've tested a function call t...

calling an oracle pl sql function from a c# method.
i've been calling my functions in c# using the select function_Name from dual , but i'm in a spot of bother now as i cant do this with the boolean function. The reason i did this is because i could not get the code that is used to execute a stored procedure to work with the functions. The code for that includes an execute section that is included for the procedure part.My issue: Is there a simple way to call a boolean oracle function from c# that takes three parameters and returns either true or there an easy way to execute functions from oracle, all the research  and code sa...

Web resources about - Oracle function call from C#, return DataSet? Pipelined -

Stack Exchange Open Source Projects
... now it’s become an absolutely critical and totally indispensable part of our infrastructure, much like HAProxy . We use Booksleeve for pipelined, ...

Cell (microprocessor) - Wikipedia, the free encyclopedia
Cell is a microprocessor architecture jointly developed by Sony , Sony Computer Entertainment , Toshiba , and IBM , an alliance known as "STI". ...

Central processing unit - Wikipedia, the free encyclopedia
The central processing unit ( CPU ) is the portion of a computer system that carries out the instructions of a computer program , to perform ...

New CmdLets of Powershell 2.0
New CmdLets of Powershell 2.0

AnandTech - Apple iPad 2 Preview
I remember the speculation that lead up to Apple's iPad launch. The list of things everyone expected the device to do was absurd, and the theories ...

Pipelining: An Overview (Part I)
Ars CPU Editor Jon Stokes looks at pipelining in the first of a two-part …

The three big ways Amazon's Kindle Fire wins
Online retailer Amazon on Wednesday officially launched its first Android tablet, the 7" Kindle Fire. Taking its name from the company's already ...

Camera - BLITZ CAM
Camera IMAGE LAB Press & Downloads Contact KickStarter Camera Specs Resolution 2048 x 1088 aspect – 2048 x 360 aspect – 2.39:1 crop ration of ...

Towards better refactoring support in IDEs for functional programming
... to"Name not present") The advantage with the latter is that it's more composable and can be pipelined ...

How redBus uses BigQuery to master big data
... it to build internal dashboards that give us a snapshot of system health. For more information on how we structured our immutable tables, pipelined ...

Resources last updated: 12/17/2015 1:14:14 PM