Calling Oracle Functions

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,
I am using the .NET Framework data provider for Oracle
and can't get anything to work when using Oracle
Functions. Couple of quesitons: 1) Do I have to declare
the output as REF CURSOR? If I don't then the error says
there is no store proc with this name? If I do, then I
always get the same error about "invalid argument or type
for input params..".

ANY HELP??


FUNCTION customer_details(dealer_id_in config.ids)
RETURN config.GenRfcTyp
IS
v_rfc config.GenRfcTyp;
BEGIN
OPEN v_rfc FOR
SELECT DISTINCT customer_id uc_id,
customer.name(customer_id)
customer_name
FROM dealer_customers
WHERE dealer_id = dealer_id_in
ORDER BY customer_name;
RETURN v_rfc;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN v_rfc;
END customer_details;
 
Steve said:
Hi,
I am using the .NET Framework data provider for Oracle
and can't get anything to work when using Oracle
Functions. Couple of quesitons: 1) Do I have to declare
the output as REF CURSOR? If I don't then the error says
there is no store proc with this name? If I do, then I
always get the same error about "invalid argument or type
for input params..".

ANY HELP??
Click to expand...

try using commandType.Text and CommandText of

"begin :rc := customer_details(:dealer_id); end;"

and bind an output ref cursor and an input parameter of whatever.

The basic idea for troubleshooting this stuff is to push it down in to
PL\SQL and then add back bind variables slowly.


You might even start with

declare
v_rfc config.GenRfcTyp;
begin v_rfc := customer_details(4);
end;

or

declare
type GenRfcTyp is REF CURSOR;
v_rfc GenRfcTyp;
begin
v_rfc := customer_details(5);
end;



David
 
Back
Top