I can't get Delphi4 to call an Oracle SELECT stored proc (one that uses
ref cursor into result set). I used to have the proc within a
package, but referring to that stored proc in Delphi by
SERGEY.P_TEST.SP_TEST produced an Oracle error that said something like
"... identifier 'P_TEST.SP_TEST' must be declared ..."

Then I wrote a stand alone stored proc that just used the type
definition in the package header. Like this:

create package p_test as
  type refc_type is ref cursor;
end;

create procedure sp_test (cur_var IN OUT p_test.refc_type) as
begin
  open cur_var for select * from t_test;
end;

t_test table has one column I NUMBER and three rows with values 1, 2,
and 3.

I tested it in SQL*Plus (I used VAR X REFCURSOR, and passed :X into
sp_test as a parameter), it all works fine (I can PRINT X and see all
the rows from the table).

In Delphi, however, I get an Oracle error "wrong number or types of
arguments in call to sp_test". My Params property properly shows my
stored proc parameter (cur_var), but Oracle just doesn't seem to like
Delphi's type specifier (it defaults to tVarBytes). I tried every type
selection there is in Delphi for that parameter and received either the
same Oracle error, or "capability not implemented" from Delphi, or
"operation not applicable" from Delphi.

BTW, I am using TStoredProc for this.

It sounds like other people have been able to implement Oracle SELECT
procs and call them from Delphi. What am I doing wrong?

Thank you in advance!

Sergey

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.