show package output parameter on form

[Ludek]

hi, what is the simplest way of showing output parameters returned by a package on a form? I would prefer some simple way like binding of a field to a tdbedit.
thanks!

[AlexP]

Hello,

One of the ways to bind an output parameter to DBEdit is to use the Oracle ref cursor as an output parameter, like

CREATE OR REPLACE PACKAGE pg_alexp IS

TYPE return_cur IS REF CURSOR;
PROCEDURE get_cursor(p_return_rec OUT return_cur, p_ret_text OUT varchar2);
END pg_alexp;
/
CREATE OR REPLACE PACKAGE BODY pg_alexp IS
PROCEDURE get_cursor(p_return_rec OUT return_cur, p_ret_text OUT varchar2) IS
BEGIN
OPEN p_return_rec FOR
SELECT 1 AS num, 'text' AS txt, TRUNC(sysdate) AS dat FROM dual;
p_ret_text:= 'this is a text';
END get_cursor;
END pg_alexp;
/

OraStoredProc1.StoredProcName := 'PG_ALEXP.GET_CURSOR';
OraStoredProc1.Prepare;
OraDataSource1.DataSet:=OraStoredProc1;
DBEdit1.DataSource:=OraDataSource1;
DBEdit1.DataField:= 'num';
OraStoredProc1.Execute;

or to use the ParamByName method, like

DBEdit1.Text:=OraStoredProc1.paramByName('P_RET_TEXT').AsString;

But using REF CURSOR decreases performance.

Please specify the task in more details and I'll try to give you a more detailed answer.

[Ludek]

Thanks for a tip.
I have some package with a procedure with some input and output parameters (no complex types, typically integers) - implemented by someone else, used also with other tools (not just my application with delphi+odac). Now I need to execute the procedure and show the output parameters on delphi form. And I'm looking for some good practice for doing this.

With ms sql & stored proc i would write following to a tmsquery.sql:

Code: Select all

declare @outputpar1 int
declare @outputpar2 int

exec myprocedure 1, @outputpar1 output, @outputpar2 output
select @outputpar1 as outputpar1, @outputpar2 as outputpar2

create persistent fields, connect to datasource and bind to tdbedits. simple , straightforward, no delphi code required, no new ddl required, just short code in tmsquery and a few clicks in delphi designer.

And I'm looking for something similar with oracle+odac. Or, when nothing simliar possible, some other "good practice".

[AlexP]

Hello,

For Oracle you can use the following PL/SQL code:

DECLARE
a NUMBER;
b VARCHAR2(50);
c DATE;
BEGIN
TEST_PROCEDURE(a,b,c);
OPEN :cur FOR
SELECT a, b, c FROM dual;
END;

where 'TEST_PROCEDURE' is a stored procedure:

PROCEDURE TEST_PROCEDURE
(A_VAL1 OUT NUMBER, A_VAL2 OUT VARCHAR2, A_VAL3 OUT DATE)
AS
BEGIN
A_VAL1 := 1;
A_VAL2 := 'text';
A_VAL3 := trunc(sysdate);
END;

you should determine the ':cur' parameter as Cursor in TOraQuery and you can use it to for binding to data controls.