Problem calling an oracle function from asp.net

[ace333]

Below is code that I thought would run a function from asp.net passing it one argument, an id and return one number an integer
as well. I am able to call stored procedures from asp.net with code similar to below but those procedures added
a row of data to the table. I'm wondering is the procedure slightly different when you try to call a
function instead of a stored procedure. I would have thought not since people seem to assume that stored procedures
cover both stored procedures and functions. This is the error that I get when I try to call the function

Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 7: PLS-00221: 'COUNTROW' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored at Project1.xmlApp.GetInt(String[] aParams) in c:\inetpub\

http://wwwroot\project1\xmlapp.aspx.cs:line

273 at Project1.xmlApp.btnRead_Click(Object sender, EventArgs e) in c:\inetpub\

http://wwwroot\project1\xmlapp.aspx.cs:line

171


Any help is greatly appreciated.




namespace Project1
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class xmlApp: System.Web.UI.Page
{


private void Page_Load(object sender, System.EventArgs e)
{

/**if(inFileLocation.Value == "")
{
btnRead.Enabled = false;
lblfileLoc.Visible = true;
}
if(inFileLocation.Value != "")
{
btnRead.Enabled = true;
lblfileLoc.Visible = false;
}
**/
lblErrors.Visible = false;
lblErrors2.Visible = false;
lblErrors3.Visible = false;
lblErrors4.Visible = false;
lblErrors5.Visible = false;

}


GetInt(arguments);





//****************************************************************


}//End of try block

catch(XmlException eff)
{


lblErrors4.Visible = true;
lblErrors4.Text ="File not validated"+"</br>" + eff.ToString();
}

catch (Exception eg)
{
lblErrors5.Visible = true;
lblErrors5.Text ="Please enter a file path"+"</br>"+ eg.ToString();
}




}//End of read method

/// <summary>
/// Method that is called to execute a stored procedure against the database and return a DataSet.
/// </summary>
/// <param name="aParams">A set or parameters to populate the command object.</param>
/// <returns>A dataset containing the results of the stored procedure.</returns>
///


public int GetInt(string[] aParams)
{
string sConn = GetConnString();

OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd;

int iResult;

try
{

PopulateCommand(aParams,out objCmd);

objCmd.Connection=objConn;
objConn.Open();

iResult = Convert.ToInt32(objCmd.ExecuteScalar().ToString());

}
catch (Exception ex)
{
throw ex;
}
finally
{
objConn.Close();
}

return iResult;

}




//***********************************************************************************************
private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
{
objCmd = new OracleCommand();

objCmd.CommandType=CommandType.StoredProcedure;


OracleParameter param;

for (int i=0;i< aParams.Length;i++)
{
String par = aParams;
if (i==0)
{
objCmd.CommandText= aParams[0];
Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+aParams[0]);


}//End if.

else
{
String args = aParams;

param = new OracleParameter(args,OracleDbType.Varchar2);

param.Direction = ParameterDirection.Input;

param.Value = args;



Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+param);

objCmd.Parameters.Add(param);

}//End else.

}//End for loop.

}//End PopulateCommand method.


//*************************************************************************************************



}

 

[vbkris]

i am writing this entire thing with SQL server in mind. please change it to oracle commands at appropriate places.

>>CommandType.StoredProcedure
all this does is (if i am correct) add an "exec" command in front of a SP name.

a stored procuedure in SQL server is executed like so:

exec SPNAME

but a function is likeso:

Select DATABASE.Owner.FunctionName(Args)

so when u set the type to storedproc it will add the "exec" command which WILL not work for functions...

Known is handfull, Unknown is worldfull

 

[ace333]

Ya that could be a problem, so any idea of how to fix that.

 

[vbkris]

dont use type as StoredProcedure!!!

just pass a normal sql like so:
sql="select FunctionName";

and use ExecuteScalar() to get the values...


Known is handfull, Unknown is worldfull

 

[ace333]

But the problem is that i want a user to have the option of either running a stored procedure or executing a function... so if i make the change to the code as you suggest the stored procs wont work

 

[vbkris]

then add an extra arg to ur function like so:

private void PopulateCommand(string[] aParams,string Type="S",out OracleCommand objCmd)
{
objCmd = new OracleCommand();


if(Type=="S")
objCmd.CommandType=CommandType.StoredProcedure;


>>Type="S"

i dont know much c#, what i am trying to do is make Type as S by default...

Known is handfull, Unknown is worldfull

 

[ace333]

Can you give an example of how to execute a function, how do i add the command to call the function to my code.

objCmd = new OracleCommand();

objCmd.CommandType=CommandType.Text;

objCmd = "select functionname(params) from dual"

Can I assign it like this

 

[vbkris]

Dont set a command type at all. it is the default way to execute the command(ie select * from...)

Known is handfull, Unknown is worldfull

 

[ace333]

So this is what i need

objCmd = new OracleCommand();

//objCmd.CommandType=CommandType.StoredProcedure;

objCmd = "select * from test";

 

[vbkris]

yup, for a normal sql thats all u need...

Known is handfull, Unknown is worldfull

 

[vbkris]

well, did it work???

Known is handfull, Unknown is worldfull

 

[ace333]

No still working on it

 

[vbkris]

k get back to me when its over...

Known is handfull, Unknown is worldfull

 

[ca8msm]

If I have understood you correctly, you have some stored procedures that have select statements in them and you want the user to be able to run them and view the results. If what I have just said is correct then you were correct in the first place to set the command type to CommandType.StoredProcedure. What you should have then done is maybe used a dataadaptor and use the Fill command to fill a dataset with the results of that SP.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.