Very Computer

by **Marc Scheun** » Sun, 31 Dec 1899 09:00:00

>PROCEDURE C3_GETMATRIXTABLE
>(matrix_id_in IN matrix.matrix_id%TYPE,
> matrix_rec_out OUT matrix%ROWTYPE)
>from Delphi. What is the best way of doing this? In particular, how is the
>parameter binding done? Can Delphi automatically resolve
>matrix.matrix_id%TYPE, or do I have to explicitly define the datatype? Is
>there a Delphi datatype that maps to a row such as matrix%ROWTYPE?

First of all, you can't use %ROWTYPE, Delphi can't handle that, define
your out parameter(s) using *ONLY* basic data types (like VARCHAR2,
NUMBER, DATE).

Then:

1) Drop a TStoredProc on your delphi form or data module and connect
it to the appropriate database (using a TDatabase object, preferably)

2) Set the StoredProcName property (your stored proc might not show up
in the dropdown list, but you can always type in the name)

3) You'll need to manually define the parameters, either in the
"Params" property editor, or in code (using the CreateParams call).
And be sure to use EXACTLY the same names as in your Oracle
definition! It's picky, even capital / non-capital letters are
important!)

4) Set the IN parameter in code:
MyStoredProc.ParamByName('matrix_id_in').AsInteger := 0
(or whatever it is)

5) Call MyStoredProc.ExecProc

6) Read out the OUT parameter by using:
myVar := MyStoredProc.ParamByName('outparam1').AsString;

HTH
Marc

--------------------------------------------------------------------------
Marc Scheuner Berner Versicherungen, Dept. ISV
May the Source be With You Laupenstrasse 27

--------------------------------------------------------------------------

by **Oleg Roshchi** » Sun, 31 Dec 1899 09:00:00

You also can call your stored procedure or function programmatically.
Below is the example of procedure call with two parameters: "func" and
"current_id".
If you call function you can get the result as it was written before by Marc
Scheuner:
myVar := EL.ParamByName('outparam1').AsString;

procedure Wri*tryStatus(v_NameFunc: string; v_id: integer);
var EL: TStoredProc;
begin
EL := TStoredProc.Create(nil);
with EL do
begin
DatabaseName := Session.Databases[0].DatabaseName ;
StoredProcName := 'ENTRY_P.LOOP_ENTRY'; // Entry_p is a package,
Loop_Entry is a procedure
Prepare;
// you need this if call programmatically!
parambyname('FUNC').asSTRING:=v_namefunc ;
parambyname('CURRENT_ID').asInteger:=v_id ;
execproc ;
destroy;
end;
end;

Cheers,
Oleg

Moscow, Russia

>PROCEDURE C3_GETMATRIXTABLE
>(matrix_id_in IN matrix.matrix_id%TYPE,
> matrix_rec_out OUT matrix%ROWTYPE)
>from Delphi. What is the best way of doing this? In particular, how is the
>parameter binding done? Can Delphi automatically resolve
>matrix.matrix_id%TYPE, or do I have to explicitly define the datatype? Is
>there a Delphi datatype that maps to a row such as matrix%ROWTYPE?

First of all, you can't use %ROWTYPE, Delphi can't handle that, define
your out parameter(s) using *ONLY* basic data types (like VARCHAR2,
NUMBER, DATE).

Then:

1) Drop a TStoredProc on your delphi form or data module and connect
it to the appropriate database (using a TDatabase object, preferably)

2) Set the StoredProcName property (your stored proc might not show up
in the dropdown list, but you can always type in the name)

3) You'll need to manually define the parameters, either in the
"Params" property editor, or in code (using the CreateParams call).
And be sure to use EXACTLY the same names as in your Oracle
definition! It's picky, even capital / non-capital letters are
important!)

4) Set the IN parameter in code:
MyStoredProc.ParamByName('matrix_id_in').AsInteger := 0
(or whatever it is)

5) Call MyStoredProc.ExecProc

6) Read out the OUT parameter by using:
myVar := MyStoredProc.ParamByName('outparam1').AsString;

HTH
Marc

--------------------------------------------------------------------------
Marc Scheuner Berner Versicherungen, Dept. ISV
May the Source be With You Laupenstrasse 27

--------------------------------------------------------------------------

Very Computer

Calling an Oracle Stored Procedure

Calling an Oracle Stored Procedure

Calling an Oracle Stored Procedure