how to execute a function that returns a boolean

[ace333]

i've been calling my functions in c# using the
select function_Name from dual , but i'm in a spot of bother now as i cant do this with the boolean function.
The reason i did this is because i could not get the code that is used to execute a stored procedure to work with the functions. The code for that includes an execute section that is included for the procedure part.

My issue: Is there a simple way to call a boolean oracle function from c# that takes three parameters and returns either true or false...

 

[ace333]

Can someone please respond. Its doing my head in.

 

[checkai]

your post is a little confusing...your trying to call an oracle function? I'm more familiar with vb and sql, but it sounds like you could simply create a stored procedure or maybe even a Text based command, and call that function from it....

basically it would return 1 row with 1 column that hold true/false...

DLC

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[ace333]

i can call stored procedures from c# but can't seem to get the code to work when it comes to calling functions written in pl\sql,
I was wondering if you can call both using the same code

 

[cappmgr]

This is calling a function. It uses System.Data.OracleClient.

public static string TestFunction()
{ 
	string myConnString = "User ID=gw;Password=clown;Data Source=texas;"; 
	OracleConnection myConnection = new OracleConnection(myConnString);
	OracleCommand myCommand = new OracleCommand("myrand",myConnection);
	myCommand.CommandType = CommandType.StoredProcedure;

	OracleParameter myParam = new OracleParameter();
	myParam.ParameterName = "n";
	myParam.OracleType = OracleType.Number;
	myParam.Value = 9;
	myParam.Direction = ParameterDirection.Input;
	myCommand.Parameters.Add(myParam);

	OracleParameter myRandomNumber = new OracleParameter();
	myRandomNumber.ParameterName = "my_random_number";
	myRandomNumber.OracleType = OracleType.Number;
	myRandomNumber.Direction = ParameterDirection.ReturnValue;
	myCommand.Parameters.Add(myRandomNumber);
	try 
	{
		myConnection.Open();
		myCommand.ExecuteScalar();
		if (myRandomNumber.Value != null)
		{
			return Convert.ToString(myRandomNumber.Value);
		}
		else
		{
			return "my rand is null";
		}
	}	
	catch (System.Data.OracleClient.OracleException OraEx) 
	{
		throw new Exception("OraEx " + OraEx.Message);
	}
	catch (System.Exception SysEx) 
	{
		throw new Exception("SysEX " + SysEx.Message);
	}
	finally 
	{
		myConnection.Close(); 
	}
}

Notice the parameter direction of the random number. Also
myRandomNumber.ParameterName = "my_random_number";
my_random_number is the name of the value in the sproc that is set and returned.
Marty

 

[ace333]

Ok, i'm gona look at this and see if i can work into my code, thanks for the code sample

 

[cappmgr]

That should have been Function and not sproc.
Marty

 

[ace333]

in the code where is it calling the function, what i mean is where is the name of the function in the code

 

[ace333]

so 'my_random_number' is the name of the function

 

[cappmgr]

OracleCommand myCommand = new OracleCommand("myrand",myConnection);

myrand is the name of the function.

Marty

 

[ace333]

Got this error

ORA-06550: line 1, column 28: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored False

your code works perfectly if i call a function that returns a string or int etc. but wont work if the function returns a true or false value. Its the exact same problem i was having when i used to call the functions this way
select my_functName from dual. I thought it was the way i called the function that was the problem but it seems ur way does not work either...
oh well


using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;


namespace Project1
{
/// <summary>
/// Summary description for WebForm5.
/// </summary>
public class WebForm5 : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
Response.Write(TestFunction());
}

public bool TestFunction()
{
string myConnString = "User ID=xxxxx;Password=xxxxx;Data Source=xxxxx;";
OracleConnection myConnection = new OracleConnection(myConnString);
OracleCommand myCommand = new OracleCommand("countRoundBool",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

OracleParameter myParam = new OracleParameter();
myParam.ParameterName = "aID";
myParam.OracleType = OracleType.Number;
myParam.Value = 12;
myParam.Direction = ParameterDirection.Input;
myCommand.Parameters.Add(myParam);

OracleParameter myRandomNumber = new OracleParameter();
myRandomNumber.ParameterName = "my_random_number";
myRandomNumber.OracleType = OracleType.Number;
myRandomNumber.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myRandomNumber);
try
{
myConnection.Open();
myCommand.ExecuteScalar();
if (myRandomNumber.Value != null)
{
return bool.Parse(myRandomNumber.Value.ToString());

}
else
{
return false;
}
}
catch (System.Data.OracleClient.OracleException OraEx)
{
throw new Exception("OraEx " + OraEx.Message);

}
catch (System.Exception SysEx)
{
throw new Exception("SysEX " + SysEx.Message);

}
finally
{
myConnection.Close();

}

}


#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

 

[cappmgr]

Just trying to answer this.

i can call stored procedures from c# but can't seem to get the code to work when it comes to calling functions written in pl\sql,
I was wondering if you can call both using the same code

I don't believe you'll get what you want. boolean is part of pl/sql but it is not a valid datatype for a table.
Marty