How to call Oracle Function from Data Access App Block

Topics: Data Access Application Block
May 4, 2007 at 5:12 AM
I have the following oracle function:

FUNCTION GetLoanStatus(LOAN_ID IN VARCHAR2) RETURN VARCHAR2 AS
status VARCHAR2;
BEGIN
SELECT CURRSTATUS INTO status FROM LOAN WHERE AP# = LOANID;
RETURN status;
END;

I have the following c# code:
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand("Test.GetLoanStatus"))
{
db.AddInParameter(cmd, "LOAN_ID", DbType.String, loanID);
object obj = db.ExecuteScalar(cmd);
status = (obj == null) ? null : obj.ToString();
}

I'm getting the following exception when I try to run:System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00221: 'GETLOANSTATUS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can anybody please help me to figure out how to call an oracle function from c# using EntLib 3.0 Data Access Application Block?

Thanks,
Hari.
May 4, 2007 at 3:22 PM
You could just execute the following SQL:

select FUNCTION_NAME from dual;

Or execute a stored procedure in which you utilize your function.
May 4, 2007 at 5:53 PM
Thanks it works. To add to this: if you need to pass a parameter you can call something like this:

SELECT <packagename>.<functionname>(<parameter>) FROM DUAL
For eg. SELECT TEST.GetData('1101') FROM DUAL;

Thanks,
Hari.

Greif wrote:
You could just execute the following SQL:

select FUNCTION_NAME from dual;

Or execute a stored procedure in which you utilize your function.