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;
            try
            {
                string UpdateResult = "";
                if (orConn != null)
                    if (orConn.State == ConnectionState.Open) orConn.Close();
                orConn = new OracleConnection(connString);
                string strQuery = SELECT citi_registration_p.update_member_info_detail(?, ?, ?, ?, ?, ?, ?, ?, ?) FROM DUAL;
                OracleCommand cmdInfoDetail;
                cmdInfoDetail = new OracleCommand(strQuery, orConn);
                cmdInfoDetail.CommandType = CommandType.StoredProcedure;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_org_id", OracleType.Number)).Value = org_id;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_account_id", OracleType.Char)).Value = account_id;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_member_info_detail_id", OracleType.Number)).Value = member_info_detail_id;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_account_detail_info_id", OracleType.Number)).Value = account_detail_info_id;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_detail_desc", OracleType.VarChar)).Value = detail_desc;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_default_ind", OracleType.Number)).Value = default_ind;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_active_ind", OracleType.Number)).Value = active_ind;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_user_id", OracleType.Number)).Value = user_id;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_host", OracleType.VarChar, 500)).Value = created_at;
                cmdInfoDetail.Parameters.Add(new OracleParameter("v_output", OracleType.Number)).Direction = ParameterDirection.ReturnValue;
                orConn.Open();
                cmdInfoDetail.ExecuteNonQuery();
                int outvalue = (int)cmdInfoDetail.Parameters["v_output"].Value;
                return outvalue.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally { orConn.Close(); }
}

 Oracle function is 'update_member_info_detail'.

Pls, guide me, how to pass value to the question mark field.

 

Thanks for your help in advance. 

When executing 'cmdInfoDetail.ExecuteNonQuery();' line i got the below eror:

ORA-06550: line 1, column 20:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
ORA-06550: line 1, column 73:
PLS-00103: Encountered the symbol "?" when expecting one of the following:

   ( ) - + case mod new not null others select <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   table avg count current exists max m

 


Regards,
Suriyanarayana.N
0
suriya_wnm
11/6/2008 12:33:15 PM
asp.net.oracle 2269 articles. 0 followers. Follow

7 Replies
1241 Views

Similar Articles

[PageSpeed] 37

You are trying to send SQL command (SELECT query) as the command text where as you have set the command type as stored procedure

cmdInfoDetail.CommandType = CommandType.StoredProcedure;

you'll have to change that to CommandType.Text

check out

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtype.aspx

for more details

 


Vishal Shukla
http://www.geekswithblogs.com/vishal/
0
vishalshukla
11/6/2008 1:28:36 PM

 My oracle function is:

 FUNCTION update_member_info_detail (
      v_org_id                   citi_member_info_detail.org_id%TYPE,
      v_account_id               citimember_info_detail.account_id%TYPE,
      v_member_info_detail_id    citimember_info_detail.member_info_detail_id%TYPE,
      v_account_detail_info_id   citi_member_info_detail.account_detail_info_id%TYPE,
      v_detail_desc              citi_member_info_detail.detail_desc%TYPE,
      v_default_ind              citi_member_info_detail.default_ind%TYPE,
      v_active_ind               citi_member_info_detail.active_ind%TYPE,
      v_user_id                  citi_users.user_id%TYPE,
      v_host                     citi_users.created_at%TYPE
   )
      RETURN NUMBER;


i am getting error only at the line ExecuteNonQuery() and its saying error for the '?' marks:

SELECT citi_registration_p.update_member_info_detail(?, ?, ?, ?, ?, ?, ?, ?, ?) FROM DUAL;

guide me.


Regards,
Suriyanarayana.N
0
suriya_wnm
11/6/2008 2:08:04 PM

As Vishal stated above, you cant set command type to StoredProcedure if you're passing a select statement.  You can either 1) use CommandType.Text and pass a select in which case you wouldnt use ExecuteNonQuery and wouldnt add a ReturnValue parameter, or 2) use CommandType.StoredProcedure and pass just the <package.>procedure name and add a parameter of type ReturnValue.

 Also, using ? for placeholders is an odbc/oledb thing.  You need to use :parametername instead. 

  If using System.Data.OracleClient the names need to match the stored proc param names.  If using Oracle.DataAccess.Client, the ReturnValue paramter needs to come first.

 Hope it helps,
Greg

0
greg
11/6/2008 4:46:38 PM

 Hi Greg,

Thanks for your valuable help.

i want to use OracleClient, can u please rearrange my coding.

i tried, but i am not sure whether that is correct. 

Pls, don't mistake. can u please correct my coding using OracleClient.

 

 

 


Regards,
Suriyanarayana.N
0
suriya_wnm
11/6/2008 4:57:46 PM

 hi, thanks for your help.

now, i changed my coding as below:
my oracle function is:
FUNCTION update_member_info_detail (
      v_org_id                   citi_member_info_detail.org_id%TYPE,
      v_account_id               citimember_info_detail.account_id%TYPE,
      v_member_info_detail_id    citimember_info_detail.member_info_detail_id%TYPE,
      v_account_detail_info_id   citi_member_info_detail.account_detail_info_id%TYPE,
      v_detail_desc              citi_member_info_detail.detail_desc%TYPE,
      v_default_ind              citi_member_info_detail.default_ind%TYPE,
      v_active_ind               citi_member_info_detail.active_ind%TYPE,
      v_user_id                  citi_users.user_id%TYPE,
      v_host                     citi_users.created_at%TYPE
   )
      RETURN NUMBER;

C# 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 = new OracleConnection(connString);
                orConn.Open();
                strQuery = TableDef.UPDATE_MEMBER_INFO_DETAIL_FUN();
                OracleCommand cmdInfoDetail;
                cmdInfoDetail = new OracleCommand("citi_registration_p.update_member_info_detail", orConn);
                cmdInfoDetail.CommandType = CommandType.StoredProcedure;
                cmdInfoDetail.Parameters.AddWithValue("v_org_id", org_id);
                cmdInfoDetail.Parameters.AddWithValue("v_account_id", account_id);
                cmdInfoDetail.Parameters.AddWithValue("v_member_info_detail_id", member_info_detail_id);
                cmdInfoDetail.Parameters.AddWithValue("v_account_detail_info_id", account_detail_info_id);
                cmdInfoDetail.Parameters.AddWithValue("v_detail_desc", detail_desc);
                cmdInfoDetail.Parameters.AddWithValue("v_default_ind", default_ind);
                cmdInfoDetail.Parameters.AddWithValue("v_active_ind", active_ind);
                cmdInfoDetail.Parameters.AddWithValue("v_user_id", user_id);
                cmdInfoDetail.Parameters.AddWithValue("v_host", created_at);
                int outvalue = cmdInfoDetail.ExecuteNonQuery();
}


when i reach, cmdInfoDetail.ExecuteNonQuery(); line i am getting following error:
ORA-06550: line 1, column 7:
PLS-00221: 'UPDATE_MEMBER_INFO_DETAIL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Pls, guide me


Regards,
Suriyanarayana.N
0
suriya_wnm
11/7/2008 2:27:04 AM

Hi,

  You're getting that error because Oracle distinguishes between PROCEDUREs and FUNCTIONs. To call a function with CommandType.StoredProcedure, you need to add a paramter of ParameterDirction.ReturnValue as I stated above.

   See if this helps.

Greg

 

 

/*
create or replace function testfunc(v1 in varchar2) return varchar2 is
begin
 return v1 || ' from the proc';
end;
/
*/
using System;
using System.Data;
using System.Data.OracleClient;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl"))
            {
                con.Open();
                using (OracleCommand cmd = new OracleCommand("", con))
                {
                    cmd.CommandText = "testfunc";
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter retval = new OracleParameter("retval", OracleType.VarChar, 4000);
                    retval.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(retval);
                    cmd.Parameters.AddWithValue("V1", "Hello");
                    cmd.ExecuteNonQuery();
                    Console.WriteLine(retval.Value.ToString());
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

 

OUTPUT
=========

Hello from the proc
Press any key to continue . . .

0
greg
11/7/2008 11:09:32 PM

 Hi Greg,

 

Thanks u very much.

 

 


Regards,
Suriyanarayana.N
0
suriya_wnm
11/8/2008 3:40:48 AM
Reply:

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

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

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

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

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

Help me pls. i wan't code for startup and shutdown oracle database with vb.net or c#.net
Help me pls. i wan't code for startup and shutdown oracle database with vb.net or c#.net In Windows, it would be easier to interact with Oracle by way of the service...  (I've not tried it myself, but this should work fine for a service installed on the same machine as the .net application:using System.ServiceProcess; ServiceController controller = new ServiceController(); controller.MachineName = "."; controller.ServiceName = "OracleServiceORCL"; string status = controller.Status.ToString(); // Stop the service controller.Stop(); // Start the ...

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

use VB.NET and C#.NET code in the same C#.NET project
All-- Here is a sample that is "off the beaten path", (at least for me). Is it possible, in an ASP.NET application, using the code-behind page building technique, to have both pages written in VB.NET and pages written C#.NET?At http://www.WebLogicArts.com/DemoList.aspx there is a sample that shows that, (contrary to popular belief), it IS possible to mix ASP.NET pages built with C#.NET with ASP.NET pages built with VB.NET in the same VS.NET 2003 project. Note that this is just a "fun" sample to see if it can be done and I do not recommend this practice as a "standard" way of develo...

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

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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp Oracle http://otn.oracle.com/software/tech/windows/odpnet/utilsoft.html 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 C#.NET WinForms
What I am working on in a winforms app written in C#.NET which makes use of loading data into Oracle 8i/9i databases. The table where this data ends up has three CLOB fields. The winforms app that I have written consists of 6 different classes that represent 6 different objects. Each object has a property that returns either a Insert of an Update query. My question is apart from using Parameterized queries and inline sql, are there any other options available that would let me get the appropriate query through the appropriate method? What I will end up doing is load all the queries into...

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

Web resources about - C#.NET with Oracle Functions - asp.net.oracle

Talk:Trigonometric functions - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Mathematics , a collaborative effort to improve the coverage of Mathematics on Wikipedia. If ...

Four arrested after wedding brawl outside Westella Renaissance function centre
A crowd of people watched on as a violent brawl erupted between two groups outside a wedding reception venue in Sydney's west.

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

Facebook Updates Search Function; Now Is A Great Time To Run And Check All Your Privacy Settings
Facebook search is… well, kind of a joke. It can tell you which 400 people in your area have similar names to that one person you want to connect ...

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

Add ‘laser beam’ to your iPhone’s long list of functions
Whether for presentations or driving your cat up the wall, laser pointers are as fun as they are useful. Given everything else an iPhone can ...

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

Resources last updated: 11/29/2015 5:11:51 PM