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.Odbc" %>
<script runat="server">
Sub Page_Load(obj as Object, e as EventArgs)
if not Page.IsPostBack then
Dim strConnect As String
strConnect = ConfigurationSettings.AppSettings("DSNSTRING")
CreateMyOdbcCommand(strConnect)


end if
End Sub
Sub CreateMyOdbcCommand(myConnectionString As String)
Dim myConnection As New OdbcConnection(myConnectionString)
myConnection.Open()
Dim myCommand As OdbcCommand = myConnection.CreateCommand()
myCommand.CommandText = "Stored Function"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("RetVal", OdbcType.Int).Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add("Id", odbcType.Numeric, 10).value = 21376
myCommand.ExecuteNonQuery()
lblMessage.Text = myCommand.Parameters("RetVal").value

MyConnection.Close()
End Sub

</script>
<html>
<body>
<asp:Label id="lblMessage" runat="server" />
</body>
</html>
ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
Source Error:

Line 30: myCommand.Parameters.Add("Id", odbcType.Numeric, 10).value = 21376
Line 31:
Line 32: myCommand.ExecuteNonQuery() <--this is where the error occured
Line 33: lblMessage.Text = myCommand.Parameters("RetVal").value
Line 34:

0
ted
10/23/2003 1:21:47 PM
asp.net.getting-started 91979 articles. 4 followers. Follow

7 Replies
1483 Views

Similar Articles

[PageSpeed] 12

Is you sProc name "Stored Function"
Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb
0
SushilaSB
10/23/2003 1:24:34 PM
Hi, 

I am using
myCommand.CommandText = "JF_GETACLEVEL"
JF_GETACLEVEL is the name of the stored function. But that still give me the error.
Ted
0
ted
10/23/2003 2:30:01 PM
Can anyone tell me what SushilaSb means? Thanks,

Ted
0
ted
10/27/2003 12:19:21 PM
As you had given your SP name as


myCommand.CommandText = "Stored Function"

so was just wondering if you gave it "AS IS"
Are you sure your SPROC is giving the proper desired O/P

Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb
0
SushilaSB
10/27/2003 12:36:45 PM
Hi This is my stored function

CREATE OR REPLACE FUNCTION JF_GetAcLevel (in_ID IN NUMBER) RETURN INTEGER IS
....
Begin
....
Exception When others return
-999;
End;
/
0
ted
10/27/2003 1:01:28 PM
Hi Guys,

I made it worked by the following code.
Thanks,
Ted
<%@ 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.Odbc" %>
<script runat="server">
Sub Page_Load(obj as Object, e as EventArgs)
Dim myConnection As OdbcConnection = New OdbcConnection(ConfigurationSettings.AppSettings("DSNSTRING"))
Dim strSQL as String
strSQL = "{? = CALL JF_GETACLEVEL(?)}"
Dim MyCommand as OdbcCommand = New OdbcCommand(strSQL, myConnection)
Dim param As OdbcParameter
param = myCommand.Parameters.Add("@RetVal", OdbcType.Numeric)
param.Direction = ParameterDirection.ReturnValue
param = myCommand.Parameters.Add("@cid", OdbcType.Numeric, 10)
param.Value = 21376
myConnection.Open()
myCommand.ExecuteNonQuery()
lblMessage.Text = MyCommand.Parameters("@RetVal").value
myConnection.Close()
End Sub
</script>
<html>
<body>
<asp:Label id="lblMessage" runat="server" />
</body>
</html>
0
ted
10/28/2003 2:08:12 PM

Hello,

is there any other way to call without using DSN..?

=UdayRaje

0
udkadam
2/23/2007 8:42:35 AM
Reply:

Similar Artilces:

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

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

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 ado.net. 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  vb.net code Protected Overrides Sub OnLoad(ByVal e As EventArgs) Dim cnn As OracleConnection Dim sConnSt...

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

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

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 to Retrive the store procedure return value in Ado.Net by using Dot Net?
Hi i am thiru. i try to create the small search engine. so that i have wrote the store procedure for it. thats given below.Create procedure FindtestThree @Name varchar(25), @Location varchar(25), @EmpId varchar(25) as select * from TbInfo where Name like + '%' + @Name + '%' select * from TbInfo where Location like + '%' + @Location + '%' select * from hi where ComName like + '%' + @EmpId + '%' Exec FindtestThree 'aa','a','d' ----------------------------------------- The above procedure return t...

Cannot assign Null to stored procedure called by ODBC.NET
Hi all, I have a problem assigning null value to stored procedure. Here is the code and I get the error message "invalid buffer length passed to a conversion routine". Any ideas? I tried "Convert.DBNull" as well. Thanks in advance, Ted <%@ 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&q...

Report based on Oracle Stored Procedure -- call from .NET
I've developed a report in Crystal Reports 10, and now want to add it to my Web Application. When I call the report, with the parameters, the problem I hit is it asked you to continually log into the Database itself. I'd like to set all the connection properties in .NET such that it seems seemless to the user. Robert, I've moved your post from the Crystal Reports forum to this Oracle forum. Your question is really about the connection to Oracle, so I think you're much more likely to receive help here in this forum.Alister I just now came across the following that may help you: Using...

Error while calling .Net web service from Oracle Stored Procedure
Hi,     We are getting the below error while calling an .net web service ( WCF) from Oracle Stored procedure.  Type: System.Exception, mscorlib, version = 2.0.0.0, culture = neutral,publickeytoken=b77a5c561934e089 Message: No client Registry found Data: System.Collections.ListDictionaryInternal Any information on the above will be helpful. Thanks. Sowmya Jayappa  ...

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

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Problem in getting Oracle function return value using ODP.net
 Dear AllI 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 Resu...

Web resources about - how to call an oracle stored function/procedure using ODBC.NET - asp.net.getting-started

Resources last updated: 12/4/2015 3:14:12 AM