How can I call an Oracle function from Delphi?

Question

I created a function in Oracle that inserts records in specific tables and return an output according to what occurs within the function. e.g (ins_rec return number)

How do I call this function and see its output in Delphi?

I got a reply (with all my thanks) for sql plus but I need to know how can I do this in Delphi

Welcome to stackoverflow! You can show your thanks to people who have answered questions by marking an answer as correct. Click the green tick by the answer. Afraid I can't help you with this question though - I know nothing about Delphi. :) — cagcowboy
– cagcowboy, Commented Feb 26, 2011 at 15:30

Bharat · Accepted Answer · 2011-02-28 06:33:41Z

7

Just pass the user defined function as column name in the query and it will work.

Example:

Var
  RetValue: Integer;
begin
  Query1.Clear;
  Query1.Sql.Text := 'Select MyFunction(Param1) FunRetValue from dual';
  Query1.Open;

  if not Query1.Eof then
  begin
    RetValue := Query1.FieldByName('FunRetValue').AsInteger;
  end;
end;

edited Feb 28, 2011 at 6:33

answered Feb 26, 2011 at 15:32

Bharat

6,8645 gold badges37 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user160694 Over a year ago

Oracle has the DUAL special table to be used when you need something like this. Your code may not perform what you expect - it can call the function for each row of <YourTable>.

score 6 · Accepted Answer · 2011-02-26 19:23:38Z

How to accomplish it may depend on what DB access library you use (BDE? dbExpress? ADO? others), some may offer a "stored procedure" component that may work with functions as well.

A general approach it to use an anonymous PL/SQL block to call the function (and a parameter to read the return value), PL/SQL resembles Pascal a lot...:

Qry.SQL.Clear;
Qry.SQL.Add('BEGIN');
Qry.SQL.Add('  :Rez := ins_rec;');
Qry.SQL.Add('END;');
// Set the parameter type here...
...
Qry.ExecSQL;
...
ReturnValue :=  Qry.ParamByName('Rez').Value;

I would not have used a function, though, but a stored procedure with an OUT value. Moreover, Oracle offers packages that are a very nice way to organize procedure and functions, and they also offer useful features like session variables and initialization/finalization sections... very much alike a Delphi unit.

Leslie · Accepted Answer · 2011-02-28 14:56:50Z

0

we run an Oracle stored procedure using this code that utilizes the BDE (I know please don't bash because we used the BDE!)

Try
  DMod.Database1.Connected:= False;
  DMod.Database1.Connected:= True;
  with DMod.StoredProc1 do
  begin
    Active:= False;

    ParamByName('V_CASE_IN').AsString:= Trim(strCaseNum);
    ParamByName('V_WARRANT_IN').AsString:= strWarrantNum;
    ParamByName('V_METRO_COMMENTS').AsString:= strComment;

    Prepare;
    ExecProc;

    Result:= ParamByName('Result').AsString;
  end;
Except

answered Feb 28, 2011 at 14:56

Leslie

3,6447 gold badges44 silver badges54 bronze badges

Collectives™ on Stack Overflow

How can I call an Oracle function from Delphi?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related