Error with TOraStoredProc and function

lasinsa · Post by **lasinsa** » Mon 16 Oct 2006 08:22

Hi,

I am using ODAC 5.80.0.35 with Delphi 2006 and Oracle 9i.

Example code:

Proc := TOraStoredProc.Create(Self);
Proc.Session := OraSession;
Proc.StoredProcName := 'Test_Procedure';
Proc.Params.CreateParam(ftInteger, 'iUserKey', ptInput).Value := 1;
Proc.ExecProc;

The above code works with an Oracle procedure, but returns the following error when trying to call a function: ORA-6550... 'Test_Function’ is not a procedure or is undefined. Any ideas?

Thanks.
Lee.

Challenger · Post by **Challenger** » Mon 16 Oct 2006 12:46

Please try to execute this procedure using SQL *Plus.

lasinsa · Post by **lasinsa** » Mon 16 Oct 2006 12:56

I can execute the function in Oracle.

If I place a TOraStoredProc component on my form, and set the
StoredProcName that way, I can execute the function using OraStoredProc.ExecProc.

lasinsa · Post by **lasinsa** » Tue 17 Oct 2006 06:27

But I would really prefer to create the TOraStoredProc in the code - is there a setting I'm overlooking?

Challenger · Post by **Challenger** » Tue 17 Oct 2006 12:13

Please send to ODAC support address complete sample that demonstrates this problem and include script to create server objects.

Phobos · Post by **Phobos** » Tue 17 Oct 2006 12:39

lasinsa wrote:I can execute the function in Oracle.

If I place a TOraStoredProc component on my form, and set the
StoredProcName that way, I can execute the function using OraStoredProc.ExecProc.

Maybe you are missing grants?
If you execute proc in SQLplus then it might work,
but when you execute the proc in your application as an user then it might not work (if it is not granted, Role is not set, etc.).

Also the next error you may get is be with Parameters. Make sure you have set the ParamCheck property to FALSE.

All IMHO.

lasinsa · Post by **lasinsa** » Wed 18 Oct 2006 06:53

Problem solved - my bad - I was not setting the "Result" parameter to type ptResult.

Thanks for the assistance.