How to get the return value of an Oracle function?

[mdonath2]

In a package 'UTILS' I have the function:

FUNCTION TestF(p_Name IN VARCHAR2) RETURN INTEGER;

In C++ Builder I call the function by:

OraQuery1->SQL->Add(L"DECLARE");
OraQuery1->SQL->Add(L" v_Res INTEGER;");
OraQuery1->SQL->Add(L"BEGIN");
OraQuery1->SQL->Add(L"v_Res := UTILS.TestF(:p_Name);");
OraQuery1->SQL->Add(L"END;");
OraQuery1->ParamByName(L"p_Name")->AsString = L"TestName";
OraQuery1->Execute();

All is OK. The function is executed correctly. But how to get the return parameter of the function? My test was:

OraQuery1->SQL->Add(L"BEGIN");
OraQuery1->SQL->Add(L":p_Res := UTILS.TestF(:p_Name);");
OraQuery1->SQL->Add(L"END;");
OraQuery1->ParamByName(L"p_Name")->AsString = L"TestName";
OraQuery1->Execute();
int res = OraQuery1->ParamByName(L"p_Res")->AsInteger;

But this fails with error EOraError::ORA-06502 ( numeric or value error: character string buffer too small)

I think I have to declare the return value before. But how to do?

Markus

[jfudickar]

Without sitting at my compiler, I would say that you have to declare the parameter in this case before calling execute.
Have a look for declareparam or so.
Regards
Jens

[mdonath2]

That's what I think. I have to declare the parameter first. But I can't find any function which looks like a 'Declare Param'-function.

Markus

[AlexP]

Hello,

To solve the problem, you should specify the OUT parameter type:

OraQuery1->SQL->Add(L"BEGIN");
OraQuery1->SQL->Add(L":p_Res := UTILS.TestF(:p_Name);");
OraQuery1->SQL->Add(L"END;");
OraQuery1->ParamByName(L"p_Name")->AsString = L"TestName";
OraQuery1->ParamByName(L"p_Res")->DataType = ftInteger;
OraQuery1->ParamByName(L"p_Res")->ParamType = ptOutput;
OraQuery1->Execute();
int res = OraQuery1->ParamByName(L"p_Res")->AsInteger;

[mdonath]

Thank you. That is what I was looking for.

Markus