How to call a SQL function which returns a cursor using c#

Hi i am new to .net. Can someone please advise how to call a SQL function which returns a cursor using c# i have tried to call using OracleCommand.CommandType = CommandType.StoredProcedure OracleParameter p_case_action_id = new OracleParameter("@p_case_action_id", OracleDbType.Int32); p_case_action_id.Direction = ParameterDirection.Input; p_case_action_id.Value = intSourceID; orlCmd5.Parameters.Add(p_case_action_id); but it's throwing an exception saying invalid arguments or saying the function name is invalid i even tried to call the function using CommandText rather than storedprocedure and the result is same errors Function -------------- FUNCTION get_student_data(studentid IN NUMBER) RETURN case_cursor;

Posted On: Wednesday 7th of November 2012 11:18:29 AM Total Views:  616
View Complete with Replies

Related Messages:

Problem calling an AS400 program from ASP.NET   (466 Views)
I am trying to call an RPG (AS400) program from a VB.Net application and continue to get the following error message: IBM.Data.DB2.iSeries.iDB2SQLErrorException SQL0204 OPS010 in type *N not found. at IBM.Data.DB2.iSeries.iDB2Exception.throwDcException(MpDcErrorInfo mpEI, MPConnection conn) at IBM.Data.DB2.iSeries.iDB2Command.reportDCError(Int32 rc) at IBM.Data.DB2.iSeries.iDB2Command.Prepare() at IBM.Data.DB2.iSeries.iDB2Command.ExecuteScalar() My code is listed below. Any help on this would be greatly appreciated. prgCmdDB2.Connection = cn2DB2 prgCmdDB2.CommandType = CommandType.Text prgCmdDB2.CommandText = "CALL OPS010(,,,,,)" Dim parCompany As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parCompany.ParameterName = "Company" parCompany.Value = dr.Item("O1CMPY") parCompany.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Char Dim parOrder As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parOrder.ParameterName = "Order" parOrder.Value = dr.Item("ORDER") parOrder.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Decimal parOrder.Size = 7 Dim parLine As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parLine.ParameterName = "Line" parLine.Value = dr.Item("O1LINE") parLine.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Decimal parLine.Size = 3 Dim parOrdValue As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parOrdValue.ParameterName = "OrderValue" parOrdValue.Value = "" parOrdValue.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Decimal parLine.Size = 17 Dim parType As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parType.ParameterName = "Type" parType.Value = "B" parType.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Char parLine.Size = 1 Dim parOrdQty As IBM.Data.DB2.iSeries.iDB2Parameter = New IBM.Data.DB2.iSeries.iDB2Parameter parOrdValue.ParameterName = "OrderQty" parOrdValue.Value = "" parOrdValue.iDB2DbType = IBM.Data.DB2.iSeries.iDB2DbType.iDB2Decimal parLine.Size = 17 prgCmdDB2.ExecuteNonQuery()
Programmatically export mailbox via Powershell - error -2147219963   (416 Views)
, we are trying to automate the user account management using Powershell to handle all interactions with Exchange 2007. The powershell commands are launched from a webservice developed in ASP.NET (C#). The project is practically over aside from the export of the mailbox. When I try to execute the following command: export-mailbox -identity "Mister x" -baditemlimit 1147483648 -pstfolderpath "d:\path\MisterX.pst" We get an error: Export-Mailbox error results: Error was found for MisterX ( because: An unknown error has occurred., error code: -2147219963 The same command runs fine from the exchange powershell console (same user, same server) We also noticed the command completes successfully if I first run the exact same command directly on powershell (from the same server) and then leave the console window open. If I close the console, the application starts failing again with the same error. If I run the application pool that owns the webservice with Domain Admin account, everything works fine but the user I would like to use has all the necessary rights. I get the feeling Powershell in order to function properly needs a session open in a specific way that I do not understand. Here following the relevant portion of code: RunspaceConfiguration runspaceConf = RunspaceConfiguration.Create(); PSSnapInException PSException = null; PSSnapInInfo info = runspaceConf.AddPSSnapIn("Microsoft.Exchange.Management.PowerShell.Admin", out PSException); Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConf); runspace.Open(); System.Management.Automation.SwitchParameter spConfirmYes; int iNbmaxerreurs = 1147483648; spConfirmYes = false; //Always False Pipeline pipelineEM = runspace.CreatePipeline(); pipelineEM.Commands.Add("Export-Mailbox"); pipelineEM.Commands[0].Parameters.Add("Identity", MailID.ToString()); pipelineEM.Commands[0].Parameters.Add("BadItemLimit", iNbmaxerreurs); pipelineEM.Commands[0].Parameters.Add("Confirm", spConfirmYes); pipelineEM.Commands[0].Parameters.Add("PSTFolderPath", sExportfilePath.ToString() + ExportfileName.ToString() + ".pst"); // Result Recuperation: System.Collections.ObjectModel.Collection psobjResults; System.Collections.ObjectModel.Collection objResultserror; psobjResults = pipelineEM.Invoke(); objResultserror = pipelineEM.Error.ReadToEnd();
How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)?   (184 Views)
I try to call Oracle Function in .net C# by using System.Data.OracleClient. But i am not successful. Here is my code FUNCTIONTTHAO_MONTT_EXISTS (vtenvarchar2) RETURN booleanIS ret boolean; montt_idinteger; BEGIN ret:=false; montt_id:=-1; selectmonthethao_idintomontt_idfromTTHAO_MONTHETHAO whereten=vten; ifmontt_id!=-1then ret:=true; endif; returnret; Exception whenothersthen returnfalse; END; I do not know, when I call function TTHAO_MONTT_EXISTS, do I must declare all two param "ret" and "montt_id" And how I declare it in .net Please help me! database application gives error: Microsoft JScript runtime error: Invalid calling object.   (390 Views)
I am using Microsoft Visual Web Developer 2010. I created a sample application using SQL server database. When I run I get an error:Microsoft JScript runtime error: Invalid calling object. and buttons: Break, Ignore, Continue. Does anyone know how to fix this, to remove this error.
How to call Excel to run at background and notify user afterwards ?   (256 Views)
Given: an Excel file with a Macro called macro1 Required: aspx page that on buttonclick call Excel file and run Macro1 AT BACKGROUND. This means user has another XLS file of his own opened and this button click calls and runs this Excel macro without notice to user even he is working on Excel. This page shows "running... pls wait..." and then when the Macro completes running, show a message "Updated" How to do this Pls help.
The SelectCommand property has not been initialized before calling 'Fill'.   (258 Views)
HI, I getting some error on mycode.could u please let me know where im doing is my code. and one more thing im searching record from my database throught this way on my textbox there is another better to get the record on my textbox. If conn.State = 1 Then conn.Close() Try cmd.Connection = conn conn.Open() Dim ds As New DataSet cmd.CommandText = "SELECT * FROM user1 where (civilid = N'" & TextBox1.Text & "')" da.Fill(ds, "data") conn.Close() For Each dt As DataTable In ds.Tables For Each dr As DataRow In dt.Rows If TextBox1.Text = dr.Item("civilid") Then txtStatus.Text = "You already have " + TextBox1.Text Exit Sub End If Next dr Next dt Catch ex As Exception txtStatus.Text = (ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error") End Try
calling a oracle function in a package from C#   (288 Views)
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 assigningthe 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.[:)]
Adding Reference of a DLL Dynamically   (138 Views)
Hi EveryOne, I am facing a problem i have a code where i have to call either a mySql procedure or a Oracle procudere depending on a flag value. Now to call a Oracle procedure i have added reference of Oracle.DataAccess (Becouse i and using Associative array as parrameter in Oracle package so have to use OracleParameters.) Now a system(Working with mySql) usnig my dll need to have Oracle.DataAccess reference in his project and His project throws a error. So what can i do so here Do i need to add reference of Oracle.DataAccess dynamically
Finding trouble trying to call a stored procedure that works when called from query but not from c#   (229 Views)
I get the following error when trying to run a signin login type asp page with a c# backend handling the process ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'findlogin' at line 1 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: System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'findlogin' at line 1 Source Error: Anunhandledexceptionwasgeneratedduringtheexecutionofthecurrentwebrequest.Informationregardingtheoriginandlocationoftheexceptioncanbeidentifiedusingtheexceptionstacktracebelow. my code is as follows public void Login_Click(Object Src, EventArgs e) { string log, password; OdbcCommand cmd = new OdbcCommand("findlogin", new OdbcConnection(GetConnectionString())); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); using (OdbcDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) {
How to call DB2 Stored Procedure   (287 Views)
How to call DB2 Stored Procedure from ASP.NET I am using following code which does not work. DB2 SP has two in parameters as: CREATE PROCEDURE DXTDB.TestIN ip_constant_ctlno_num VARCHAR(1000), IN ip_prem_clm_sys CHAR(3) ) --------------------------------- stringselectQuery="{CALLDXTDB."+sp+"(),()}"; Listlist=newList(); OdbcCommandcommand=newOdbcCommand(selectQuery,_connection); command.CommandType=CommandType.StoredProcedure; command.Parameters.Add("ip_constant_ctlno_num",OdbcType.VarChar,1000).Value=controlNumbers; command.Parameters.Add("ip_prem_clm_sys",OdbcType.Char,3).Value =clmSys; using(_connection) { _connection.Open(); OdbcDataReaderreader=command.ExecuteReader(CommandBehavior.CloseConnection); GIProductFamilyFactoryfactory=newGIProductFamilyFactory(); using(reader) { list.Add("ALL"); while(reader.Read()) list.Add(factory.Create(reader)); } } returnlist; -------------------------- ERROR [07001] [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001
Dynamically upload a TNS entry to TNSNames.ora file using VS2005   (273 Views)
As a Requirement we need to Upload the TNS entry given by the User ( After validating) to the Tnsnames.ora file in it possible to upload using .Net if so Please guide me..
ORA-01008 when adding new row to DataTable an call Adapter.Update(DataTable)   (368 Views)
hello, i always get the error "ORA-01008: Nicht allen Variablen ist ein Wert zugeordnet" when inserting a new record with the following code: Dim employees as EmployeesDataTable = Adapter.GetData Dim emp as EmployeesRow = employees.NewEmployeesRow emp.Ename = "Hugo" emp.DetpId = 5 employees.AddEmployeesRow(emp) Adapter.Update(employees) but when i use the DBDirectMethod Insert instead (see following code) it works fine: Adapter.Insert("Hugo",5) why does the ORA-01008 error appear, when not using the DBDirectMethod regards, matthias
how to call a session variable in select statement   (259 Views)
I'm holding the userid in a session variable when a user logs in. my tables are in an Oracle table. I have a product1.aspx and product.aspx.vb page. I'm able to call and use the session varibale inside the product.aspx.vb page. In the product.aspx page, I have a select statement which needs to use this session variable. I have tried most of the permutations and combinations without any luck. As it stand now, the code is I have also tried the following code: user777 is the session variable that I set in my Login page. Would be really greatful to anyone who can point me in the right direction. Thank You.
Enter Date into a database automatically   (183 Views)
Hi , I would like to know how to insert a date into a database each time that a file is uploaded so that the time and date of upload is recorded. This is what I have so far: Dim conn As New OleDbConnection( Provider=MySQLProv; Data Source=mySource;User Id=myID;Password=myPassword;) Dim MySQL As String = "INSERT into QDocumentsTBL (docTitle, docType, docCOntent,docDate) values (FileTitle, FileType, File)" cmdInsert.Parameters.Add("@FileTitle", txtFileTitle.Text) cmdInsert.Parameters.Add("@FileType", StrFileType) cmdInsert.Parameters.Add("@FileContent", arrFile) cmdInsert.ExecuteNonQuery() myConnection.Close() cmdInsert.ExecuteNonQuery() myConnection.Close() The docDate column was created like this docDate timestamp(14) in MySQL. Also the the semicolon after MySQLProv cause an error which says that it is not a valid character, although that is how it was shown in I would really appreciate it if someone can help me out. Thank you in advance for your time.
Oracle triggers calling .exe   (336 Views)
Is it possible to have a trigger call a .NET executable Each time a new record is inserted or updated, I want to compare the newly inserted/updated record with another record in another database and do further processing. My idea was to have the trigger call a .NET app which will do the comparing and further processing. Has anyone encounter a issue like the one described above
cannot acces oracle DB when no client installed locally   (399 Views)
i have application which acces an Oracle DB throught ODP .net, my project includes the Oracle.DataAccess.dll, my test server has a oracle 10g client on it so i can access the DB fine, although the DB is on another server, wheni move my application to my live server and open my application (logon.aspx connects the DB ) i get this error :Unable to load DLL 'OraOps10.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) do i need to have an Oracle Client installation on my production server, can I not just access using ODP without having to have a client locally
How to call DB2 storedprocdeure from code with INOUT parameters   (263 Views)
Pls help... I have to call a DB2 stored procedure from ASP.NET code by using 18 INOUT parameters. THis is aprocedure which gives me price for a particular product. Here is my code. Dim objconn As New OdbcConnection("DSN=ABCD;uid=xyz;password=123;") Dim objcmd As OdbcCommand Dim query As String Dim dblprice As Double Dim objParam As OdbcParameter Try objcmd = New OdbcCommand("CALL PKLIB.PKWEBPRC(,,,,,,,,,,,,,,,,,)", objconn) objcmd.CommandType = CommandType.StoredProcedure objcmd.Parameters.Add(New OdbcParameter("@PDIV", OdbcType.VarChar)).Value = "090" objcmd.Parameters.Add(New OdbcParameter("@PCOL", OdbcType.VarChar)).Value = "352" objcmd.Parameters.Add(New OdbcParameter("@PPATT", OdbcType.VarChar)).Value = "P000003" objcmd.Parameters.Add(New OdbcParameter("@PCOLR", OdbcType.VarChar)).Value = "N024" objcmd.Parameters.Add(New OdbcParameter("@PORDDT", OdbcType.Decimal)).Value = 80610 objcmd.Parameters.Add(New OdbcParameter("@PCUST#", OdbcType.VarChar)).Value = "00000000" objcmd.Parameters.Add(New OdbcParameter("@PCTYPE", OdbcType.VarChar)).Value = "090" objcmd.Parameters.Add(New OdbcParameter("@PQORD", OdbcType.Decimal)).Value = 1.0 objcmd.Parameters.Add(New OdbcParameter("@PRHSIN", OdbcType.VarChar)).Value = "F" objcmd.Parameters.Add(New OdbcParameter("@PQUAL", OdbcType.VarChar)).Value = "1" objcmd.Parameters.Add(New OdbcParameter("@LSTRCD", OdbcType.VarChar)).Value = "" objcmd.Parameters.Add(New OdbcParameter("@PPRCTP", OdbcType.VarChar)).Value = "" objcmd.Parameters.Add(New OdbcParameter("@PPRICE", OdbcType.Decimal)).Value = 0.0 objcmd.Parameters.Add(New OdbcParameter("@PPRLOD", OdbcType.Decimal)).Value = 0.0 objcmd.Parameters.Add(New OdbcParameter("@PRCSEQ", OdbcType.Decimal)).Value = 0 objcmd.Parameters.Add(New OdbcParameter("@EDTUPD", OdbcType.VarChar)).Value = "0" objcmd.Parameters.Add(New OdbcParameter("@PRCOM$", OdbcType.Decimal)).Value = 0.0 objcmd.Parameters.Add(New OdbcParameter("@PCURCY", OdbcType.VarChar)).Value = "USD" objconn.Open() objcmd.ExecuteNonQuery() dblprice = Convert.ToDouble(objcmd.Parameters.Item("@PPRICE").Value) Catch ex As Exception Response.Write(ex.Message) Finally objconn.Close() End Try Thispiece of code runs without any error butalways gives me price as 0. The variable @PPRICE will get the price of the product. Pls let me know whts wrong with my code.
Error statement handle not executed when calling a storeproc in c#   (348 Views)
I need help on this. I am calling a store proc which has a refcursor. I am able to execute it manually. When i try to run it c# in 2.0 its throwing the error 'ORA-24338 Statement handle not exectued' tried with datareader as well as dataadator. canyou me on this. here is the code . // OracleCommand cmd = new OracleCommand(); // cmd.Connection = Conn; // cmd.CommandType = CommandType.StoredProcedure; // cmd.CommandText = "ASAP.PKG_GET.GET_CAVALUE"; //cmd.Parameters.Add("aDocNum", OracleType.Number).Value = intvalue; //cmd.Parameters.Add("oReturnVal", OracleType.Number).Direction = ParameterDirection.Output; //cmd.Parameters.Add("oReturnCd", OracleType.VarChar,400).Direction = ParameterDirection.Output; //cmd.Parameters.Add("oResultSet", OracleType.Cursor).Direction = ParameterDirection.Output; // dr= cmd.ExecuteReader(); Thank you so much in advance
Connection string works locally but not on server   (157 Views)
I set upan Oracle connection that works locally, but catches an error and does not work when published to the server. I specified the driver in the connection string and confirmed that it is also installed on the server. Any ideas
Oracle function call from C#, return DataSet? Pipelined   (431 Views)
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: DataTabledt=newDataTable(); OracleConnectionConn=newOracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); OracleCommandDbComm=newOracleCommand("SCHEMA.MYFUNCTION",Conn); DbComm.CommandType=CommandType.StoredProcedure; OracleParameterinVar=newOracleParameter("VAR",OracleType.VarChar); inVar.Direction=ParameterDirection.Input; 9 inVar.Value="999"; DbComm.Parameters.Add(inVar); //I'vetriedthisblockofcode: Conn.Open(); OracleDataAdapteradapter=newOracleDataAdapter(DbComm); adapter.Fill(dt); Conn.Close(); //Andalsotriedthis: Conn.Open(); OracleDataReaderreader=DbComm.ExecuteReader(); dt.Load(reader); reader.Close(); 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.....