Sorry to rehash an old issue. I've looked at several old posting about this topic and have not had any success in implementing a stored procedure (function) call that returns a cursor. I have successfully gotten a stored Proc to return a cursor, but I want
to try to get a function to return one also. I am using the Microsoft System.Data.OracleClient package for the data access and I am running Oracle 8i as the database. My issue is that when i set up the call, I pass no parameters and only recieve the curosr
in the oracle function. I add the return cursor to the command object but it does not allow me to assign it the direction of "OracleType.ReturnValue", because when I do i get the error
"parameter 'refcursor': OracleType.Cursor parameters may only be ParameterDirection.Output parameters." If I change it to an output parameter like the error warns i recieve this error:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'POL_FN_READALL_PROCEDURES' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
below is a snippet of the problematic code. Any help would be appreciated. PL/SQL ---------------------------
FUNCTION POL_FN_READALL_PROCEDURES RETURN Types.cursor_type IS
p_cur Types.cursor_type;
BEGIN
open p_cur for
select PROCEDUREID,
DRAFTID,
PROCEDURETEXT
from PROCEDURES;
return (p_cur);
END;
C#
----------------------------
oCmd = new OracleCommand ("POL_FN_READALL_PROCEDURES",oCon);
//Stored Procedure (Function )
oCmd.CommandType=CommandType.StoredProcedure;
//Create Parameter Object
oCmd.Parameters.Add(new OracleParameter("refcursor",OracleType.Cursor)).Direction = ParameterDirection.Output;
//Instatiate Dataset
DataSet Ds2=new DataSet();
//Instantiate Data Adapter
OracleDataAdapter oAdp2=new OracleDataAdapter(oCmd);
//Fill Data Set
oAdp2.Fill(Ds2);
//Bind Data to Grids
dgTwo.DataSource = Ds2.Tables[0];
dgTwo.DataBind();
-------------------------------------------- thanks mike
I am having the exact same problem but with calling a stored proc, not a function. Mike, can u help me. My code is the same as yours, but I m getting the error :
ORA-06550 wrong number or types of arguments in call to procedure_name
my stored proc code is
CREATE OR REPLACE PROCEDURE sp_get_GlobalParams
(
p_cur OUT Types.cursor_type
)
AS
BEGIN
open p_cur for
SELECT * FROM T_GLOBAL_PARAMS;
END;
p_cur is of type REF CURSOR. VB.NET code is:
Dim strConn As String = ConfigurationSettings.AppSettings.Get("OracleConnectionString")
Dim objConn As New OracleClient.OracleConnection(strConn)
'Creating the OleDB Connection
objConn.Open()
'Creating the Command Type
Dim objCmd As New OracleClient.OracleCommand()
objCmd.CommandText = "sp_get_GlobalParams"
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Connection = objConn
objCmd.Parameters.Add(New OracleClient.OracleParameter("Refcur", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.ReturnValue
'Creating the Data Adapter
Dim objDA As New OracleClient.OracleDataAdapter()
objDA.SelectCommand = objCmd
'Creating the DataSet
Dim objDS As New System.Data.DataSet()
'Filling the DataSet using the Data Adapter
objDA.Fill(objDS, "AllTables")
What am I doing wrong? Thanks. Help will be much appreciated.
For starters, it appears that you are mixing db connection types. You start using OleDb but then try to reference the OracleClient. you need to choose one or the other. My code is using the System.Data.OracleClient. I've used OleDb before, but I have never
had success at it, especially with paramaterized queries. It also does not function well with Clob and Blob data types (atleast in my experience) Secondly, the paramater direction on a Ref Cursor needs to be output because that is the only supported direction
for a ref cursor. This may be the reason why I'm having my problem. I'd recommend tackling these two issues first and then see if it works. If it doenst, you should evaluate from there. Mike
Thanks for the Mike reply. I was trying earlier with OleDb, but then I switched to OracleClient. In the code, i posted, only the comments say OleDb, but i am actually using Oracle Client. Also, i have changed the parameter direction to output instead of ReturnType.
I am still getting the same error. Any other ideas? Thanks !
make sure the paramater has the same name as the paramater in the stored proc. You are using p_cur in the stored proc so change the vb.net code from "Refcur" to "p_cur". Mike
cpuDemon
Member
60 Points
12 Posts
Returning Cursors with an Oracle Function
Jan 30, 2004 02:28 PM|LINK
FUNCTION POL_FN_READALL_PROCEDURES RETURN Types.cursor_type IS p_cur Types.cursor_type; BEGIN open p_cur for select PROCEDUREID, DRAFTID, PROCEDURETEXT from PROCEDURES; return (p_cur); END;C# ----------------------------oCmd = new OracleCommand ("POL_FN_READALL_PROCEDURES",oCon); //Stored Procedure (Function ) oCmd.CommandType=CommandType.StoredProcedure; //Create Parameter Object oCmd.Parameters.Add(new OracleParameter("refcursor",OracleType.Cursor)).Direction = ParameterDirection.Output; //Instatiate Dataset DataSet Ds2=new DataSet(); //Instantiate Data Adapter OracleDataAdapter oAdp2=new OracleDataAdapter(oCmd); //Fill Data Set oAdp2.Fill(Ds2); //Bind Data to Grids dgTwo.DataSource = Ds2.Tables[0]; dgTwo.DataBind();-------------------------------------------- thanks mikeDjDev
Participant
855 Points
171 Posts
Re: Returning Cursors with an Oracle Function
Feb 02, 2004 07:59 PM|LINK
CREATE OR REPLACE PROCEDURE sp_get_GlobalParams ( p_cur OUT Types.cursor_type ) AS BEGIN open p_cur for SELECT * FROM T_GLOBAL_PARAMS; END;p_cur is of type REF CURSOR. VB.NET code is:Dim strConn As String = ConfigurationSettings.AppSettings.Get("OracleConnectionString") Dim objConn As New OracleClient.OracleConnection(strConn) 'Creating the OleDB Connection objConn.Open() 'Creating the Command Type Dim objCmd As New OracleClient.OracleCommand() objCmd.CommandText = "sp_get_GlobalParams" objCmd.CommandType = CommandType.StoredProcedure objCmd.Connection = objConn objCmd.Parameters.Add(New OracleClient.OracleParameter("Refcur", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.ReturnValue 'Creating the Data Adapter Dim objDA As New OracleClient.OracleDataAdapter() objDA.SelectCommand = objCmd 'Creating the DataSet Dim objDS As New System.Data.DataSet() 'Filling the DataSet using the Data Adapter objDA.Fill(objDS, "AllTables")What am I doing wrong? Thanks. Help will be much appreciated.Dj Dev
cpuDemon
Member
60 Points
12 Posts
Re: Returning Cursors with an Oracle Function
Feb 02, 2004 08:37 PM|LINK
DjDev
Participant
855 Points
171 Posts
Re: Returning Cursors with an Oracle Function
Feb 02, 2004 09:05 PM|LINK
Dj Dev
cpuDemon
Member
60 Points
12 Posts
Re: Returning Cursors with an Oracle Function
Feb 03, 2004 01:13 PM|LINK
DjDev
Participant
855 Points
171 Posts
Re: Returning Cursors with an Oracle Function
Feb 03, 2004 03:18 PM|LINK
Dj Dev
cpuDemon
Member
60 Points
12 Posts
Re: Returning Cursors with an Oracle Function
Feb 03, 2004 03:42 PM|LINK