how to call an oracle function

how do i call an oracle function from a c# asp.net application?

i need to call:

ops$sqltime.pa_new_job_no_fn

which should return the next job_no. given a project_no. thanks in advance.

0
realnewbie
5/24/2005 3:02:06 PM
asp.net.oracle 2269 articles. 0 followers. Follow

10 Replies
895 Views

Similar Articles

[PageSpeed] 22

// not tested, but should work
OracleCommand cmd       = new OracleCommand("ops$sqltime.pa_new_job_no_fn","connection_string");
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_number = new OracleParameter();
p_numberl.OracleDbType = OracleDbType.Decimal;
p_number.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_number);
// open database connection
cmd.ExecuteScalar();
int next_number = p_number.Value;

##########################

 realnewbie wrote:

how do i call an oracle function from a c# asp.net application?

i need to call:

ops$sqltime.pa_new_job_no_fn

which should return the next job_no. given a project_no. thanks in advance.


--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
5/25/2005 11:09:07 AM
i tried the code but keep getting build errors. i changed the code in an effort to get rid of the errors but unsuccessfull. one modification leads to a different build error.
0
realnewbie
6/2/2005 5:50:44 PM
what is the error message?

Are your returning a single column and a single value? If not, you have to ExecuteNonQuery(), not ExecuteScalar().

Post your code and error message.


--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
6/2/2005 6:46:17 PM
whether i'm using executescalar() or executenonquery(), i'm getting the same exception:
ORA-06550: line 1, column 26: PLS-00306: wrong number or types of arguments in call to 'PA_NEW_JOB_NO_FN' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

my code:
OracleCommand cmd = new OracleCommand     ("ops$sqltime.pa_new_job_no_fn", oraConn);

cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_number = new OracleParameter();
p_number.OracleType = OracleType.Int32;
p_number.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_number);
// open database connection
cmd.ExecuteScalar();
int ljn = (int) p_number.Value;
0
realnewbie
6/2/2005 7:12:14 PM
 realnewbie wrote:
whether i'm using executescalar() or executenonquery(), i'm getting the same exception:
ORA-06550: line 1, column 26: PLS-00306: wrong number or types of arguments in call to 'PA_NEW_JOB_NO_FN' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

my code:
OracleCommand cmd = new OracleCommand     ("ops$sqltime.pa_new_job_no_fn", oraConn);

cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_number = new OracleParameter();
p_number.OracleType = OracleType.Int32;
p_number.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_number);
// open database connection
cmd.ExecuteScalar();
int ljn = (int) p_number.Value;

The code snippet looks fine. The error message usually indicates that you are passing in parameters in the incorrect order.

Either post your Oracle code or check that the parameters are being passed in correctly.

Also, are you using ODP.NET or Microsoft's Oracle provider? I have heard of people getting  an error when using a function with the Microsoft data provider -- evidently it is expecting a procedure and not a function.



--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
6/2/2005 10:15:56 PM

i am using microsoft's oracle provider. here is the oracle funtion:

 
             (p_project_no            IN     varchar2) return varchar2 is
-- ***************************************************************************
-- DESCRIPTION: This procedure is called by the rollout process. Given a project no,
-- it will determine a unique job_no for that project.

--  Version     Date        Who      Description of Change
-- -------    --------      ---      --------------------------------
-- 5.1.3      11/08/04      DL-JJT   Changed f(x) to return '101' instead
--                                   of '1' if l_return is null
-- 51.1.02    08/24/98      GSB      :6702: Check pa_job for duplicates
--                                   before returning the job_no.
-- 8.01.XX    07/10/98      GSB      :3121: Initial Creation

l_count                number;
l_last_job_no          number := 0;
l_this_job_no          number;
l_return               pa_job.job_no%type;

cursor c1 is
select job_no
from pa_job
where project_no = p_project_no
order by job_no desc;

r1    c1%rowtype;

--Main
BEGIN
   open c1;
   loop
      fetch c1 into r1;
      exit when c1%notfound or l_return is not null;

      --This will test if the job_no returned is numeric. If it isn't
      --skip on to the next one.
      begin
         l_this_job_no := NULL;
         l_this_job_no := TO_NUMBER(r1.job_no);
      exception
         when others then
         null;
      end;

      if l_this_job_no is not null
         and l_this_job_no != 9999 then
         --We have a job that is entirely numeric. So let's add 1 to it, and
         --test for the existence of the new job no. We do this by comparing
         --to the last job_no we looked at. If it passes that
         if l_this_job_no + 1 != l_last_job_no
            and l_this_job_no != l_last_job_no
            then
            --Now check the database:
            select count(*)
              into l_count
              from pa_job
             where project_no = p_project_no
               and job_no = to_char(l_this_job_no + 1);
            if l_count = 0
               then
               l_return := TO_CHAR(l_this_job_no + 1);
            else
               l_last_job_no := l_this_job_no;
            end if;
         else
            l_last_job_no := l_this_job_no;
         end if;
      end if;
   end loop;

   close c1;

   --If l_return is null, we know this project has no numeric jobs, so we are
   --safe returning a 1.
   l_return := NVL(l_return,'101');
   RETURN(l_return);
END;

0
realnewbie
6/3/2005 1:34:19 PM
Your function is returning a VARCHAR2 datatype.

In your client code you have the return value defined as OracleType.Int32;

This is mostly likely causing your problem. Which would explain the error message indicating a problem with line 1.

Change the return value parameter to OracleType.Varchar2;
Also, change the variable ljn from int to string.

I didn't really look at the rest of your code in the funtion, but see if this fixes your problem.




--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
6/3/2005 2:33:57 PM
i changed it to varchar but still getting the same exception.

my code:

OracleCommand cmd = new OracleCommand("ops$sqltime.pa_new_job_no_fn", oraConn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_number = new OracleParameter();
p_number.OracleType = OracleType.VarChar;
p_number.Size = 4000;
p_number.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_number);
// open database connection
cmd.ExecuteScalar();
string l = (string) p_number.Value;
0
realnewbie
6/3/2005 2:44:56 PM
 realnewbie wrote:
i changed it to varchar but still getting the same exception.

my code:

OracleCommand cmd = new OracleCommand("ops$sqltime.pa_new_job_no_fn", oraConn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_number = new OracleParameter();
p_number.OracleType = OracleType.VarChar;
p_number.Size = 4000;
p_number.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_number);
// open database connection
cmd.ExecuteScalar();
string l = (string) p_number.Value;


I don't have Microsoft's Oracle client installed so I cannot test your code the same way as you as running it. But try this...

change string l = (string)p_number.Value;  TO  string l = p_number.Value.ToString();

Also, change the order of your parameters. Your function shows an input parameter, but I do not see it in your client code.
Also, the error message you receive said something about line 1 column 26...I would look at this location in your PL/SQL code, it should give you an indication of where the problem.

You may also want to post ALL of your client code, what you have posted thus is incomplete.


--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
6/3/2005 4:33:43 PM
problem is solved. i changed 
 par1  = new OracleParameter("PROJECT_NO", OracleType.VarChar);
to
 par1  = new OracleParameter("P_PROJECT_NO", OracleType.VarChar);
and now it's working. thanks dweezil.

my code:

OracleParameter par1 = new OracleParameter("RETURN_VALUE", OracleType.VarChar);
par1.Direction = ParameterDirection.ReturnValue;   
par1.Size = 4000;
cmd.Parameters.Add(par1);
par1 = new OracleParameter("P_PROJECT_NO", OracleType.VarChar);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
par1.Size = 4000;
cmd.Parameters.Add(par1);
cmd.ExecuteScalar();
string l = cmd.Parameters["RETURN_VALUE"].Value.ToString();
0
realnewbie
6/3/2005 4:42:11 PM
Reply:

Similar Artilces:

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

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

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

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

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

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

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

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

Calling Oracle Stored Function calls using JDBC?
To prepare our application for Appeon Server, we are changing our Oracle DB interaction from Native Oracle Driver to using JDBC so we can deploy the app on Appeon. Does someone know the SYNTAX on how to call oracle stored function from PB using JDBC. Some sample code would be apprecaited.. ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp This is a multi-part message in MIME format. ------=_NextPart_000_0469_01C40859.331BADA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quote...

Oracle function call
------=_NextPart_000_512F_01C54762.D2410210 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Disclaimer:=20 This message and any attachment(s) contained here are information that = is confidential, proprietary to HCL Technologies and its customers. = Contents may be privileged or otherwise protected by law. The = information is solely intended for the individual or the entity it is = addressed to. If you are not the intended recipient of this message, you = are not authorized to read, forward, print, retain, copy or disse...

call oracle function
hi there I have a select statement (oracle) in a datawindow. in this sql statement I want to use a function I have defined in oracle. With SQL+ I can execute this function without any problem. But in a datawindow there happens nothing. no error and the other select data are displayed correctly. only the data from the functio is null?????? Any help ? Thank you Markus Works for us when the function returns a valid value when called with appropriate arguments. Can you run the SQL [not just the function call] from SQL+? Please post the SQL from the datawindow and the funct...

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