Devart Forums

Posted: **Mon 26 Nov 2007 12:03**

Hi
Am new to Delphi - sorry if a basic question.
We need help called an Oracle PLSQL function (stored in a package).
We have used component TOraStoredProc and successulfully execute the package by filling in the PARAMS tab of the TOraStoredProcedure component.
Same component has generated call to PLSQL function for us (see PART A following).

Prob 1) When I make the TOraStoredProc component *active*, I get an error "Sql Stattement does not return any rows". There is no SQL merely a generated call to a FUNCTION with returns a value of VARCHAR2. How do I make Active = True?

Oracle Package Wizard generates code as in PART B which I call with Code I have written in Part C. Does not work. NOt a Delphi expert as mentioned but would love some help here too.

PS. Am in Norway. Have tried to find people / help who know Delphi 2007 and ODAC. No success.

Thanks

========
PART A

begin
:RESULT := gen02.SCANNING.LOAD(:BFIL, :P_LOBJ_CODE, :P_LOGIN_ID, :P_LOCKED, :P_REGNR);
end;

=======
PART B

function TSCANNING.LOAD
(
const ABFIL: string;
const AP_LOBJ_CODE: string;
const AP_LOGIN_ID: string;
const AP_LOCKED: string;
const AP_REGNR: string): string;
var
RESULT_PARAM,
ABFIL_PARAM,
AP_LOBJ_CODE_PARAM,
AP_LOGIN_ID_PARAM,
AP_LOCKED_PARAM,
AP_REGNR_PARAM: TOraParam;
begin
BeginExecPLSQL;
try
RESULT_PARAM := AddParam('RESULT', ftUnknown, ptOutput);
ABFIL_PARAM := AddParam('BFIL', ftString, ptInput);
ABFIL_PARAM.Size := 8;
ABFIL_PARAM.AsString := ABFIL;
AP_LOBJ_CODE_PARAM := AddParam('P_LOBJ_CODE', ftString, ptInput);
AP_LOBJ_CODE_PARAM.Size := 2;
AP_LOBJ_CODE_PARAM.AsString := AP_LOBJ_CODE;
AP_LOGIN_ID_PARAM := AddParam('P_LOGIN_ID', ftString, ptInput);
AP_LOGIN_ID_PARAM.Size := 5;
AP_LOGIN_ID_PARAM.AsString := AP_LOGIN_ID;
AP_LOCKED_PARAM := AddParam('P_LOCKED', ftString, ptInput);
AP_LOCKED_PARAM.Size := 1;
AP_LOCKED_PARAM.AsString := AP_LOCKED;
AP_REGNR_PARAM := AddParam('P_REGNR', ftString, ptInput);
AP_REGNR_PARAM.Size := 05;
AP_REGNR_PARAM.AsString := AP_REGNR;
ExecProc('LOAD');
Result := RESULT_PARAM.AsString;
finally
EndExecPLSQL;
end;
end;

=======
PART C

procedure TfrmForm1.Button1Click(Sender: TObject);
VAR X: tscanning;
begin
try
x.Create(frmform1);
x.load('test.jpg','MT','ELIGR','Y','12345');
finally
x.free;
end;
end;
=======

Posted: **Tue 27 Nov 2007 09:07**

You should call Execute method of TOraStoredProc to execute the stored procedure. For example:

Code: Select all

var
  Res: string;
begin
  OraStoredProc1.Execute;
  Res := OraStoredProc1.ParamByName('RESULT').AsString; // get a result of the function
end;